Go to Top

10 things every Access developer who works with SQL Server should know

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.

 

 

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

7 Responses to "10 things every Access developer who works with SQL Server should know"

  • Giorgio Rovelli
    March 27, 2016 - 6:37 pm Reply

    Point 9 says, “When inserting bulk data from Access to SQL Server use XML import,”
    How do you insert bulk data from Access to SQL Server using XML import?

  • Daniel Heidelberg
    June 16, 2013 - 2:32 pm Reply

    1. I know Rowversion is Changed every time a table is modified or changed, is it possible that if I do more than one update let’s say i Update it once and then Updated it again the second Update will have higher “Rowversion” value than that first update ?

    2. Can you provide with a good comparison between “Timestamp” and “Rowversion” I didn’t saw any guides about it.

    • Ben Clothier
      June 18, 2013 - 3:43 am Reply

      1) yes. Do note that rowversion is global to the whole database — if you edit one record in one table, and get binary equivalent value of 1, then update some other table’s record, that record will get 2 but when you go back to the first table and change yet another record, this get 3. So while you can infer which update happened first, next and last you should not assume there will be no gaps between the edits within a single table.

      2) There are potentially two answer to the question — if you’re talking about the older definition of ‘timestamp’ as used as SQL Server, then the answer is none- it was just a bad name that they finally got around to fixing in 2005 but for whatever reasons never actually fixed the SSMS designer to use the new name. There is no function difference between the original SQL Server ‘timestamp’ and SQL Server ‘rowversion’. They are synonymous.

      BUT – if you’re wondering about MySQL’s timestamp versus SQL Server’s timestamp/rowversion, then they are NOT the same thing. SQL Server’s rowversion never ever stamped any kind of time, period (which is why the original name of ‘timestamp’ was horrible to belong with). All it did was stamped a sequential binary value to every change made in a single database. MySQL’s timestamp, on the other hand, truly did store a timestamp, using UNIX timestamp (which has epoch date of 1970-01-01 and counts the second since that epoch date), so you can use it like a regular date/time. Other RDBMS may have their own implementation of timestamp and rowversion — you would need to consult their documentation on how to best use them.

      Hope that helps.

  • Anthony Latham
    May 16, 2013 - 6:51 pm Reply

    Hi Juan,

    I exclusively use Microsoft Access ADP with SQL Server Express

    I use ADODB with late binding:

    Public Const padCmdTable As Integer = 2
    Public Const padCmdText As Integer = 1
    Public Const padLockOptimistic As Integer = 3
    Public Const padLockReadOnly As Integer = 1
    Public Const padOpenDynamic As Integer = 2
    Public Const padOpenForwardOnly As Integer = 0
    Public Const padOpenStatic As Integer = 3

    ‘ I call this function throughout my database.
    Public Function InitialiseRecordsetLate(ByRef rst As Object, _
    ByVal strSource As String, _
    Optional ByVal intCursorType As Integer, _
    Optional ByVal intLockType As Integer, _
    Optional ByVal intOptions As Integer) As Boolean

    On Error GoTo ErrorHandler

    InitialiseRecordsetLate = False
    Set rst = CreateObject(“ADODB.Recordset”)
    Set rst.ActiveConnection = Application.CurrentProject.Connection
    rst.CursorLocation = 2
    rst.Open strSource, , intCursorType, intLockType, intOptions
    InitialiseRecordsetLate = True

    ExitHere:
    Exit Function

    ErrorHandler:
    InitialiseRecordsetLate = False
    Call LogError(Err.Number, Err.Description, mconModuleName, “InitialiseRecordsetLate”)
    Resume ExitHere
    End Function

    Example to call InitialiseRecordsetLate

    Dim rst As Object
    Dim strSQL as String

    strSQL = “SELECT * FROM TableName;”

    Call InitialiseRecordsetLate(rst, strSQL, padOpenStatic, padLockReadOnly, padCmdText)

  • Srdjan Vasiljevic
    May 16, 2013 - 4:29 pm Reply

    Hi Juan. Thank you for the tips.

    Can you provide some info about XML import to SQL from Access ?

    Thanks in advance.

  • ronnie valero
    May 16, 2013 - 3:07 am Reply

    Hi Juan, What is the future of Access as a front end tool?
    Access fe and be works fine in VPN using Remote Desktop or RemoteAPP.

  • Rx_
    May 14, 2013 - 11:38 pm Reply

    Thanks for the valuable tips. Access 2010 and SQL Server are a great match.
    When using Access 2010 with SQL Server and distribute to users with Citrix, these tips really hit a home run.

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
  • This field is for validation purposes and should be left unchanged.