Importing or linking an Access 2007 db to a SQL Server 2005 database over a network

  1. First make sure the database is setup to use SQL auth as above (note NT auth will work also but requires client to use user name and password of server)
  2. In access create a new db and import data from an ODBC db
  3. Create a “Machine DSN” not a FILE DSN (File DSN is the default) see http://support.microsoft.com/kb/327268
  4. Select the tables you want to import or link to
  5. If access cannot identify a key field in the tables it will offer you the opportunity to create one (not necessary)
  6. Note foreign key relationships between tables will not be imported and must be manually re-entered, but can be saved without affecting the SQL server if using linked tables (good news is the relationships remain even after a refresh of the linked tables)

(a tip if you want to use linked table so that users can see up to the minute data without having to import but don’t want the users to modify the underlying SQL server db then when creating the SQL server login about and applying user mapping, choose to only allow the user to read the specific database and not to have write privileges)

Advertisements
%d bloggers like this: