Alternative to DAO

[FrymanSMU]

Hi everyone, not new just new username. I used to be FrymanTCU, I started going to SMU so I had to make the change...

But I'm looking for an alternative to DAO.OpenRecordset? I am trying to replicate a Query that Quereis another Query and DAO is not letting me do this. I know there has to be some easy solution that I am not aware so I figured I ask.

Thanks,
Rich

[CreganTur]

I'd say welcome to the forum... but you're not new:goofball:

ADO (Active-X Data Objects) might allow you to do what you want.

[FrymanSMU]

Hi Randy, hope all is going well. I tried using DAO and got a Run-Time 3078 Error. Below is the series of Queries using DAO and SQL. Qry_A & B are pulling information then the SpcProj Recordset is tring to count the accounts that are in both queries.

Dim SpcProj_Rst As DAO.Recordset, Qry_A As DAO.Recordset, Qry_B As DAO.Recordset

Set Qry_A = db.OpenRecordset("SELECT Tbl_LogGrouped.TransDate, Tbl_LogGrouped.BranchID, Tbl_LogGrouped.AccountNum " & _
"FROM (Tbl_LogGrouped INNER JOIN Forms ON Tbl_LogGrouped.FormID = Forms.FormID) INNER JOIN " & _
"[Classified Reasons] ON Tbl_LogGrouped.LastOfRejectCode = [Classified Reasons].[Reject Code] " & _
"WHERE (((Forms.FormGroup)='AAA') AND (([Classified Reasons].Type)='NIGO'));")

Set Qry_B = db.OpenRecordset("SELECT Tbl_LogGrouped.TransDate, Tbl_LogGrouped.BranchID, Tbl_LogGrouped.AccountNum " & _
"FROM (Tbl_LogGrouped INNER JOIN Forms ON Tbl_LogGrouped.FormID = Forms.FormID) INNER JOIN " & _
"[Classified Reasons] ON Tbl_LogGrouped.LastOfRejectCode = [Classified Reasons].[Reject Code] " & _
"WHERE (((Forms.FormGroup)='W9F') AND (([Classified Reasons].Type)='IGO'));")

Set SpcProj_Rst = db.OpenRecordset("SELECT Qry_A.TransDate, Count(Qry_A.AccountNum) AS CountOfAcctNum " & _
"FROM Qry_A INNER JOIN Qry_B ON (Qry_A.BranchID = Qry_B.BranchID) AND (Qry_A.AccountNum = Qry_B.AccountNum) " & _
"AND (Qry_A.TransDate = Qry_B.TransDate) GROUP BY Qry_A.TransDate;")

[CreganTur]

Try using ADO instead of DAO.

[FrymanSMU]

I changed DAO to ADODB. Now I get Runtime 13 Type Mismatch...

[FrymanSMU]

Do I need a new Reference for that?

[CreganTur]

You'll need a reference to Microsoft ActiveX Data Objects.

Also, you won't be able to just change the DAO keyword to ADO- they are different, so you'll have to use the correct syntax. ADO does not use the OpenRecordset method.

There are a lot of great references that provide step by step information for ADO, and Access Help has great info too.

[FrymanSMU]

Sounds like I have more HW to do tonight, thanks for the help Randy. I'll see what I can google up and let you know how it works out.

Thanks,
Rich

[OBP]

Rich, that Syntax looks OK for ADO at first glance, on what Line do you get the Type mismatch error.
The SQL Syntax is very picky about how the Field Types are handled so you might need some "" or ' ' around some field names on the right hand side of the = sign.
Like this
If Not IsNull(Me.Box) Then sWHERE = sWHERE & " AND BOX = '" & Me.Box & "' "

i.e. not directly around them as you have here
'W9F'

[FrymanSMU]

So I have figured out the ADO syntax and have opened Qry_A and Qry_B but when I try SpcProj_Rst I get Run-Time '-2147217865(80040e37)' cannot find input table or query 'Qry_A'... I have been digging around but can't find how to Query another ADO Query. If you can point me in the right direction I would appreciate it, Thanks.

