[AccessD] Win 2K; open an existing query in code

>I just want to know in code if the query will return any rows or not.

Liz,
If all you need is a count, try:

lngCount=Dcount("primary_key_field","qry_something") 
If lngCount > 0 then ' you have rows

There's even an optional where clause available as the third parameter
for the function, so you can count how many in the query/table meet a
certain criteria.

Steve




-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
(AccessD)
Sent: Friday, October 31, 2003 12:00 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Win 2K; open an existing query in code


Hi Liz:

How's it going? Just got back from my DBA course.

I am not sure what you fully mean but I will assume you mean, 'How can
can a existing MS Access query to be used to create/populate a
recordset. A query does not return rows because it is just a sequel
script but that script can be used to generate the data for a recordset
as follows:

Public Function GetInvoice() as Boolean
 Dim strConnection As String
 Dim cmdInvoice As ADODB.Command
 Public rsInvoice As ADODB.Recordset

 Set rsInvoice = New ADODB.Recordset
 Set cmdInvoice = New ADODB.Command

 strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; _
               Persist Security Info=False; _
               Data Source= MyDatabaseNameAndLocation"

GetInvoice = False

' accessing a parameterized query in a MDB database.
' If the query does not have parameters just remove the
' .Parameter row from the Command settings.
With cmdInvoice
 .ActiveConnection = strConnection
 .CommandText = "TheNameofMyMDBQuery"
 .CommandType = adCmdStoredProc
 .Parameters.Append .CreateParameter("InvoiceCode", adChar,
adParamInput, 10, strInvoiceCode) End With

With rsInvoice
 .CursorLocation = adUseClient
 .LockType = adLockBatchOptimistic
 .Open cmdInvoice
 If .EOF = False And .BOF = False Then
  .MoveLast
  GetInvoice = True
 End If
End With

End Function

Assuming the query was going to return some rows, your recorset is now
populated.

HTH
Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Elizabeth
Carter
Sent: Thursday, October 30, 2003 1:42 PM
To: Access List
Subject: [AccessD] Win 2K; open an existing query in code


Hi All,

I have tried searching the archives and have not found the answer that I
seek... probably means answer is no :)

I would like to know if it's possible to open an existing query and
store it's output into a recordset. I don't want a form to open that the
user sees, I just want to know in code if the query will return any rows
or not.

Any help would be greatly appreciated.

Liz
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com