append queries

[Haggr1 via AccessMonster.com]

I have 3 append queries I would like to run at the same time from one
"command button" without warning messages. But before they run, I need to
delete all records in the

I am appending. Below are the 3 queries.
Thanks

INSERT INTO ImportDateDue ( Job, Due )
SELECT [Date Due].[Job Number], [Date Due].[Date Due]
FROM [Date Due]
WHERE ((([Date Due].[Date Due])>Now()-1));

INSERT INTO ImportDateDue ( Job, Due )
SELECT Import.Job, Import.Due
FROM Import
WHERE (((Import.Due)>Now()-"1"));

INSERT INTO ImportDateDue ( Job, Item, Ordered, Age )
SELECT Import.Job, Import.Item, Import.Ordered, Import.Age
FROM Import
WHERE (((Import.Ordered)<Date()-3));

 

[Clifford Bass]

Hi,

You can do something like this:

DoCmd.SetWarnings False
DoCmd.RunSQL "delete from tablename'"
DoCmd.RunSQL "insert ..."
DoCmd.RunSQL "insert ..."
DoCmd.RunSQL "insert ..."
DoCmd.SetWarnings True

Clifford Bass

 

[Haggr1 via AccessMonster.com]

Is this correct

Private Sub Command162_Click()
DoCmd.Close

On Error GoTo Err_Command162_Click
DoCmd.SetWarnings False
DoCmd.RunSQL "delete from ImportDateDue'"
DoCmd.RunSQL "INSERT INTO ImportDateDue ( Job, Due )"
SELECT [Date Due].[Job Number], [Date Due].[Date Due]
FROM [Date Due]
WHERE ((([Date Due].[Date Due])>Now()-1));"
DoCmd.RunSQL "INSERT INTO ImportDateDue ( Job, Due )"
SELECT Import.Job, Import.Due
FROM Import
WHERE (((Import.Due)>Now()-"1"));"
DoCmd.RunSQL "INSERT INTO ImportDateDue ( Job, Item, Ordered, Age )"
SELECT Import.Job, Import.Item, Import.Ordered, Import.Age
FROM Import
WHERE (((Import.Ordered)<Date()-3));"
DoCmd.SetWarnings True


Err_Command162_Click:
MsgBox Err.Description
Resume Exit_Command162_Click

End Sub

Hi,

You can do something like this:

DoCmd.SetWarnings False
DoCmd.RunSQL "delete from tablename'"
DoCmd.RunSQL "insert ..."
DoCmd.RunSQL "insert ..."
DoCmd.RunSQL "insert ..."
DoCmd.SetWarnings True

Clifford Bass

I have 3 append queries I would like to run at the same time from one
"command button" without warning messages. But before they run, I need to

[quoted text clipped - 15 lines]

FROM Import
WHERE (((Import.Ordered)<Date()-3));

 

[Haggr1 via AccessMonster.com]

This works
Private Sub Command62_Click()
On Error GoTo Err_Command62_Click

With CurrentDb
DoCmd.Hourglass True
.Execute "DELETE FROM importdatedue;", dbFailOnError
.Execute "QryDateDue", dbFailOnError
.Execute "QryDateDueImport", dbFailOnError
.Execute "QryDateDueImportOld", dbFailOnError
End With

Exit_Command62_Click:
DoCmd.Hourglass False


Exit Sub

Err_Command62_Click:
MsgBox Err.Description
Resume Exit_Command62_Click
End Sub

Is this correct

Private Sub Command162_Click()
DoCmd.Close

On Error GoTo Err_Command162_Click
DoCmd.SetWarnings False
DoCmd.RunSQL "delete from ImportDateDue'"
DoCmd.RunSQL "INSERT INTO ImportDateDue ( Job, Due )"
SELECT [Date Due].[Job Number], [Date Due].[Date Due]
FROM [Date Due]
WHERE ((([Date Due].[Date Due])>Now()-1));"
DoCmd.RunSQL "INSERT INTO ImportDateDue ( Job, Due )"
SELECT Import.Job, Import.Due
FROM Import
WHERE (((Import.Due)>Now()-"1"));"
DoCmd.RunSQL "INSERT INTO ImportDateDue ( Job, Item, Ordered, Age )"
SELECT Import.Job, Import.Item, Import.Ordered, Import.Age
FROM Import
WHERE (((Import.Ordered)<Date()-3));"
DoCmd.SetWarnings True


Err_Command162_Click:
MsgBox Err.Description
Resume Exit_Command162_Click

End Sub

Hi,

[quoted text clipped - 14 lines]

 

[Clifford Bass]

Hi,

It does not look quite right. So, even though you found another method
that works, here is how you would adjust the stuff below (example of one
statement):

DoCmd.RunSQL "INSERT INTO ImportDateDue ( Job, Due ) " & _
"SELECT [Date Due].[Job Number], [Date Due].[Date Due] " & _
"FROM [Date Due] " & _
"WHERE ((([Date Due].[Date Due])>Now()-1));"

Also the "DoCmd.SetWarnings True" line would be better after the missing
"Exit_Command162_Click:" line.

Anyway, glad you were able to use another method!

Clifford Bass

 

[Haggr1 via AccessMonster.com]

Would you recommend your code... is so how would i write code to delete
records from Table

Hi,

It does not look quite right. So, even though you found another method
that works, here is how you would adjust the stuff below (example of one
statement):

DoCmd.RunSQL "INSERT INTO ImportDateDue ( Job, Due ) " & _
"SELECT [Date Due].[Job Number], [Date Due].[Date Due] " & _
"FROM [Date Due] " & _
"WHERE ((([Date Due].[Date Due])>Now()-1));"

Also the "DoCmd.SetWarnings True" line would be better after the missing
"Exit_Command162_Click:" line.

Anyway, glad you were able to use another method!

Clifford Bass

Is this correct

[quoted text clipped - 24 lines]

End Sub

 

[Clifford Bass]

Hi,

The line you had, without the apostrophe after "Due" should work:

DoCmd.RunSQL "delete from ImportDateDue"

I don't think I have any specific recommendation. There are a number
of ways to get to your goal. All that work. Some have features that others
do not. You can even do a DoCmd.OpenQuery ... to run an action query. If
you want to get back the number of rows deleted or added you can use the
CurrentProject.Connection object instead of the CurrentDB object. It's
Execute method is a little different. Also you can use transaction
processing with that object. So you can make it either do everything
successfully or completely fail if one part fails. RunSQL does allow for the
use of transactions, but I am not up on that aspect to know quite whether it
is the same kind of transaction over multiple statements. My preference is
to "hide" those queries from the user by not putting them in a query, just
directly putting the SQL in the code. However, I usually build and test them
using the query designer.

Hope it helps!

Clifford Bass