Java query를 여러번 실행하는 방법

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

Taryn

238k55 gold badges362 silver badges403 bronze badges

asked Oct 24, 2008 at 10:36

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

answered Oct 24, 2008 at 13:09

FionnualaFionnuala

89.8k7 gold badges108 silver badges145 bronze badges

2

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.

answered Oct 24, 2008 at 11:01

Patrick CuffPatrick Cuff

27.8k11 gold badges67 silver badges94 bronze badges

"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.

answered Oct 24, 2008 at 12:12

FionnualaFionnuala

89.8k7 gold badges108 silver badges145 bronze badges

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

answered Oct 24, 2008 at 11:20

FionnualaFionnuala

89.8k7 gold badges108 silver badges145 bronze badges