Syntax Error Writing in MDB table

[Les Gombart]

I have an excel sheet that uses Access tables in the background to store
information... The file has been functioning, but for various reasons, I
needed to change the name of the Access file from "PDAv1_be.mdb" with a table
called "clients", to "PDAdatabase.mdb" with a table called database.

I now keep getting an error saying that input table or query "clients"
cannot be found. When I change the script to "database", I get an error
"Syntax error in FROM clause".

The code causing the problem is pasted below...
------------------------------------------------------------

databasefile = Admin.Range("B3").Value


'Open the connection to the PDA Database
Set Connection = New ADODB.Connection
Cnct = "Provider = Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & databasefile & ";"

Connection.Open ConnectionString:=Cnct

'Create recordset
Set rstimportbrand = New ADODB.Recordset

rstimportbrand.Open "database", Connection, adOpenKeyset, adLockOptimistic,
adCmdTable


I would REALLY appreciate it if someone could point me in the right direct...

NB using Excel 2007

 

[Les Gombart]

yup... table is called database

 

[Tim Williams]

Use "adCmdTableDirect" instead of "adCmdTable"

Tim

 

[Les Gombart]

Problem resolved thanks... FYI I changed the access table name from database
to something else and then changed the code in VBA from database to the new
name and hey presto.

I can only assume that the "database" name was causing a conflict as a
reserved name or something to that effect