Vba access: runtime error 3734
Can anyone give me information about runtime 3734 in Access vba. For reference, I am getting it from the code in the following thread
How do I start a request loop on 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
source to share
I don't understand what you are trying to do. Why are you using one DAO Recordset and one ADO? It doesn't make any sense. If you have stored the queries in the Access interface, then even if your back end is, say, SQL Server with ODBC table references, there really is no usefulness in using ADO.
There is no loop proof in your code, so if your code is not called by a loop, it doesn't seem to me that the explanation from the KB article will apply.
I don't know what you want to do, but the question of opening your database once and not every repetition of your loop should be pretty obvious to anyone thinking about it. If you are looping and reopening the same database on each iteration of the loop, it should be obvious that the operation belongs outside the loop.
It will 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 this code, you are using an MDB that is currently open in the UI, but it doesn't matter - whichever you open the database and iterate over its objects should only open once, outside of the loop.
If you want your loop to be in a subroutine called from your main code, then pass the database variable as an argument to your subroutine. The subroutine will 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 it like this:
Dim db As DAO.Database
Set db = CurrentDB()
Call ProcessQueries(db)
Set db = Nothing
Now, if you insist on fetching raw data from the DAO and then doing something with it through ADO, you will have a DAO loop and within it and an ADO loop. Because of this, you want to define your ADO connection outside of your DAO loop, not inside it. The only exception is if the data you pull from your DAO loop determines which database you open with ADO. Since we do not know what you are actually trying to accomplish, it is almost impossible to give good advice on what you should change in your code.
source to share