How to run a loop of queries in access?

Question

Hi

I have a database with a table which is full of conditions and error messages for checking another database.

I want to run a loop such that each of these conditions is checked against all the tables in the second database and generae a report which gives the errors.

Is this possible in ms access.

For example,

querycrit table

id           query                                 error    
1           speed<25 and speed>56              speed above limit  
2           dist<56 or dist >78                dist within limit

I have more than 400 queries like this of different variables.

THe table against which I am running the queries is

records table

id   speed     dist    accce   decele   aaa   bbb     ccc
1     33        34      44         33   33     33      33
2     45        44      55         55   55     22      23

regards ttk

Answer 1

When you say "report", do you mean an Access Report, or would writing to a file or Access Form work?

You can create a function or sub in a Module to do this. Open a recordset on your querycrit table and spin through the records dynamically building and running the SQL for the records table. You can write the results of these dynamic queries to a file, or a form, or insert the results into a temp table and drive the Access Report from there.

Answer 2

thanks for the reply

But how do i actually loop the query do i take it in a string and do a loop ?

can anyone provide me a sample ?

Answer 3

Here is some sample code, it is typed, not tested.

Dim rs AS DAO.Recordset
Dim rs2 AS DAO.Recordset

Set rs=CurrentDB.OpenRecordset("querycrit")

strSQL="SELECT * From Records WHERE "
Do While Not rs.EOF
   Set rs2=CurrentDB.OpenRecordset(strSQL & rs![Query])
   If Not rs2.EOF Then
       Debug.Print rs![Error]
       Debug.Print rs2.Fields(1)
   End If

   rs.MoveNext
Loop

Answer 4

tHANKS FOR THE REPLY

but there is a bit more complications

actually there are many record tables to be checked and not all queries can be run on all tables, for eg in one table speed may not be there and in next table distance may not be there.

due to this i am getting error 3061 too less parameters

i think the code should be changed such that if the query criteria paramerters are bnot in the table then move to next table till ll tables and then exit. Is this right or is something esle should be done

Answer 5

"actually there are many record tables to be checked and not all queries can be run on all tables, for eg in one table speed may not be there and in next table distance may not be there."

The correct think to do, I think, would be to create a table of tables and a query-table junction table that shows which queries are to be run on which table, for example:

TableID  QueryID
1           4
2           1
2           3
3           1

This can the be used to run the correct set of queries on each table.

Answer 6

Hmm thats an idea but i have over 300 queries with 3o to 40 tables and it would not be feasible to map them. also i cannot expect the next databse i get to be of same format.

I have tried to modify the above code to get the control to next table when the query paramerters are not found in firts table and so on. but i am sure there is some thing wrorng with the loop i cannot put my hand on

Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset  ' for the tables


Set rs = CurrentDb.OpenRecordset("querycrit")
Set rs3 = table1
strSQL = "SELECT * From rs3 WHERE "
nexttable:
If rs = rs3.Fields Then
Do While Not rs.EOF
   Set rs2 = CurrentDb.OpenRecordset(strSQL & rs![query])
   If Not rs2.EOF Then
       Debug.Print rs![error]
       Debug.Print rs.GetString
       End If
    rs.MoveNext
Loop
Else: Do While Not rs3.EOF
rs3.MoveNext
GoTo nexttable

Answer 7

Here is some more sample code. It illustrates the use of two different types of recordsets. You may wish to read VBA Traps: Working with Recordsets by Allen Browne and List of reserved words in Access 2002 and in later versions of Access .

Dim rs As DAO.Recordset
Dim rs2 As ADODB.Recordset

Set rs = CurrentDb.OpenRecordset("querycrit")
Set rs2 = CreateObject("ADODB.Recordset")
rs2.ActiveConnection = CurrentProject.Connection
For Each tdf In CurrentDb.TableDefs
'EDIT: TableDefs includes Microsoft System tables and '
'these should never be tampered with. They all begin with Msys '
'so we can leave them out of the loop here. '
   If Left(tdf.Name, 4) <> "msys" And tdf.Name <> "querycrit" Then
        rs.MoveFirst
        strSQL = "SELECT * From [" & tdf.Name & "] WHERE "

        Do While Not rs.EOF
            On Error Resume Next
            Debug.Print tdf.Name
            rs2.Open strSQL & " " & rs![query]
            If Err.Number = 0 Then
                On Error GoTo 0
                If Not rs2.EOF Then
                    Debug.Print rs![Error]
                    Debug.Print rs2.GetString
                End If
            End If
            Err.Clear
            rs2.Close
            rs.MoveNext

        Loop
    End If
Next
End Sub

Answer 8

hello i have some problem in ms access 2007 i want convert 12°31'51" this value into decimal value using query method