Working with SQL Server and Access can be challenging, here are 10 things you should know about SQL Server and Access:
- 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.
- 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)
- 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.
- 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.
- You should use DateTime or even better DateTime2 instead of Date in SQL Server.
- 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.
- 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.
- Using ADODB instead of VBA to send action queries to SQL Server is the fastest method, using linked tables is the slowest.
- When inserting bulk data from Access to SQL Server use XML import, it usually takes seconds instead of minutes using an insert query.
- Retrieve the last key inserted by using SCOPE_IDENTITY and ADODB.
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?
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.
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.
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)
Hi Juan. Thank you for the tips.
Can you provide some info about XML import to SQL from Access ?
Thanks in advance.
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.
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.