How to run an action query in the VB code??

[sonikudi]

Hey guys,

So i have an action query 'A' that makes a table, this query 'A' selects columns from another query 'B', and the query 'B' asks user for dates that is [from shipment date] and [to shipment date].
I tried using the current db.execute cmd but it gives me an error saying saying "Run time error '3061'. too few parameters. Expected 2". I know this error has to do with the user entering shipment dates but i am not sure how to fix it..my code is as follows:

Private Sub Graph_percent_rejected_Click()
Dim StrTableName As String
StrTableName = "qrySummaryStaubTable"
CurrentDb.TableDefs.Delete StrTableName
CurrentDb.Execute ("qrySummaryStaub")

End Sub

Any help will be greatly appreciated.

Thanks!

 

[sonikudi]

I kind of forgot to specify my goal and went directly to the problem...my goal here is to execute the query when the user clicks a bttn.

 

[dhookom]

IMHO, parameter prompts in queries is never acceptable user interface. I would use a couple text boxes on the form with names like txtStartDate and txtEndDate. This is a much more professional way to get user input.

Then use code like:

Dim strSQL as String
strSQL = "SELECT INTO ... " & _
  "FROM B WHERE [DateField] Between #" & _
  Me.txtStartDate & "# AND #" & Me.txtEndDate & "#"
CurrentDb.Execute strSQL, dbFailOnError

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JerryKlmns]

sonikudi

I wouldn't delete the table itself but rather the records it holds and use an append query in place of the make-table one, for the following reasons

A] You lose PK
B] You lose Indexes
C] You lose Referential Integry set up
D] Bloats the database
E] User running the code should have permissions on object' s structures

Also I double Duane's position.

Be aware that if your dates iclude time it is more suitable to

"WHERE [DateField] >= #" & Me.txtStartDate & "# AND " & _
[DateField] < #" & Me.txtEndDate + 1 & "#;"

 

[sonikudi]

JerryKlmns,

thanks for the suggestion, but if i don't delete the table then it gives me an error saying the 'table already exists'..so i have to delete the table and not just the records. the nature of this project requires that i put the output of the query into a table..so there is no way around that, but is there anything else i can do?

 

[PHV]

Delete the rows and then use an append query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sonikudi]

okay, back to the query that Duane suggested, well i made a form asked for the user input in text boxes and everything but it still gave me an error saying "too few arguments, expected 3"..

I figured its because of the query 'B' asking for field dates and not the query A, so i thought of writing te code for query A and running that first and then exeuting the query B

the code is as follows:

CurrentDb.TableDefs.Delete ("qrySummaryStaubTable")

SrtSQL2 = "SELECT [Item Num], [Shipment Date], SUM([Num Defected]) AS SumNumDefected, SUM([Batch Size]) AS SumBatchSize"& _
"FROM [incoming inspec staub parts]" & _
"WHERE Vendor= "Staub" And t1.[Shipment Date] Between #" & _
Me.txtStartDate & "# AND #" & Me.txtEndDate & "#" GROUP BY [Item Num],[Shipment Date];


DoCmd.RunSQL StrSQL2

StrSQL = "SELECT t1.[Item Num], t2.Description, t1.[Shipment Date],(t1.SumNumDefected/t1.SumBatchSize)*100 AS PercentRejected INTO qrySummaryStaubTable " & _
  "FROM qryGetSummaryStaub AS t1, [Item numbers] AS t2 WHERE (t1.[Item Num])=(t2.[Item Num]) AND t1.Date Between #" & _
  Me.txtStartDate & "# AND #" & Me.txtEndDate & "#"

CurrentDb.Execute StrSQL, dbFailOnError

But for the code in StrSQL2 it gives me an "expected end of statement after the first where clause...

I can't figure out if the syntax is wrong or what..

Any ideas?

 

[dhookom]

You can only RunSQL on action queries. Also, your syntax is in need of lots of work like adding spaces and delimiting text with alternative quoting methods. This fix some of the errors but can't be "run". You also use both SrtSQL2 and StrSQL2.

SrtSQL2 = "SELECT [Item Num], [Shipment Date], SUM([Num Defected]) AS SumNumDefected, SUM([Batch Size]) AS SumBatchSize "& _
"FROM [incoming inspec staub parts] " & _
"WHERE Vendor= 'Staub' And [Shipment Date] Between #" & _
Me.txtStartDate & "# AND #" & Me.txtEndDate & "# GROUP BY [Item Num],[Shipment Date];"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sonikudi]

hey, thanks a lot! where can i find more information on using the proper syntax for sql in VB??

 

[dhookom]

There are lots of resources on SQL syntax. You just need to learn how to build strings of text. You can add code for debugging like:
Debug.Print StrSQL2
This will print the statement into the immediate window.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JerryKlmns]

sonikudi

If you delete the table then you run a make-table query
If you delete the records then you run an append query.

Is that more clear now?

 

[sonikudi]

JerryKlmns and Duane ..thanks! a lot