Syntax Error Writing in MDB table
 

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

Syntax Error Writing in MDB table
 

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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=154397

Microsoft Office Help

Syntax Error Writing in MDB table
 

yup... table is called database

"joel" wrote:


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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=154397

Microsoft Office Help

.

Syntax Error Writing in MDB table
 

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.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=154397

Microsoft Office Help

Syntax Error Writing in MDB table
 

Use "adCmdTableDirect" instead of "adCmdTable"

Tim



On Nov 16, 3:11*am, Les Gombart
wrote:
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

Syntax Error Writing in MDB table
 

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


"joel" wrote:


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.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=154397

Microsoft Office Help

.