append data

[slimjen1]

All; using 2010; I have a database in which I import 4 excel spreadsheets into temp tables. I want to append the data in temp tables to existing tables.
I want user to be able to do the importing and appending from a form with a command button. I put code similar to this in a function along with code to import the excel spreadsheet (Transferspreadsheet...):

Dim srtQry1 As String, srtQry2 As String

strQry1 = "INSERT INTO Order_Line (OrderNo, ProductID, Qty, Price) " & _
              "SELECT OrderNo, ProductID, Qty, Price " & _
              "FROM Import_Order_Lines;"

strQry2 = "INSERT INTO Order (OrderNo, OrderDate, CurstomerName, CustomerDeliveryAddress, CustomerSuburb, CustomerState, CustomerPostCode) " & _
              "SELECT OrderNo, OrderDate, CurstomerName, CustomerDeliveryAddress, CustomerSuburb, CustomerState, CustomerPostCode " & _
              "FROM Import_Order_Lines;"

DoCmd.RunSQL strQry1
DoCmd.RunSQL strQry2

Like i said; I have 4 tables. Is there a more proficient use of VBA that I can use or am I on the right track. How would you do this?
Thanks

 

[Beetle]

That's likely how I would do it, based on your description of the circumstances, although, if you have referential integrity enforced on your tables I would think that you would need to append the records to the Orders table first, then the Order_Line table. I would also use

CurrentDb.Execute strQry1, dbFailOnError

so as to avoid the warning messages.

 

[slimjen1]

thanks for replying Sean. I do need to do this in order. But can you explain:
CurrentDB.Execute....?
Thanks

 

[slimjen1]

So the code should be:

Function Append()
On Error GoTo Err_Append
Dim sQry1 As String, sQry2 As String Dim dbs As DAO.Database

sQry1 = "INSERT INTO Order_Line (OrderNo, ProductID, Qty, Price) " & _
              "SELECT OrderNo, ProductID, Qty, Price " & _
              "FROM Import_Order_Lines;"

sQry2 = "INSERT INTO Order (OrderNo, OrderDate, CurstomerName, CustomerDeliveryAddress, CustomerSuburb, CustomerState, CustomerPostCode) " & _
              "SELECT OrderNo, OrderDate, CurstomerName, CustomerDeliveryAddress, CustomerSuburb, CustomerState, CustomerPostCode " & _
              "FROM Import_Order_Lines;"

dbs.Execute sQry1, dbFailOnError
dbs.Execute sQry2, dbFailOnError

Exit_Append:
    Exit Function

Err_Append:
    MsgBox Err.Description
    Resume Exit_Append
    
End Function

Is this correct?
Thanks

 

[Beetle]

DoCmd.RunSQL is a method of the Access UI, so with this method the query is evaluated by the Access expression service before being passed to the Ace/Jet database engine. As a result, you will get the default Access warnings when running an action query about "you are about to update records in a table.....". You can code around this by turning the warnings off, but you then need to take steps to make sure that the warnings get turned back on even if there is an error;

Private Sub SomeProcedure()
On Error GoTo ErrHandler

    Dim strSQL As String

    strSQL = "Update MyTable Set This = That"

    Docmd.SetWarnings False
    DoCmd.RunSQL strSQL

ExitHere:
    DoCmd.SetWarnings True
    Exit Sub

ErrHandler:
    MsgBox "There has been an error:" & vbNewLine _
             & Err.Number & " " & Err.Description
    Resume ExitHere

End Sub

On the other hand, CurrentDB.Execute is a method of the Ace/Jet database engine, so the query is passed directly to Ace/Jet, bypassing the Access expression service and the default warning messages, eliminating the need to code around it;

Private Sub SomeProcedure()
On Error GoTo ErrHandler

    Dim strSQL As String

    strSQL = "Update MyTable Set This = That"

    CurrentDb.Execute strSQL, dbFailOnError

ExitHere:
    Exit Sub

ErrHandler:
    MsgBox "There has been an error:" & vbNewLine _
             & Err.Number & " " & Err.Description
    Resume ExitHere

End Sub

You do need to make sure and include the dbFailOnError argument so that if there is an error when processing the query the error information will be passed to your error handler. Otherwise, any errors will simply be ignored and you won't know that something went wrong.

Another difference to be aware of between the two is how you handle parameters or form control references. Since RunSQL is an Access method, you can include form references directly in the SQL and the expression service will evaluate the reference before passing the SQL to Ace/Jet. So the following works with RunSQL;

Dim strSQL As String

strSQL = "Update MyTable Set ThisField= Forms!MyForm!MyControl"

DoCmd.RunSQL strSQL

This will not work with CurrentDB.Execute because we are passing the statement directly to Ace/Jet, which is not aware of the existence of Forms!MyForm!MyControl or what it is. So you need to place the form reference outside of the SQL string, so that the actual value is returned from the form control and passed into the string before it is executed;

Dim strSQL As String

strSQL = "Update MyTable Set ThisField=" & Forms!MyForm!MyControl

CurrentDb.Execute strSQL, dbFailOnError

Or if you're running a saved query that has parameters then you use QueryDefs and evaluate the parameters before executing the query.

Hope this helps.

 

[Beetle]

Regarding your post #4, there's no need to declare a variable for the database object, just use CurrentDb;

With CurrentDb
    .Execute sQry1, dbFailOnError
    .Execute sQry2, dbFailOnError
End With

 

[slimjen1]

Ok. Thanks so much for the education on this. So to clarify; I can use my code and run it like so:

Function Append()
On Error GoTo Err_Append
Dim sQry1 As String, sQry2 As String 

sQry1 = "INSERT INTO Order_Line (OrderNo, ProductID, Qty, Price) " & _
              "SELECT OrderNo, ProductID, Qty, Price " & _
              "FROM Import_Order_Lines;"

sQry2 = "INSERT INTO Order (OrderNo, OrderDate, CurstomerName, CustomerDeliveryAddress, CustomerSuburb, CustomerState, CustomerPostCode) " & _
              "SELECT OrderNo, OrderDate, CurstomerName, CustomerDeliveryAddress, CustomerSuburb, CustomerState, CustomerPostCode " & _
              "FROM Import_Order_Lines;"
sQry3 = .......
sQry4 = ......

With CurrentDb
    .Execute sQry1, dbFailOnError
    .Execute sQry2, dbFailOnError
    .Execute sQry3, dbFailOnError
    .Execute sQry4, dbFailOnError
End With

Exit_Append:
    Exit Function

Err_Append:
    MsgBox Err.Description
    Resume Exit_Append
    
End Function

Thanks

 

[Privateer]

Its not a big deal, but I have an entire application with one strSQL variable. You could also do
strSQL: = "Select ..."
CurrentDb.Execute strSQL, dbFailOnError
strSQL: = "Select ..."
CurrentDb.Execute strSQL, dbFailOnError
strSQL: = "Select ..."
CurrentDb.Execute strSQL, dbFailOnError
strSQL: = "Select ..."
CurrentDb.Execute strSQL, dbFailOnError

Is sometimes makes it easier to find the error by pausing on each execute line.