| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| retain table formatting when writing excel table to a txt file | Excel Programming | |||
| Syntax Error - Using Microsoft Query and ODBC link to a table | Excel Discussion (Misc queries) | |||
| when writing an IF statement what is the syntax for "Not Equal to. | Excel Discussion (Misc queries) | |||
| when writing an IF statement what is the syntax for "Not Equal to. | Excel Discussion (Misc queries) | |||
| Error 50290: Error writing to Worksheet while using an ActiveX Control | Excel Programming | |||
Hybrid Mode
