Using Access as front-end to SQL Server

Users who are familiar with the MS Access interface may find the initial upgrade to sql server daunting. The following useful first step will allow users to continue using the interface/functions they are familiar with.

This involves linking the sql server tables in using odbc

First of all set up an odbc entry via control panel to your sql db

Then, in Access, choose the menu options file/get external data/link tables and change the file type to 'odbc databases' and then select the odbc entry you set up earlier

You will then be able to select all the tables you want to link in. It will then be possible to query, update, append and delete data from the sql database (assuming the user has suitable ermissions) directly from Access

A couple of things to note. Access needs to understand the primary key on the tables, if this is not set up in sql server you will need to define the fields which form the key. Also Access will prefix the table names with the table owner and an underscore e.g.dbo_yourtable, but you can then rename them as you wish without affecting the underlying link.

While Access will let you link to a table withoout some kind of unique Identifier, it will not let you update, insert or delete from that table without one.

Also, Access will not auomatically reflect any underlying table structure changes you make. You will have to refresh the link on the tables when you change their structure in SQL Server.

I hope this is useful to someone and not too obvious to those who do this all the time

Andy