Convert DAO QueryDef to ADO 1

[bradmaunsell]

I often use the follwing DAO routine with a form when I want to do something with a recordset (the recordset used as the datasource for a form). For example, I may want to append a transaction file with records shown on a subform I have open. This could be an accts payable subsidiary application where I want to append multiple line-item records to the general ledger application.

This following DAO code works fine.
Anybody know how to convert this to ADO?

Sub rs_qdf()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set dbs = CurrentDb()

Set qdf = dbs.QueryDefs("cboGL_Rollups")
strSQL = qdf.SQL
strSQL = strSQL & "WHERE RupDesc = " & Forms!frmDE_Hdr!RupID
Set rs = dbs.OpenRecordset(strSQL)

Do Until rs.EOF
MsgBox rs!RupDesc
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
dbs.Close
Set dbs = Nothing
End Sub

**********************************************************

FYI: Unrelated to above question but just so you can see how I handle a "regular" ADO recordset. This is the routine I often use for various ADO recordset processing.

Sub ADO_Recordset()
Dim rs As New ADODB.Recordset
Dim strSQL As String

Set rs = New ADODB.Recordset

strSQL = "SELECT tblGL_Accts.AcctID, tblGL_Accts.AcctDesc FROM tblGL_Accts WHERE tblGL_Accts.AcctID < 20000"

rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdText 'strSQL Data Source

Do Until rs.EOF
MsgBox rs!AcctDesc
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
End Sub


Thanks,

Brad
South Burlington, Vermont

 

[cmmrfrds]

Use ADOX to work with objects such as tables, queries, procedures, etc....

A couple of examples.

Public Function testAccess()
Dim cn As New adodb.Connection, sql1 As String
Dim rs As adodb.Recordset, connString As String, bdate As String
Set rs = New adodb.Recordset
connString = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Atemp\CADOTests.mdb;" & _
"Persist Security Info=False"

''- Microsoft ADO Ext. 2.6 for DDL and Security
Dim cg As New ADOX.Catalog
Set cg.ActiveConnection = CurrentProject.Connection

Dim v As View
Dim vn As View
''For Each v In cg.Views
''Debug.Print "views = "; v.Name
'' If v.Name = "query1" Then
'' Set vn = v
'' End If
''Next
Set vn = cg.Views("query1")

''cn.Open connString
Set cn = CurrentProject.Connection
cn.Execute vn.Name
'OR
''rs.Open vn.Name, connString, adOpenForwardOnly, adLockReadOnly

End Function

Function ADOTables()
'-- set reference to ADOX library
'- Microsoft ADO Ext. 2.6 for DDL and Security
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO

Dim cg As New ADOX.Catalog
Dim tb As New ADOX.Table

Set cg.ActiveConnection = CurrentProject.Connection

For Each tb In cg.Tables
Debug.Print "table = "; tb.Name
Debug.Print "type = "; tb.Type
Next

End Function

 

[bradmaunsell]

Thanks for the reply. I am new to ADO so it took me while to digest this info. Although I came up with a differemnt solution, this trigger some ADOX research that helps me with other projects.

 

[MacDaddyinAustin]

I, too, am a regular user of the DAO object model for accessing recordsets who would like to learn the ADOX methods. But I have had limited success finding any extensive discussion about it. Can anyone suggest a thread or other reference which I could use to study and learn? Thanx.

 

[cmmrfrds]

http://msdn.microsoft.com/library/d...susersappendchangepasswordmethodsexamplex.asp

 

[LittleSmudge]

A GOOD link cmmrfrds.

I'm going to be browsing that patch for a while to come .. ..



Your vote for cmmrfrds for TipMaster of the Week has been submitted.

Thank you for letting cmmrfrds know this post was helpful.



G.