Sep 15 2016

How to add an SQL Table to a Microsoft Access application

I was having a weird time getting a new Sql Table to work properly in an Access application.  The problem is that this app has not had a new table added for several years so I kept chasing red herrings.  Ultimately here are the steps that work for me…

 

WHAT TO DO TO ADD AN A TABLE TO Sql

1.  Be sure to NOT forget to set the permissions on the table like this…
    GRANT INSERT, UPDATE, DELETE, SELECT, VIEW DEFINITION ON <NEW_TABLE_NAME> TO MYDB  Ultimately this was the step I forgot.
    
    DO NOT link the table to Access until the permissions have been set.

2.  When adding the table as a new linked table into  the app, use a FILE DSN.  Using a System DSN may have contributed to the issue as well.  Click the Save Password checkbox when linking it in.
   
3.  Remove the dbo_ from the front of the table name in Access.

4.  Your Access app needs a RefreshTableLinks process as described all over the place (just google refresh sql table links in msaccess).  Run that after adding the table.  Here is an example. 

http://stackoverflow.com/questions/564265/how-to-refresh-linked-tables-in-an-access-mdb-when-odbc-changes