February 1, 2005 at 3:00 pm
Hello. I have a legacy MS Access application, which has some tables I need to access from my new SQL Server database. I can't just import that data, because the old system is still used, but I need to be able to look at it and possibly update it in it's current state at any time - I need to share it, basically. I tried exporting the required tables from Access using File. -> Export, picking ODBC, and picking LocalServer under Machine Data Sources. That gave me the error: "ODBC -- call failed. [Microsoft][ODBC Device Manager] Data source name not found and no default driver selected (#0)". I am not very good with this sort of configuration, etc, and have no idea if this ODBC business is even what I need to use to share my tables.
Please advise.
Thanks
James.
February 1, 2005 at 3:08 pm
It sounds like you need to setup a Linked Server. Once you define your Access db as a linked server you can query and update it pretty much as though the tables were in SQL Server. If you have no idea how to do this, check out Books Online for full documentation.
Linked servers work well, but I would caution that you probably don't want to use them for queries that are run frequently or try to run cross server joins unless the tables are very small or performance isn't an issue.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
February 2, 2005 at 2:44 am
A different approach could be:
- MOVE the tables from Access to Sql Server
- change the Access application mdb to LINK tables (via ODBC) to the Sql Server, after using the wizard you can rename the linked tables to the original name (take out the 'dbo_' prefix built on name by the wizard)
Now yor old app can run using the data on SqlServer, and this should be (AFAIK) not worst than before from a performance point of view, and it will be easier from the Sql Server to access that data.
HTH
Gigi
February 2, 2005 at 10:59 am
You could import all the tables to SQL Server. Use SQL Server for housing data and create ADP interface on ACCESS. It works pretty good. I am running one of the database this way.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply