Working with SQL Server and Access can be challenging, here are 10 things you should know about SQL Server and Access:

  1. Using a pure Access solution, (no SQL Server), is almost impossible over VPN, but not a problem at all with SQL Server as your backend.
  2. Every table should have a timestamp field, but did you know timestamp is now called rowversion? Did you also know the only way to assign rowversion to the table is through DDL commands? (Timestamp is still an option in SSMS)
  3. The only way to edit data in a view in Access is to make sure the view has an index. There are two ways about it: You can add the index using Access or create the index in SQL Server using the CREATE VIEW SQL syntax. You can’t create an index using GUI in SSMS.
  4. Speaking of views, if you try to delete a record from a view composed of many tables, SQL Server may return an error message saying it does not know which record to delete. The fix is to create a delete trigger on the view and delete the record from the table you wish. Program the trigger for both bulk deletes and single record deletions.
  5. You should use DateTime or even better DateTime2 instead of Date in SQL Server.
  6. When querying for dates in SQL you must take into account the time, for example: Where DateOrdered Between 1/1/13 and 1/31/13 may not include all orders, but using DateOrdered Between 1/1/13 00:00:00 AM And 1/31/13 11:59:59 PM will.
  7. To avoid problems with dates, have Access save the date instead of using GetDate() in SQL Server. Access will store the date as mm/dd/yyyy 00:00:00 AM every time.
  8. Using ADODB instead of VBA to send action queries to SQL Server is the fastest method, using linked tables is the slowest.
  9. When inserting bulk data from Access to SQL Server use XML import, it usually takes seconds instead of minutes using an insert query.
  10. Retrieve the last key inserted by using SCOPE_IDENTITY and ADODB.