September 23, 2001 at 9:38 pm
Im converting an app from access 2000 to sql server 2000 and am unsure of how the datatypes map..can anyone help?
thanks
September 24, 2001 at 1:30 pm
September 24, 2001 at 4:21 pm
Here is some code I did quite a while back, then Leon and I revised for a recent project. Has been lightly tested. You'll need to set a reference to ADOX. Basically we're passing it an ADO recordset that we want to export to an Access mdb and we don't know the structure at design time, so we build it on the fly. Some of the mappings are based on pure experimentation. This code is meant to support both Access 97 and 2000 formats. One of the interesting things we found is that you can define a table with more than 2000 chars per record in Access 97 (but that is the limit of the data), but in Access 2000 it will not let you do so (they fixed a bug which was kinda handy).
Maybe it'll help!
Andy
'see if we need to do special handling
If AccessCountTableLength(rs) > 2000 Then
bIsBigTable = True
End If
'append a new table first
Set tdf = New ADOX.Table
tdf.Name = UCase$(mvarTableName)
Set tdf.ParentCatalog = cat
'just loop through the fields collection, adding each one to our
tabledef - skipping
'any recordset fields. Only special handling required is for numbers,
and you could
'probably skip that
For J = 0 To rs.Fields.Count - 1
bSkipThisCol = False
Set fld = New ADOX.Column
With fld
.Name = rs.Fields(J).Name
.Type = adLongVarChar
.Attributes = adColNullable
Select Case rs.Fields(J).Type
Case adBigInt, adCurrency, adDecimal, adDouble, adInteger,
adUnsignedTinyInt
.Type = rs.Fields(J).Type
.DefinedSize = rs.Fields(J).DefinedSize
.NumericScale = rs.Fields(J).NumericScale
Case adDate, adDBTime, adDBTimeStamp
.Type = adDate
Case adChar, adVarChar
If bIsBigTable = False Then
If ExportFormat = Access2000 Then
'remap because Access2K doesnt like char - see
Q266302
.Type = adWChar
.DefinedSize = rs.Fields(J).DefinedSize
Else
.Type = rs.Fields(J).Type
.DefinedSize = rs.Fields(J).DefinedSize
End If
Else
'make any text field a memo
If ExportFormat = Access2000 Then
.Type = adLongVarWChar
Else
.Type = adLongVarChar
.DefinedSize = rs.Fields(J).DefinedSize
End If
End If
Case adLongVarChar
If ExportFormat = Access2000 Then
.Type = adLongVarWChar
Else
.Type = rs.Fields(J).Type
.DefinedSize = rs.Fields(J).DefinedSize
End If
Case adBoolean
.Type = rs.Fields(J).Type
.DefinedSize = 1
If ExportFormat = Access2000 Then
.Attributes = adColFixed
End If
Case Else
'if it's 136 its an embedded recordset, otherwise its
text
'or something else where we dont care about setting any
other
'properties/attributes
If rs.Fields(J).Type = 136 Then
'Also have to set a flag
'so that we dont append this field to the table - it
would be
'all nulls anyway
bSkipThisCol = True
Call AccessCreateTable(rs.Fields(J).Value, cat,
rs.Fields(J).Name, ExportFormat)
Else
.Type = rs.Fields(J).Type
.DefinedSize = rs.Fields(J).DefinedSize
End If
End Select
End With
'be skip if it was a recordset
If bSkipThisCol = False Then
tdf.Columns.Append fld
End If
Set fld = Nothing
Next
'now that definition is complete, append the table
cat.Tables.Append tdf
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply