Access with MS-SQL Server

Question

my company (I work for) has a Microsoft Office access application, it's more than 1 million line of code and hundred of forms, tables, databases... So converting it to dot net (my suggestion) isn't a choice for my boss.

The aim is to migrate it to SQL Server, I have done simple SQL migration and tables had been converted and everything alright, but I'm using SQL 2008, which (should) implement the "disconnect" technology (ado.net) however access works only with ADO the old technology.

but the application works! and updates the SQL databases, so how does this works??

Answer 1

Evan's right, you'll probably get much better results from this on StackOverflow.

That said, you're partially right. Internally Access only works with the original ADO because it uses VBA. Since VBA only has support for COM you're only going to get classic ADO support in your macros and internal code.

Connecting to an external datasource is a bit different in that you can use other drivers to do this. More than likely you're connecting to SQL 2008 with the .NET Sql Native Client or a SQL OLEDB driver.

Answer 2

Do you mean that the migration wizard has not only migrated the data to sql server, but also updated the mdb file to use that sql server database instead of its own tables? And you are wondering how that works?

I am not sure how the migration wizard sets this up, but I have done this manually before, and I did it with linked tables. After all the data is migrated to sql server, I removed all the tables from the mdb file and replaced each of them with a linked table to their equivalent table in sql server..