When creating new table and field in vba Access 2000. Getting Error Data Type Conversion Error

Question

I am trying to create a new table with a few field on vba for Access 2000. I am able to create the table and a field when the data type of the field is Text But i need it to be Number. when I am changing the data type from Text to Number it gives me the error Data Type Conversion Error. Any Help would be Appreciated. Thanks

Public Sub CreateNewDB()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

'Initialize the Contractor table

Set db = CurrentDb()
Set tdf = db.CreateTableDef("new test table")

'Specify the fields
With tdf

'Create Sequence No Fied
 ' Set fld = .CreateField("Sequence No", dbText, 10) ' This works 
Set fld = .CreateField("Sequence No", dbNumber, Long INteger) ' this gives me an error 
.Fields.Append fld

End With

'save  the table
db.TableDefs.Append tdf
Set fld = Nothing
Set tdf = Nothing

 End Sub

Beth Beth · Accepted Answer · 2014-12-18T21:18:38

try dbLong instead of dbNumber. Reference

Here's code to set the PK on a newly created table:

Set td = CurrentDb.CreateTableDef(sLocalTableName, dbAttachSavePWD, sRemoteTableName, sConnect)
CurrentDb.TableDefs.Append td
sExec = "CREATE UNIQUE INDEX PK ON [" & sLocalTableName & "]"
For iPK = 0 To iPkfldCount - 1
    sPKfields = sPKfields & "[" & CurrentDb.TableDefs(sLocalTableName).Fields(iPK).Name & "],"
Next
sPKfields = Left(sPKfields, Len(sPKfields) - 1)     ' strip trailing comma
CurrentDb.Execute sExec & " (" & sPKfields & ")"

after you set the PK fields, you shouldn't need to index or set to No Duplicates. All PKs work that way.

When creating new table and field in vba Access 2000. Getting Error Data Type Conversion Error

1 Answers