-
- ' Welcome to GuLuCoM's How to ADODB easy.
-
- ' Please tell me what u think about this How To.... gulucom@hotmail.com
-
- ' -> 3 ways to use ADODB :
-
- Dim cn As ADODB.Connection
- Dim rs As ADODB.Recordset
- Dim cmd As ADODB.Command
-
-
- '------------- Open the Database ----------------
- Set cn = New ADODB.Connection
- cn.ConnectionString = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source=TaBaS.mdb;Persist Security Info=False"
- cn.Open
-
- 1.
- '-------------- Create a Command --------------
- Set cmd = New ADODB.Command
- cmd.CommandText = "authors" ' table in the database
- cmd.CommandType = adCmdTable
- cmd.ActiveConnection = cn
-
- '--------------- Create a Recordset --------------------
- Set rs = New ADODB.Recordset
- Set rs.Source = cmd
- rs.Open
-
- 2.
- Set rs = cn.Execute("select * from Table") ' Execute opens the recordset
-
- 3.
-
- Set rs = New ADODB.Recordset
- rs.CursorType = adOpenKeyset
- rs.LockType = adLockOptimistic
- rs.Source = "TbEscort"
- rs.ActiveConnection = cn
- rs.Open
-
-
- ' Now let's play with our recordset (rs)
-
- ' - to add a new row in the recordset :
-
- rs.AddNew
- rs.Fields("First_Name") = x
- rs.Update
-
- ' - to keep the reference of a specific record
- Dim varBKM as Variant
- varBKM = rs.Bookmark
-
- ' - to move in the recordset
- rs.MoveNext
- rs.MoveLast
- ......
-
- ' - to find a specific record
- rs.Find "worker_name LIKE 'r*'"
-
- ' - to filter the recordset
- rs.Filter = "quantity > 30"
-
- ' - to sort the recordset
- rs.Sort = "order_date ASC"
-
- ' - to delete a record
- rs.Delete adAffectCurrent ' -> default
- adAffectGroup ' -> after filter
- adAffectAll
-
- ' - to rexecute the query
- rs.Requery
-
- ' - to update a field in the record
- rs.Update "worker_name", "david"
-
- ' - to save the recordset
- rs.Save "C:/MyRS.adtg", adPersistADTG
- or with ADO 2.1 rs.Save "C:/MyRS.xml", adPersistXML
-
- ' - to open
- rs.Open "C:/MyRS.adtg", , adOpenStatic, adLockOptimistic, adComdFile
-
- ' - NEVER FORGET TO CLOSE
- rs.Close
- cn.Close
- Set rs = Nothing
- Set cn = Nothing
-
-
-
' Welcome to GuLuCoM's How to ADODB easy.
' Please tell me what u think about this How To.... gulucom@hotmail.com
' -> 3 ways to use ADODB :
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
'------------- Open the Database ----------------
Set cn = New ADODB.Connection
cn.ConnectionString = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source=TaBaS.mdb;Persist Security Info=False"
cn.Open
1.
'-------------- Create a Command --------------
Set cmd = New ADODB.Command
cmd.CommandText = "authors" ' table in the database
cmd.CommandType = adCmdTable
cmd.ActiveConnection = cn
'--------------- Create a Recordset --------------------
Set rs = New ADODB.Recordset
Set rs.Source = cmd
rs.Open
2.
Set rs = cn.Execute("select * from Table") ' Execute opens the recordset
3.
Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Source = "TbEscort"
rs.ActiveConnection = cn
rs.Open
' Now let's play with our recordset (rs)
' - to add a new row in the recordset :
rs.AddNew
rs.Fields("First_Name") = x
rs.Update
' - to keep the reference of a specific record
Dim varBKM as Variant
varBKM = rs.Bookmark
' - to move in the recordset
rs.MoveNext
rs.MoveLast
......
' - to find a specific record
rs.Find "worker_name LIKE 'r*'"
' - to filter the recordset
rs.Filter = "quantity > 30"
' - to sort the recordset
rs.Sort = "order_date ASC"
' - to delete a record
rs.Delete adAffectCurrent ' -> default
adAffectGroup ' -> after filter
adAffectAll
' - to rexecute the query
rs.Requery
' - to update a field in the record
rs.Update "worker_name", "david"
' - to save the recordset
rs.Save "C:/MyRS.adtg", adPersistADTG
or with ADO 2.1 rs.Save "C:/MyRS.xml", adPersistXML
' - to open
rs.Open "C:/MyRS.adtg", , adOpenStatic, adLockOptimistic, adComdFile
' - NEVER FORGET TO CLOSE
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing