mapping access 2000 datatypes to sql server 2000

  • Im converting an app from access 2000 to sql server 2000 and am unsure of how the datatypes map..can anyone help?

    thanks

  • 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