execute a query from VBA

[Darryl]

Greetings,
I am running Access 2000. I have created a query called newcustomers. I
have created
a report called newcustreport. On the On Open event of the report, I want
to have the
newcustomers query execute (it is NOT the data source of the report).

I couldn't find (google'd) the VBA statement to execute the query.

something like: execute newcustomers. Or run newcustomers.

Anyway thanks in advance for any ideas.

-Darryl

 

[Allen Browne]

Try:
dbEngine(0)(0).Execute "newcustomers", dbFailOnError

That's more reliable than RunSQL, and avoids the confirmation message.

 

[Darryl]

Well,
the query is a Make-Query (ie, it creates a temp table). When I use your
solution,
it errors with the "ncust table exists" error message.

Any ideas on how to get around this ?

-Darryl

 

[Allen Browne]

The simplest idea is to set up the temp table correctly, and then use an
Append query to populate it instead of a Make Table.

Empty and populte it like this:
With dbEngine(0)(0)
.Execute "DELETE FROM ncust;", dbFailOnError
.Execute "newcustomers", dbFailOnError
End With

If you prefer, you can drop the table like this:
.Execute "DROP TABLE ncust;"

 

[Darryl]

OK,
I must be on stupid pills today. Here is my code:

Private Sub Report_Open(Cancel As Integer)
DoCmd.SetWarnings False
With DBEngine(0)(0)
.Execute "delete from newcases", dbFailOnError
.Execute "tstquery3", dbFailOnError
End With
DoCmd.SetWarnings True

End Sub

I get an error "run time 3061" too few parameters expected 2.

what have I screwed up ?

 

[Allen Browne]

Does your query contain references to a text box on a form? The Expression
Service is not available to resolve those, so we need to assign those
exactly.

Post the SQL statement for tstquery3 (by switching from query design to SQL
View (on the View menu.))

 

[Darryl]

here is the query :
INSERT INTO newcases ( EntryDate, ClientNumber, MatterNumber,
MatterAcceptedDate, BusinessNamesOnly, LastName, FirstName, MiddleInitial,
ATTYInitials, PracticeClass )
SELECT NewClientCaseMasterTable.EntryDate,
NewClientCaseMasterTable.ClientNumber,
NewClientCaseMasterTable.MatterNumber,
NewClientCaseMasterTable.MatterAcceptedDate,
NewClientCaseMasterTable.BusinessNamesOnly,
NewClientCaseMasterTable.LastName, NewClientCaseMasterTable.FirstName,
NewClientCaseMasterTable.MiddleInitial,
NewClientCaseMasterTable.ATTYInitials,
NewClientCaseMasterTable.PracticeClass, *
FROM NewClientCaseMasterTable
WHERE (((NewClientCaseMasterTable.EntryDate) Between [bdate] And [edate]));

The query prompts for [bdate] and [edate].

 

[Allen Browne]

Okay, your query appears to have 2 parameters: bdate and edate. You need to
supply those programmatically, or else concatenate the values into a string
and execute that.

This example assumes you put this code into a form that has 2 unbound text
boxes named bdate and edate. The code builds the dates from the form into
the string, so the code does not need the saved query at all.

Dim strSql As String
Const conJetDate = "\#mm\/dd\/yyyy\#"

strSql = "INSERT INTO newcases ( EntryDate, ClientNumber, MatterNumber,
MatterAcceptedDate, BusinessNamesOnly, LastName, FirstName, MiddleInitial,
ATTYInitials, PracticeClass ) " & _
"SELECT NewClientCaseMasterTable.EntryDate,
NewClientCaseMasterTable.ClientNumber,
NewClientCaseMasterTable.MatterNumber,
NewClientCaseMasterTable.MatterAcceptedDate,
NewClientCaseMasterTable.BusinessNamesOnly,
NewClientCaseMasterTable.LastName, NewClientCaseMasterTable.FirstName,
NewClientCaseMasterTable.MiddleInitial,
NewClientCaseMasterTable.ATTYInitials,
NewClientCaseMasterTable.PracticeClass, * " & _
"FROM NewClientCaseMasterTable WHERE NewClientCaseMasterTable.EntryDate
Between " & _
Format(Me.bdate, conJetDate) & " And " & Format(Me.edate, conJetDate) & ";"

dbEngine(0)(0).Execute strSql, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

here is the query :
INSERT INTO newcases ( EntryDate, ClientNumber, MatterNumber,
MatterAcceptedDate, BusinessNamesOnly, LastName, FirstName, MiddleInitial,
ATTYInitials, PracticeClass )
SELECT NewClientCaseMasterTable.EntryDate,
NewClientCaseMasterTable.ClientNumber,
NewClientCaseMasterTable.MatterNumber,
NewClientCaseMasterTable.MatterAcceptedDate,
NewClientCaseMasterTable.BusinessNamesOnly,
NewClientCaseMasterTable.LastName, NewClientCaseMasterTable.FirstName,
NewClientCaseMasterTable.MiddleInitial,
NewClientCaseMasterTable.ATTYInitials,
NewClientCaseMasterTable.PracticeClass, *
FROM NewClientCaseMasterTable
WHERE (((NewClientCaseMasterTable.EntryDate) Between [bdate] And
[edate]));

The query prompts for [bdate] and [edate].

Does your query contain references to a text box on a form? The
Expression
Service is not available to resolve those, so we need to assign those
exactly.

Post the SQL statement for tstquery3 (by switching from query design to SQL
View (on the View menu.))