Connection to Access or SQL Server 2005

[George1111]

Using VB.Net I have a fully functional application which connects to an SQL Server 2005 Database

I want to now use the same application to connect to and Access Database (with exactly the same database design)

Is this possible in a simple way or is it necssary to rewrite every Database command to make it compatible with an Access Database

I came across some discussion on Linking an Access Database to an SQL Server as per :

Link Microsoft SQL Server to a Microsoft Access database

SQL Server can also be linked to a Microsoft Access database. Create a new linked server and give it a descriptive name, for example "Booklist". Next select "Other data source" and choose as data provider "Microsoft Jet 4.0 OLE DB Provider". Enter as product name "Access" and as data source the path to Microsoft Access file, for example "c:\My files\Books.mdb". This data provider is able to show the list of tables and views. So when you expand the Table's or View's item under this linked server you will see the list of tables or views in the Microsoft Access database.

Unfortunately I cant seem to find any reference to what is suggested here

The help system in Dot Net, as usual tells me it hasn't a clue - (Why should I be surprised - it has NEVER EVER given me an answer worth anything)

Confirmation of this lack of help is below

Dont you just love the "expectation failed" answer (I shouldn't expect too much)

Appreciate any help with this

[olivthill]

First, you have to choose whether you want a direct connection to your Access tables, or whether you prefer to have access to your Access tables through SQL Server tables (this is the solution you quoted).

For a direct connection to Access, the first and important obstacle is the "connection string". See http://www.connectionstrings.com/ . Then, since the SQL syntax is not always the same between SQL Server and Access, some queries might have to be rewritten or redesigned.

For a connection to Access through SQL Server, you have to define special tables in SQL Server. They are sometimes called "attached tables", or "linked tables". In the definition of these tables, you have to specify Access as a foreign source. With this solution, I suppose that your queries will not have to be changed.