Run-time error on appending linked table in MS Access

Question

I have a database that links to an Excel spreadsheet. When adding the linked table manually, I have no problems (I just follow the little wizard and the table is created perfectly). I am trying to automate that using VBA:

Dim db as DAO.Database
Dim tdf as TableDef

Set db = CurrentDB
Set tdf = db.CreateTableDef("linked_table")
tdf.Connect = "Excel 8.0;HDR=YES;IMEX=2;DATABASE=" & Me.txtExcelFile  ' references form field
db.TableDefs.Append tdf  ' Here's where I get a run time error

Unfortunately I get run-time error 3264 on the last line and it says, "No field defined--cannot append TableDefs or Index." I'm not even sure why the last line is needed (it seems from the documentation I've found that it's to add the table to the TableDefs collection of the database). Any suggestions on resolving the error?

Answer 1

You are missing:

tdf.SourceTableName = "Sheet1$"

Or whatever range or sheet name you wish to use as the table.

So, in all, this works for me:

Dim db as DAO.Database
Dim tdf as TableDef

Set db = CurrentDB
Set tdf = db.CreateTableDef("linked_table")
tdf.SourceTableName = "Sheet1$"
tdf.Connect = "Excel 8.0;HDR=YES;IMEX=2;DATABASE=" & Me.txtExcelFile  ' references form field
db.TableDefs.Append tdf

You can also link Excel using TransferSpreadsheet