March 13, 2006 at 7:59 am
We recently upgraded a piece of software so that its back end went from Access to SQL. After the upgrade, there is still an Access .mdb file that contains linked tables to the SQL database.
I have another piece of software that accesses a query in the above database. Before the upgrade, it used an ODBC connection to the Access .mdb file. Now that the upgrade is complete, this connection no longer works.
I tried setting up a connection directly to the SQL server; however, the software is looking for a .mdb file, not a SQL database so this won't work for me.
Any ideas how to solve this problem?
Thanks for the help in advance.
March 13, 2006 at 11:03 am
From a certain mdac the odbc-driver for Access was dropped.
Since then you have to use the jet-driver.
March 13, 2006 at 12:54 pm
Hi Jo, thanks for the reply.
I am not sure I understand what you mean. When I create my DSN, should I not be using the SQL Server driver? If this is the case, which driver should I be using?
Thanks.
March 14, 2006 at 12:16 am
I thought you tried to connect to your access db with odbc drivers.
1) Does your query work from the Access-database itself using the odbc connection to sql server with the sql server drivers?
2) Are the linked tables still working?
3) What is the exact error information?
March 14, 2006 at 8:04 am
Check your Linked Table Manager in Access. It sounds like the upgrade created the SQL database and tables, but either it did not change your links from the front-end to the new SQL back-end, or you need to create an ODBC connection to the SQL database and change the source for the linked tables in the Linked Table Manager.
March 15, 2006 at 4:35 am
Check your table names on the SQL box. I've found the upgrade wizard will rename tables. If this is the case, the application you're running may be attempting to access a query by the old name. Also, attempt to connect to the .mdb from excel using the odbc driver for access (.mdb) to see if it still works -- sort of a test for how your app connects. Hope this helps.
March 15, 2006 at 6:07 am
Thank you all for your replies -
I tried connecting to my access db with the odbc connection and it did not work. My linked tables are working fine. All of the table names look fine too.
I am not receiving any errors. When I go into the software to set the datasource, it just places a red ! next to the query name.
I would like to just create an odbc connection to the sql server, but I can't since one of the buttons only looks for a .mdb file.
March 15, 2006 at 10:14 am
Consider converting/moving the access database application, (mdb), to an access data project, (adp).
[font="Arial"]Clifton G. Collins III[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply