creating a new table using vba

[mazza]

Try to create a new table in an external database using the following code
but when I run the code nothing happens

any idea whats missing here (using access 2003)


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

Dim tName As String
Dim test As String
Dim strDB As String

'Hide the Access Window

Set dbs = OpenDatabase("c:\program files\compair service management\SQSV2DATA.mdb")

'Insert the name of the table you wish to create
tName = "TblCheckList"

Set tdf = dbs.CreateTableDef(tName)

' Create table wih Description fields.
Set fld = tdf.CreateField("checkid", dbLong)
tdf.Fields.Append fld
Set fld = tdf.CreateField("code", dbText, 2)
tdf.Fields.Append fld
Set fld = tdf.CreateField("modeltype", dbText, 7)
tdf.Fields.Append fld
Set fld = tdf.CreateField("item", dbText, 100)
tdf.Fields.Append fld
Set fld = tdf.CreateField("serv1", dbText, 10)
tdf.Fields.Append fld
Set fld = tdf.CreateField("serv2", dbText, 10)
tdf.Fields.Append fld
Set fld = tdf.CreateField("serv3", dbText, 10)
tdf.Fields.Append fld
Set fld = tdf.CreateField("serv4", dbText, 10)
tdf.Fields.Append fld


dbs.TableDefs.Refresh

 

[DJkarl]

Try to create a new table in an external database using the following code
but when I run the code nothing happens

any idea whats missing here (using access 2003)


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

Dim tName As String
Dim test As String
Dim strDB As String

'Hide the Access Window

Set dbs = OpenDatabase("c:\program files\compair service management\SQSV2DATA.mdb")

'Insert the name of the table you wish to create
tName = "TblCheckList"

Set tdf = dbs.CreateTableDef(tName)

' Create table wih Description fields.
Set fld = tdf.CreateField("checkid", dbLong)
tdf.Fields.Append fld
Set fld = tdf.CreateField("code", dbText, 2)
tdf.Fields.Append fld
Set fld = tdf.CreateField("modeltype", dbText, 7)
tdf.Fields.Append fld
Set fld = tdf.CreateField("item", dbText, 100)
tdf.Fields.Append fld
Set fld = tdf.CreateField("serv1", dbText, 10)
tdf.Fields.Append fld
Set fld = tdf.CreateField("serv2", dbText, 10)
tdf.Fields.Append fld
Set fld = tdf.CreateField("serv3", dbText, 10)
tdf.Fields.Append fld
Set fld = tdf.CreateField("serv4", dbText, 10)
tdf.Fields.Append fld


dbs.TableDefs.Refresh

I think you're missing

dbs.TableDefs.Append tdf

before the Refresh statement.

Alternately I might suggest using this method

dbs.Execute "CREATE TABLE TblCheckList ([checkid] LONG,code TEXT(2),[modeltype] TEXT(7),[item] TEXT(100),[serv1] TEXT(10),[serv2] TEXT(10),[serv3] TEXT(10),[serv4] TEXT(10))"