Dim db2 As ADODB.Connection
Set db2 = New ADODB.Connection
db2.Open CurrentProject.Connection
Dim SpcProj_Rst As ADODB.Recordset, Qry_A As ADODB.Recordset, Qry_B As ADODB.Recordset
Set Qry_A = New ADODB.Recordset
Qry_A.Open "SELECT Tbl_LogGrouped.TransDate, Tbl_LogGrouped.BranchID, Tbl_LogGrouped.AccountNum " & _
"FROM (Tbl_LogGrouped INNER JOIN Forms ON Tbl_LogGrouped.FormID = Forms.FormID) INNER JOIN [Classified Reasons] ON Tbl_LogGrouped.LastOfRejectCode = [Classified Reasons].[Reject Code] " & _
"WHERE (((Forms.FormGroup)='AAA') AND (([Classified Reasons].Type)='NIGO'));", db2, adOpenDynamic, adLockOptimistic
Set Qry_B = New ADODB.Recordset
Qry_B.Open "SELECT Tbl_LogGrouped.TransDate, Tbl_LogGrouped.BranchID, Tbl_LogGrouped.AccountNum " & _
"FROM (Tbl_LogGrouped INNER JOIN Forms ON Tbl_LogGrouped.FormID = Forms.FormID) INNER JOIN [Classified Reasons] ON Tbl_LogGrouped.LastOfRejectCode = [Classified Reasons].[Reject Code] " & _
"WHERE (((Forms.FormGroup)='W9F') AND (([Classified Reasons].Type)='IGO'));", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Set SpcProj_Rst = New ADODB.Recordset
SpcProj_Rst.Open "SELECT Qry_A.TransDate, Count(Qry_A!AccountNum) AS CountOfAcctNum " & _
"FROM Qry_A INNER JOIN Qry_B ON (Qry_A.BranchID = Qry_B.BranchID) AND (Qry_A.AccountNum = Qry_B.AccountNum) AND (Qry_A.TransDate = Qry_B.TransDate) " & _
"GROUP BY Qry_A.TransDate;", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
If SpcProj_Rst.RecordCount > 0 Then
SpcProj_Rst.MoveFirst
Do Until SpcProj_Rst.EOF
DoCmd.RunSQL "UPDATE Tbl_DailyProcessed SET Tbl_DailyProcessed.SpecialProjects = " & SpcProj_Rst!CountOfAcctNum & " WHERE (((Tbl_DailyProcessed.TransDate)=#" & SpcProj_Rst!TransDate & "#));"
SpcProj_Rst.MoveNext
Loop
End If
Qry_A.Close
Qry_B.Close
SpcProj_Rst.Close
db2.Close

[hansup]

So I have figured out the ADO syntax and have opened Qry_A and Qry_B but when I try SpcProj_Rst I get Run-Time '-2147217865(80040e37)' cannot find input table or query 'Qry_A'... I have been digging around but can't find how to Query another ADO Query. If you can point me in the right direction I would appreciate it, Thanks.
I can't find any way, with Access 2003 SP3, to use the name of a Recordset following FROM in a SQL statement. I tried with both ADO and DAO, and with every variation I could imagine. However, in every case, Jet complains that it can't find a table or query matching the name of my recordset.

If it's possible, I will be grateful to whoever steps up to show us the way. Here's the code from my last attempt. (I get an error every time on rs2.open):

Public Sub selectFromRecordset()
Dim cn1 As ADODB.Connection
Dim cn2 As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim strSql1 As String
Dim strSql2 As String

strSql1 = "SELECT s.CustID, s.Product, s.Amount FROM Sampledata AS s;"
Set cn1 = CurrentProject.Connection
Set rs1 = New ADODB.Recordset
rs1.Open strSql1, cn1
Debug.Print "rs1.Fields(0).Name: " & rs1.Fields(0).Name

strSql2 = "SELECT t.CustID FROM rs1 AS t;"
Set cn2 = CurrentProject.Connection
Set rs2 = New ADODB.Recordset
rs2.Open strSql2, cn2
Debug.Print "rs2.Fields(0).Name: " & rs2.Fields(0).Name

rs1.Close
Set rs1 = Nothing
Set cn1 = Nothing
rs2.Close
Set rs2 = Nothing
Set cn2 = Nothing
End Sub

[OBP]

What you should do is use the first 2 SQL Statements to actually create 2 QueryDefs, which creates an Actual Query, which can then be referred to in your 3rd query.
Like this dynamic report version :-
Dynamic Report Query

Private Sub Command2_Click()
Dim rs As Object, sql As String, qdfNew As Object
With CurrentDb
.QueryDefs.Delete "NewQueryDef"

Set qdfNew = .CreateQueryDef("NewQueryDef", _
"SELECT * FROM Categories WHERE [CategoryID]> 10 ")
DoCmd.OpenReport "Categories Query", acViewPreview
End With
End Sub