On Error GoTo Err_Load
     
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim oRst_test As ADODB.Recordset
Dim fld As ADODB.Field
Dim sql As String

'connect to MySQL server using Connector/ODBC
Set conn = New ADODB.Connection
'conn.ConnectionString = "DRIVER={MariaDB ODBC 2.0 Driver};" '_
conn.ConnectionString = "DRIVER={MySQL ODBC 5.3 UNICODE Driver};" _
& "SERVER=192.168.200.245;" _
& "DATABASE=odbctest;" _
& "UID=xxxxxx;PWD=xxxxxxxx;PORT=9999;OPTION=17317930;"


'17317930: Log Query, Zero Date to Min, big int, big resultset, found rows
'540714:Log Query, big int, big resultset, found rows
'16426: big int, big resultset, found rows

conn.Open

'create table
conn.Execute "DROP TABLE IF EXISTS myado"
conn.Execute "CREATE TABLE myado(id int not null primary key AUTO_INCREMENT, name varchar(20)," _
& "txt text, dt date, tm time, ts timestamp)"

'direct insert
conn.Execute "INSERT INTO myado(id,name,txt) values(115,'100','venu')"
conn.Execute "INSERT INTO myado(id,name,txt) values(116,'200','MySQL')"
conn.Execute "INSERT INTO myado(id,name,txt) values(117,'300','Delete')"


Set oRst_test = New ADODB.Recordset
'oRst_test.CursorLocation = adUseServer
oRst_test.CursorLocation = adUseClient

'rs insert
oRst_test.Open "select * from myado", conn, adOpenDynamic, adLockOptimistic
'oRst_test.Open "select * from myado", conn, adOpenKeyset, adLockPessimistic

'Debug.Print oRst_test.RecordCount
'oRst_test.MoveFirst
'Debug.Print String(50, "-") & "Initial my_ado Result Set " & String(50, "-")
'For Each fld In oRst_test.Fields
'Debug.Print fld.Name,
'Next
'Debug.Print

oRst_test.AddNew
oRst_test!ID = 22
oRst_test!Name = "phb"
oRst_test!txt = "Insert row"
oRst_test!dt = Date
oRst_test!tm = Time
'oRst_test!Rate = 1.000011
'oRst!Type = 10
'oRst!amount = 100
oRst_test.Update
oRst_test.Close

'rs update
oRst_test.Open "SELECT * FROM myado"
oRst_test!Name = "phb"
oRst_test!txt = "updated-row"
oRst_test.Update
oRst_test.Close

'rs update second time..
oRst_test.Open "SELECT * FROM myado"
oRst_test!Name = "update"
oRst_test!txt = "updated-second-time"
oRst_test.Update
oRst_test.Close

'rs delete
oRst_test.Open "SELECT * FROM myado"
oRst_test.MoveNext
oRst_test.MoveNext
oRst_test.Delete
oRst_test.Close

'fetch the updated table ..
oRst_test.Open "SELECT * FROM myado", conn
Debug.Print oRst_test.RecordCount
oRst_test.MoveFirst
Debug.Print String(50, "-") & "Updated my_ado Result Set " & String(50, "-")
For Each fld In oRst_test.Fields
Debug.Print fld.Name,
Next
Debug.Print

Do Until oRst_test.EOF
For Each fld In oRst_test.Fields
Debug.Print fld.Value,
Next
oRst_test.MoveNext
Debug.Print
Loop
oRst_test.Close
conn.Close
Set oRst_test = Nothing
Set conn = Nothing