ADODB - Using Queries ?? Supported ??

[jconfused]

I do not appear to be able to create an ADO recordset using an Access Query as the source.The previous method being ... creating a recordset using the QueryDef object ?? Does anyone know if this approach is supported under ADO or not -- or do I have to use DAO ?? If it is supported under ADO what would the syntax be ??

 

[cmmrfrds]

You can select from a query just like you would from a table in an Access database. EXAMPLE

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:\AtestDir\ADOTests.mdb;" & _
"Persist Security Info=False"

sql1 = "select * from query1"

Debug.Print sql1
rs.Open sql1, connString, adOpenForwardOnly, adLockReadOnly
Debug.Print rs.EOF
If Not rs.EOF Then
Debug.Print rs.Fields(0).Name
Debug.Print rs.Fields(0).Value
Debug.Print rs.Fields(1).Name
Debug.Print rs.Fields(1).Value
End If

End Function

 

[maggieb]

I have the same question - how do you reference an existing query?

 

[cmmrfrds]

Here is ONE way.
''- 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

''Debug.Print sql1
rs.Open vn.Name, connString, adOpenForwardOnly, adLockReadOnly