access vba: Runtime error 3734

Question

Hi,

Can anyone give me details of runtime error 3734 in Access vba. For reference i am getting it from a code in the following thread

http://stackoverflow.com/questions/233026/how-to-run-a-loop-of-queries-in-access

Sub plausibt_check()

Dim rs As DAO.Recordset
Dim rs2 As ADODB.Recordset
Dim db As database
Dim strsql As String
Dim tdf As TableDef




Set db = opendatabase("C:\Codebook.mdb")
Set rs = db.OpenRecordset("querycrit")

Set rs2 = CreateObject("ADODB.Recordset")
rs2.ActiveConnection = CurrentProject.Connection


For Each tdf In CurrentDb.TableDefs ' in this line the error occurs

Answer 1

It seems that you are using ADO in the current database without saving. You must save before running code that contains ADO.

Answer 2

I don't understand what you're trying to do. Why are you using one DAO recordset and one ADO? This makes no sense at all. If you have saved queries in an Access front end, then even if your back end is, say, SQL Server with ODBC table links, there is really no utility whatsoever in using ADO.

There is no evidence of a loop in your code, so unless your code is being called by a loop, it doesn't seem to me that the KB article explanation would apply.

I don't know what it is you want to do, but the point about opening your database once, rather than in each repetition of your loop, should be pretty obvious to anyone who thinks about it. If you're running a loop and repeatedly opening the same database in each repetition of the loop, it should be obvious that the operation belongs outside the loop.

That would be something like this:

  Dim db As DAO.Database
  Dim qdf As DAO.QueryDef

  Set db = CurrentDB()

  For Each qdf in db.QueryDef
    [do whatever here]
  Next qdf

  Set qdf = Nothing
  Set db = Nothing

In that code, you're using the MDB currently open in the user interface, but it doesn't matter -- whichever database you're opening and looping through its objects should be opened only once, outside the loop.

If you want to have your loop be in a subroutine called from your main code, then pass the database variable as an argument to your subroutine. The subroutine would be something like this:

  Public Sub ProcessQueries(db As DAO.Database)
    Dim qdf As DAO.QueryDef

    For Each qdf in db.QueryDef
      [do whatever here]
    Next qdf

    Set qdf = Nothing
  End Sub

And you would call that thus:

  Dim db As DAO.Database

  Set db = CurrentDB()    
  Call ProcessQueries(db)    
  Set db = Nothing

Now, if you insist on getting source data from DAO and then doing something with it via ADO, you'd have a DAO loop and inside it, and ADO loop. Because of that, you'd want to define your ADO connection outside your DAO loop, rather than inside it. The only exception to that would be if the data you're pulling from your DAO loop defines which database you're opening with ADO. As we don't know what you're actually trying to accomplish, it's pretty much impossible to give good advice on exactly what you should change in your code.

--
David W. Fenton
David Fenton Associates