Hi Guys,

Not sure if this belongs in the VBA or MS Access forums, so I'll post it to both.

I'm writing an application in MS Access that needs to read an Excel worksheet as a table. It contains customers details and the Access application will be opening it to read it only. No updates, no additions.

The full path name to the Excel spread sheet is contained in the variable CliData and the name of the worksheet in the XL file is called CUSTTABLE. For simplicity, I will create the table name in Access as CUSTTABLE also.

So, my code looks like this:

Dim DataFile As TableDef
Set DataFile = CurrentDb.CreateTableDef("CUSTTABLE")
DataFile.Connect = "Excel 8.0;database=" & CliData
DataFile.SourceTableName = "CUSTTABLE"
CurrentDb.TableDefs.Append DataFile

When I execute the code, it steps to the last line, when it attempts to append it to the current DB and gives the error message:

The Microsoft Jet database engine could not find the object 'CUSTTABLE'. Make sure the object exists and that you spell its name and the path name correctly.

Well, I've checked the path name and it's OK and I know the name of the XL table is correct, so the only thing I can think of is that I should be using something other than the SourceTableName to define worksheet that is to be accessed.

I can manually ccreate a linked table to this data and all is good, however I need to make it easy for the users to change which XL spreadsheet they are getting the data from, so I need to be able to create the linked table programmatically.

Has anyone tried this before?

Hope this makes sense.

Can anyone help?

TIA

Alan

Recommendation, look at the connection string of the table when you link it manually and mimic that. Don't use the "SourceTableName" parameter. To see the connect string, go to the Immediate window in the VBE and enter:

? CurrentDb.TableDefs("CUSTTABLE").Connect

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

Thanks for this tip Randall, it has helped.

The only problem now is that when I try to connect, I get a new error saying that there are no fields defined and it cannot append TableDef

Obviously, when I manually link this table, I specify that the first row contains the field names and all is good. When I try to define the fields using the CreateField method, Access ends up creating a blank internal table with these field names.

Is anyone able to add to this?

TIA

Alan

Randall,

Don't panic! I've sorted this out.

Firstly you include an option in the connect string 'HDG=YES' to get the field heading from the first row. Then I noticed that I was referring to the table as CUSTTABLE, which is the name of the work sheet I want to read.

Because it is a worksheet, not a named range, I should have been referring to the table as "CUSTTABLE$".

All is now working correctly.

Thanks again for your earlier suggestion, it also helped a lot.

Regards,

Alan