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