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

Jim Lawrence (AccessD) accessd at shaw.ca
Thu Oct 30 22:59:54 CST 2003


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



More information about the AccessD mailing list