running sql code record by record instead of statement by statement 
Author Message
 running sql code record by record instead of statement by statement

We are attempting to run a series of statements against a table called
transactions, with primary key sequence, and want to run the statements record by
record (sql1-3 on first record, sql1-3 on the second...) instead of the usual
running statement by statement (sql1-3 on all records, sql1-3 on all records..).  
We need this because new records from sql2 must be part of the sql1 test on each
subsequent record.  The sql statements run fine, the question is how to pull the
current record in the loop into the sql statement as an additional where.  Below
is the current code, thanks in advance anybody that can help us.

Function importdatacode()

dim rs as recordset
Dim sql1 As String
Dim sql2 As String
dim sql3 as string

            'sql 1 is make matches

            sql1 = "UPDATE Transactions INNER JOIN masterNEW ON
Transactions.SSN = " & masterNEW.SSN" _
            & " AND Transactions.LAST = masterNEW.LAST " _
& " SET Transactions.MasterID = " _
            & " masterNEW.MasterID WHERE Transactions.MasterID Is Null;"
' sql 2 makes it a new master when it fails tests

Transactioncamefrom) select " & _
"SELECT Transactions.LOCATION, Transactions.FIRST, Transactions.LAST,
Transactions.MIDDLE, Transactions.sequence" _
& "WHERE Transactions.MasterID Is Null AND Transactions.Review=False;")

sql3 = "UPDATE masterNEW INNER JOIN Transactions ON masterNEW.Transactioncamefrom
= Transactions.SEQUENCE SET Transactions.MasterID = masterNEW.MasterID;"

Set rs = CurrentDb.OpenRecordset("transactions")
Do Until rs.EOF
           DoCmd.SetWarnings False
           DoCmd.RunSQL (sql1)
' this one give transactions a master id if they matched on sql1
           DoCmd.RunSQL (sql3)
           DoCmd.RunSQL (sql2)

        rs.Close: Set rs = Nothing

End Function

Thu, 09 Oct 2003 22:55:19 GMT  
 running sql code record by record instead of statement by statement

Hi Bruce!

I've got 2 advices. The first will help you, but not answer your question.
The second may help you, if I understood your question right.

1. Please never use DoCmd.SetWarnings and DoCmd.RunSQL.
Because SetWarnings False sets Access in a state, that is different then the
default Access behavior. When a user accidentaly executes an action query,
then the query will run without asking the user. The second reason is, that
Access runs the query with DoCmd.RunQuery in the same way, a user is
executing the query by hand. There is a lot of overhead involved, that you
don't need and that costs a lot of processing time. A better and faster way
is to use the Execute statement:

    Dim DB as Database
    Set DB = CurrentDB
    DB.Exectue "Update ....", dbFailonError
    DB.Execute "Insert ... ", dbFailonError
    Set DB=Nothing

2. To built your own Where clause is realy easy. You already have your loop
through the records.
    But you have to take care, if your strSQL1 string already contains a
where clause, than you have to append " AND ID=..." instead of building
"WHERE ID=...".

Dim DB as Database
Set DB = CurrentDB

Set rs = DB.OpenRecordset("transactions")
'rs.MoveFirst   'You don't need this, because you already are on the first
Do Until rs.EOF
    strWhere = "WHERE SQL.ID = " & CStr(rs!ID)

   DB.Execute strSQL1 & strWhere, dbFailOnError
' this one give transactions a master id if they matched on sql1
   DB.Execute sql3, dbFailonError
   DB.Execute sql2, dbFailonError


Set rs = Nothing
Set DB = Nothing

Fri, 10 Oct 2003 20:03:32 GMT  
 [ 2 post ] 

 Relevant Pages 

1. Counting records returned by SQL Select statement

2. Counting Records in VB w/SQL statements

3. How Do I get the previous record in an SQL statement

4. Help on SQL statement: all records except the ones on the current month

5. SQL Statement does not return records

6. SQL Statement to find records for specific month

7. SQL-statement to delete some records

8. Exclude a record from a database in SQL statement

9. SQL statement jumbles records

10. SQL Statement for updating an existing recording in a db

11. SQL Statement returns records in Access but not in ADO

12. SQL Statement for repeated records.


Powered by phpBB® Forum Software