Access VBA unable to create table

2
votes

I'm having trouble creating an Access table using VBA, then accessing it via open-recordset. My goal is to write out records into a table. If I manually create the table, my code works perfectly. If the table does not exist, the program terminates at the open-recordset attempt. The error msg is:

"The MSO Access Database engine could not find the object 'myTable'

Where "myTable" is the value of argTable. Here's the code snippet:

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

Set db = CurrentDb()
Set tbl = db.CreateTableDef(argTable)
Set fld = tbl.CreateField("F1")

Set rstAccessTableOut = db.OpenRecordset(argTable, dbOpenTable)

What am I doing wrong? (Note I do not want to use SQL.)

1
ms-accessvba

1 Answers

1
votes

After you finish adding the fields you have to append the new table def to the collection to be able to use it. Also the filed needs to be appended as well

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

Set db = CurrentDb()
Set tbl = db.CreateTableDef(argTable)
Set fld = tbl.CreateField("F1", dbText, 20)

tbl.Fields.Append fld

db.TableDefs.Append tbl

Set rstAccessTableOut = db.OpenRecordset(argTable, dbOpenTable)