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

 

[joel]

OPen the database with Access. Check if there is a table in Acces
called "database"

 

[Les Gombart]

yup... table is called database

 

[joel]

Is the version of Excel and Access the same?

Try creating a new workbook and put the macro into the workbook. There
is a hidden version number in the worksheet that is associated with the
version of excel that was used to create the sheet. This version number
doesn't get updated. So a worksheet that was created in excel 2003
still has 2003 when used with 2007. Access does get updated and is not
two way compatible in that Access 2003 can read a database that was
updated to 2007. An Access 2007 can't read a database in Access 2003.
One possibility is the version of Access and version of Excel are not
compatible.

 

[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