Using a query instead of a SQL string with DAO

[GraeagleBill]

Using Allen's DAO sample as a frame of reference (below), what is the syntax for OpenRecordset(????) when a query is used instead of SQL string?
Thanks,
Bill


Function DAORecordsetExample()
Dim rs As DAO.Recordset
Dim strSql As String

strSql = "SELECT MyField FROM MyTable;"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)

Do While Not rs.EOF
Debug.Print rs!MyField


rs.MoveNext
Loop

rs.Close
Set rs = Nothing
End Function

[June7]

It's the same. Source of recordset can be query just as well as table (except for UNION query, VBA doesn't seem to like that). http://msdn.microsoft.com/en-us/libr.../ff820966.aspx

[GraeagleBill]

Hummmm! That's what I thought might be the case, so tried:

Set rs = DBEngine(0)(0).OpenRecordset(QBroadcastGroups)

But VBA debug reports that the query QBroadcastGroups is "Empty". Yet, when I run the QBroadcastGroups query directly it functions perfectly. The error diagnostic reports that jet can't find the table or query. I've checked the spelling 5 times and even did a copy/paste of the query name, but to still no success.

[GraeagleBill]

When I set a string to the queries underlying SQL, the function works as desired. Here's the successful code:

Function DAORecordsetExample()
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT DISTINCT Groups.GroupID, Groups.GroupName, Groupings.GroupPhoneBroadcast"
strSQL = strSQL & " FROM Groups INNER JOIN Groupings ON Groups.GroupID = Groupings.GroupID"
strSQL = strSQL & " WHERE (((Groupings.GroupPhoneBroadcast) = True)) ORDER BY Groups.GroupName;"

Set rs = DBEngine(0)(0).OpenRecordset(strSQL)
'Set rs = DBEngine(0)(0).OpenRecordset(QBroadcastGroups)

Do While Not rs.EOF
Debug.Print rs!GroupName
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
End Function

[June7]

Put the table or query name within quotes as in the example shown in link.

[GraeagleBill]

I have no idea how I'd dismissed that thought earlier. It has to be the way OpenRecordset examines its parameter. It must be that OpenRecordset first takes the string and tries to resolve with a quick lookup amongst the queries and failing that parses the string for SQL command structure.

Thanks,
Bill

[ChrisO]

Turn Option Explicit on.

QbroadcastGroups is a variable and without Option Explicit it is defined as a Variant and initialized to Empty.

Chris.

[June7]

Yes, Bill.

No, Chris, is not a variable, is name of saved Access query object.

[GraeagleBill]

Just as a FYI, I always code my modules "Option Explicit" so I'll catch problems whenever I compile.

Just "grasping for straws", I substituted the SQL statement directly but got the same result.

Dim strSQL As String

strSQL = "SELECT Registry.RegistryID, Registry.LastName, Registry.FirstName, Registry.HomePhone, Registry.CellPhone, Registry.RegAs, Registry.Regular"
strSQL = strSQL & " FROM Registry WHERE (((Registry.HomePhone) Is Not Null) AND ((Registry.RegAs)= 'Hd of HseHold') "
strSQL = strSQL & " AND ((Registry.Regular)=True)) OR (((Registry.CellPhone) Is Not Null) AND ((Registry.RegAs)= 'Hd of HseHold') AND ((Registry.Regular)=True));"

Set rsGrpALL = DBEngine(0)(0).OpenRecordset(strSQL)

'Set rsGrpALL = DBEngine(0)(0).OpenRecordset("QBroadcastGroupALL")
iRow = rsGrpALL.RecordCount 'How many Registry entries found
jCol = iGroups + 5

[June7]

Did you say in post4 that the strSQL variable was working?

Only used DBEngine couple times and only with OpenDatabase, not OpenRecordset, so not sure what is wrong. Why did you choose to use it? Want to provide db for analysis? Follow instructions at bottom of my post. Is this where you got example code from http://allenbrowne.com/func-dao.html?

[ChrisO]

From post #4…

Function DAORecordsetExample()
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT DISTINCT Groups.GroupID, Groups.GroupName, Groupings.GroupPhoneBroadcast"
strSQL = strSQL & " FROM Groups INNER JOIN Groupings ON Groups.GroupID = Groupings.GroupID"
strSQL = strSQL & " WHERE (((Groupings.GroupPhoneBroadcast) = True)) ORDER BY Groups.GroupName;"

Set rs = DBEngine(0)(0).OpenRecordset(strSQL)
'Set rs = DBEngine(0)(0).OpenRecordset(QBroadcastGroups)

Do While Not rs.EOF
Debug.Print rs!GroupName
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
End Function

In the line which is commented out, QbroadcastGroups (without quotes) is not declared and would not have compiled unless QbroadcastGroups (as a variable) is declared outside the procedure or Option Explicit was turned off at the time.

Post #1
>>Using Allen's DAO sample as a frame of reference<<

Post #3
>> But VBA debug reports that the query QBroadcastGroups is "Empty".<<

Post #5
>>Put the table or query name within quotes as in the example shown in link.<<

Post #6
>>I have no idea how I'd dismissed that thought earlier.<<
>> It has to be the way OpenRecordset examines its parameter.<<

Post #7.
>>QbroadcastGroups is a variable and without Option Explicit it is defined as a Variant and initialized to Empty.<<

Post #9
>> Just as a FYI, I always code my modules "Option Explicit" so I'll catch problems whenever I compile.<<




Please explain the following:-
Why are you using Allen's DAO sample as a frame of reference in post #1?
Why did debug report Empty in post #3?
How the quotes were missing around QbroadcastGroups in post #4.
The suggested fix in post #5 worked.
That fix was confirmed in post #6.
Why must it be in post #6.
Why you would need to ‘always code my modules’ in post #9 if it was turned on by default?

Are you now saying QbroadcastGroups as a variable (without the quotes) was declared outside of the procedure?
Are you still saying that it must be something else?

Chris.

[John_G]

June at post #8 -

No, Chris, is not a variable, is name of saved Access query object.

Chris is correct. QbroadcastGroups is interpreted as the name of a query/table only if it is in quotation marks, otherwise it must be a (string) variable containing the name of a table/query (or SQL).

John


GraeagleBill:

Set rs = DBEngine(0)(0).OpenRecordset("QBroadcastGroups")

should not present a problem.

John.

[ChrisO]

If a person can say that it must be something or it has to be something then why do they have to go to a site to get the code? Then why do they have to go to another site to get that code to work for them?

Must be and has to be are both levels of confidence that are not logically supported by the fact that they have to go to sites for answers.

People, at all levels of development, need to keep an open mind when they feel the need to ask a question. Unless the question is rhetorical they can not logically say it must be this or it has to be that else they would have solved their own problem and not asked someone else for an answer.

Chris.

[GraeagleBill]

Bottom line here is that the use of the query as input to OpenRecordset works fine when the name of the query is in quoted, e.g., OpenRecordset("QBroadcastGroupALL").

And, my problem with the record count was fixed thus:
rsGrpAll.MoveLast
iRow = rsGrpALL.RecordCount

And yes, iGroups is global to the module

I code Options Explicit each time I code a module. If that can be a default for all modules when being created, then I haven't ever seen it.

I'll look at the other questions in the morning..........it's 8PM in California

[John_G]

GraeagleBill -

"If that can be a default for all modules when being created, then I haven't ever seen it."

It's not very obvious. While in the VBA editor, go to Tools - Options. On the Editor tab, select the option "Require Variable Declaration". That results in Option Explicit being included in every new module.

John