Are there any issues with connecting MS Access to a SQL Server database that I should be aware of?

Question

I have an issue occurring in Microsoft Dynamics GP, and am doing research to see if this is the cause,but this could be a Access to SQL Server issue. GP stores data in SQL Server, and MS Access is being used to access the table data. Some data appears to be going missing in the production database. Unfortunately, I don't know what is being done with Access, as that is not being disclosed to me. A simple solution would be to tell them to stop using Access to verify if data still goes missing, but I would like to know of any documented issues before suggesting this.

So in an attempt to give a best guess on what is happening, what does Access do when it is connected with SQL Server as back end? Does it lock the tables? What would have to be done to cause data to be missing or deleted? If Access is the cause, what steps should be taken to resolve it?

Answer 1

Is it your intention that Access should be allowed to change or delete data?

If not, have you considered setting up a user ID for use by Access that permits read-only access (no pun intended)?

In any case, a SQL Server best practice is to insure that user accounts have the mimimum access rights necessary to meet the business requirements. Please don't give everyone the sa account with a blank password.

EDIT:

Think of Access like SQL Server Management Studio, in other words an interactive tool one can use to query and change the database in a virtually unlimited fashion provided your account has the permissions to allow you to do that. Since it is an interactive environment where one can pull up a table, deleting a row is as simple as clicking on that row and pressing the delete key. Similary changing a data value is as simple as clicking on that row and column and typing a new value.

Of course deleting something is as simple as hitting the delete key by accident as well.

It does not suprise me that when you give users such a tool and an account with no access restrictions that data will end up being changed, whether by intent, accident or otherwise.

Answer 2

SQL Server data is typically accessed in MSACCESS using linked tables, which are ODBC/OLEDB connections. While this technology is somewhat antiquated and not completely bug free, I can't think of anything in the underlying technology that might be causing this.

Are your ordinary users accessing these tables using an ODBC data source or connection string containing an administrator ID and password to the SQL Server database (i.e. unrestricted rights)? If they are, missing data is going to be a given. Deleting rows in an open MSACCESS linked table is as easy as deleting rows in Excel.

Record locking in MSACCESS/SQL connections is typically optimistic (unless you change it). In the old days under SQL Server 2000 this meant that if two people were editing the same record at the same time, the person who saves last won (silently). Nowadays the last person saving is told that someone else has edited the record while they were making changes, and offers two options: abandon my changes, or overwrite the other person's changes.

In any case, providing read-only access to the tables should alleviate a lot of the heartburn.