Author Topic: Converting DAO to ADO  (Read 1878 times)

0 Members and 1 Guest are viewing this topic.

Kheilmann

  • Guest
Converting DAO to ADO
« on: January 25, 2007, 11:05:24 AM »
I am just looking for the ADO equivalent to my often used DAO code:(Dims, statements, sequence, etc.)

Dim strSQL as string
Dim recObj as Dao.Recordset
Dim dbObj as DAO.Recordset

Set dbObj = CurrentDb()

strSQL = "Select * From tblTable Where ....."
Set recObj = dbObj.OPenrecordset(strSQL, dbOpenDyanset)

Also, are there any other settings in ADO that I absolutely need to know about??

I appreciate the help.  I've been putting this off for way too long now. 


Fatty

  • Guest
Re: Converting DAO to ADO
« Reply #1 on: January 25, 2007, 01:58:09 PM »
Here is a working example I use
Don't forget about reference to
Microsoft ActiveX Data Objects 2.XX Library
Hth

Code: [Select]
Option Compare Database
Option Explicit

Sub PopupADORecord()

     Dim cnn As ADODB.Connection
     Dim rst As ADODB.Recordset
     Dim fld As ADODB.Field
     Dim dbpath As String
     Dim strSQL As String

     On Error GoTo Err_Control

     Set cnn = New ADODB.Connection
     Set rst = New ADODB.Recordset
     ' change on your current data base full path here:
     dbpath = "C:\DataServer\Lots\Prim.mdb"
     cnn.ConnectionString = " Provider=" & _
                            " Microsoft.Jet.OLEDB.4.0;" & _
                            " Password="""";" & _
                            " Data Source=" & dbpath & ";" & _
                            " Persist Security Info=False"

     cnn.Open
     ' change SQL string on what you need:
     strSQL = "SELECT  * FROM Moorages WHERE " & _
              "(Amount > 15000) And " & _
              "(Type = ""Annual"")"
     rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly

     Do While Not rst.EOF
          For Each fld In rst.Fields
               Debug.Print fld.Name
               Debug.Print fld.Value
          Next fld
          rst.MoveNext
     Loop

Exit_Here:

     rst.Close
     cnn.Close
     Set rst = Nothing
     Set cnn = Nothing
     Exit Sub

Err_Control:
     MsgBox Err.Description
     Resume Exit_Here

End Sub

~'J'~
« Last Edit: January 25, 2007, 03:46:08 PM by Fatty »

Fatty

  • Guest
Re: Converting DAO to ADO
« Reply #2 on: January 25, 2007, 03:02:54 PM »
In addition to my prior reply
If you need to run a similar sub from
current database, try this one

Code: [Select]
Sub PopupFromCurrent()

     Dim cnn As ADODB.Connection
     Dim rst As ADODB.Recordset
     Dim fld As ADODB.Field
     Dim strSQL As String

     On Error GoTo Err_Control

     Set cnn = CurrentProject.Connection
     Set rst = New ADODB.Recordset

     strSQL = "SELECT  * FROM Moorages WHERE " & _
              "(Amount > 1400) And " & _
              "(Type = ""Annual"")"

     rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly

     Do While Not rst.EOF
          For Each fld In rst.Fields
               Debug.Print fld.Name
               Debug.Print fld.Value
          Next fld
          rst.MoveNext
     Loop

Exit_Here:

     rst.Close
     cnn.Close
     Set rst = Nothing
     Set cnn = Nothing
     Exit Sub

Err_Control:
     MsgBox Err.Description
     Resume Exit_Here

End Sub

~'J'~
« Last Edit: January 25, 2007, 03:45:30 PM by Fatty »