All geek questions in one place

Runtime error when adding a linked table to MS Access

I have a database that references an Excel spreadsheet. When adding a linked table manually, I have no problem (I just follow the little wizard and the table is created perfectly). I am trying to automate this 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 where I get a run time error

Unfortunately, I get a 3264 error at runtime on the last line, and it says: "There is no field defined - it is not possible to add TableDefs or Index." I don’t even know why the last row is needed (from the documentation I found that it adds a table to the Database TableDefs collection). Any suggestions for fixing the error?

+4
source share
1 answer

You are lacking:

 tdf.SourceTableName = "Sheet1$"

Or any range or sheet name that you want to use as a table.

So overall, 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

+4
source

Source: https://habr.com/ru/post/1433883/

More articles:


All Articles