Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Opening Query In Subroutine (Access 2000)

    I'm trying to write a subroutine that opens a query and processes its records.

    In the past (i.e. Access 2), I did this via the following code:

    Sub mySub(param as variant)

    Dim Cur_Database As Database
    Dim Cur_Query As Recordset
    Dim Query_Name As Variant
    Dim Query_Criteria As Variant

    Query_Name = "myQuery"
    Query_Criteria = "field1 = " & param

    Set Cur_Database = DBEngine.Workspaces(0).Databases(0)
    Set Cur_Query = Cur_Database.OpenRecordset(Query_Name)

    Cur_Query.FindFirst Query_Criteria

    Do Until _Query.NoMatch
    :
    Cur_Query.FindNext Query_Criteria
    Loop

    However, when I try to compile this, Access reports "User defined type not defined" for the
    "Dim Cur_Database As Database" line (it appears it doesn't like the type "Database"), and "Method or data member not found" for the "Cur_Query.FindFirst" line (it appears it doesn't like ".FindFirst").

    All I want is the simplest way to open a query in a module, and process its records one at a time.

    Can anyone help? Much appreciated !

    David Schuetz-Jones
    IVL Technologies Ltd
    Victoria, BC Canada

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Opening Query In Subroutine (Access 2000)

    >>Dim Cur_Database As Database
    >>Dim Cur_Query As Recordset

    You may have trouble with above lines in Access2000, since it uses ADO by default, rather than DAO. You could remove the reference to the ADO libary from you database. Or you can use a global replace to change above code like this:

    Dim Cur_Database As DAO.Database
    Dim Cur_Query As DAO.Recordset
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Nov 2002
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening Query In Subroutine (Access 2000)

    Thanks for your response. I replaced the "As Database" and "As Recordset" with "As DAO.Database"
    and "As DAO.Recordset", but still get the same error on the "Dim ... Database" line.

    Can you give examples of the ADO commands I should be using instead (for both the "Dim Database" and ".FindFirst" commands)?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Opening Query In Subroutine (Access 2000)

    You'll still get the error unless you make sure a reference is set to the DAO 3.6 object library. If you want to use ADO, there are several approaches that will work. Here's one for use with linked or local tables in an Access 2000 mdb.

    <pre><font color=448800> ' Created by Charlotte Foust
    ' opens and returns an ADO recordset based
    ' on the SQL and properties passed</font color=448800>
    Dim rst As ADODB.Recordset <font color=448800> 'holds recordset object</font color=448800>

    <font color=448800>'initialize the object variable</font color=448800>
    Set rst = New ADODB.Recordset

    <font color=448800>'set recordset properties and
    'open the recordset </font color=448800>
    rst.CursorType = adOpenDynamic
    rst.LockType = adLockOptimistic
    rst.ActiveConnection = CurrentProject.Connection
    rst.Open "myQuery", , , , adCmdStoredProc</pre>

    Charlotte

  5. #5
    New Lounger
    Join Date
    Nov 2002
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening Query In Subroutine (Access 2000)

    1. I replaced my "open database and query" statements with yours, and that part of the subroutine now compiles. However, my "rst.FindFirst" command is still producing an error: "Compile Error: Method or data member not found."

    2. This may be a step backwards, but you mention "You'll still get the error [with the "Dim Cur_Database As DAO.Database" command] unless you make sure a reference is set to the DAO 3.6 object library. ". How do I go about doing that? Are there extra components I need to have installed in Access 2000?

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Opening Query In Subroutine (Access 2000)

    1. There is no FindFirst method in ADO, only a Find method.

    2. The DAO object library is there but it isn't referenced in Access 2000 unless you specifically select it. In the VB Editor, go to Tools-->References. Find the Item in the list that says Microsoft DAO 3.6 Object Library and check it. That will allow you to use DAO code, but you'll need to remember to specifically declare object variables using the DAO.object syntax, not just databases and recordset, but tabledefs, querydefs, properties, parameters, fields, etc.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •