I am currently working on migrating a legacy codebase from VB6 and Access to VB.NET and SQL Server. I have run the Microsoft SQL Server Migration Assistant successfully and populated a SQL server with all of our existing tables from access, and I am currently looking into completely removing access from the equation, but I still need it currently to run the queries so that they can be forwarded to SQL Server. Is there a way to send queries directly to SQL Server without needing to translate them all, such as some sort of translation plugin or service I can use?
Sql-server – SQL Server accepting Access queries
migrationms accesssql serversql-server-2008
Best Answer
Assuming your queries are against regular Access tables, you should be able to replace the Access tables with Linked Tables to your SQL Server. The Linked SQL tables have to have the same names and structure to in order for this to work. I don't have the 2008 version of Access so I can't give exact instructions but look in the menus for something like External Data or Link Tables, then look for ODBC and create a "Link to the data source..." and follow the instructions to setup an ODBC data source to your SQL server, you can Google how to setup an ODBC connection if you need to.
You may need to setup the ODBC connection outside of Access beforehand depending on your security settings. Keep in mind that if your OS is 64-bit you need to use the 32-bid ODBC administrator console to setup an ODBC data source that Access can use. (Wasted a day figuring out that one.)