How to read the SQL server TIMESTAMP fields via an ADO-OLE DB connection

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

Paste the code into your VB or VBA application.
Edit the connection string and SQL statement to match your data.
Call the procedure from within your application.

[VBA]


        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