Reading SQL Server TIMESTAMP fields via ADO/OLE DB Connection

Description:

This example demonstrates how to open an ADO recordset from a SQL Server table which includes TIMESTAMP fields. The Timestamp fields are cast as data type "datetime".

How to use:
  1. Paste the code into your VB or VBA application.
  2. Edit the connection string and SQL statement to match your data.
  3. Call the procedure from within your application.
Public Sub Read_TIMESTAMP()
'++ Read_TIMESTAMP:  Reading SQL Server TIMESTAMP fields ..
On Error GoTo EH

'++ Create the ADO Connection and Recordset objects
Dim pAdocon As ADODB.Connection
Set pAdocon = New ADODB.Connection

Dim pAdors As ADODB.Recordset
Set pAdors = New ADODB.Recordset

'++ MS OLE DB provider for SQL Server
Dim sConstring As String
sConstring = "Provider=SQLOLEDB.1;User ID=oledb;Password=oledb;Persist Security Info=True;Initial Catalog=my_database;Data Source=my_server"

'++ Open the connection
pAdocon.Open sConstring

'++ Construct a SQL expression that will handle the data type conversion
Dim SQLstr As String
SQLstr = "select firstname, birthdate, country, " & _
         "cast(timestamp_fld as datetime) as TimeStmp from employee_2"

'++ Open the recordset
pAdors.Open SQLstr, pAdocon, adOpenForwardOnly, adLockOptimistic

'++ Apply a filter to the recordset
pAdors.Filter = "Country = 'UK'"

'++ Edit the recordset
pAdors.Update pAdors.Fields.Item(0).Name, "Eric"

'++ Close and de-reference the ADO objects
pAdors.Close
pAdocon.Close

Set pAdors = Nothing
Set pAdocon = Nothing

Exit Sub
EH:
  MsgBox Err.Description, vbInformation, "Read_TIMESTAMP"

End Sub