ADO run make table query with parameter 2

[itchyII]

Hello All,
I have been asked to automate (in order to run as scheduled task) a multistep process in an Access application. The application was built entirely using the Access gui with about 50 queries and 50 tables. There are a series of queries that must be run and those queries call other queries and so forth. The problem I am having is that I have been asked to do this as quickly as possible and I do not have time to convert all the access queries to code. So, I have an access make table query, that calls another query that requires a parameter. I need to run the queries automatically and pass the parameter as a variable that is passed to my function. And I need to do all of this using ADO (They are in an Access 2000 environment and will be migrating to 2003 shortly). I am not very fast and familiar with ADO. I tried simply taking the sql from the query (the second one) and tried to create a recordset, just to see if the parameter prompt would show up, but it doesn't and I get an error saying "Syntax error in INSERT INTO statement".

Function step1(SO As String) As ADODB.Recordset

Dim rs As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim sql As String

Set cnn = CurrentProject.Connection
Set rs = CreateObject("ADODB.recordset")

sql = "INSERT INTO POs per SO With AC SELECT [POs per SO average cost with Add Charges Step 1a].PartKey, [POs per SO average cost with Add Charges Step 1a].Partnumber, Avg([POs per SO average cost with Add Charges Step 1a].[Total Cost Incl AC]) AS [SumOfTotal Cost Incl AC] INTO [POs per SO With AC] " _
        & "FROM [POs per SO average cost with Add Charges Step 1a] GROUP BY [POs per SO average cost with Add Charges Step 1a].PartKey, [POs per SO average cost with Add Charges Step 1a].Partnumber;"


rs.Open sql, cnn

end function
[\code]


I have tried simply calling the existing function substituting:

rs.Open sql, cnn

with:

rs.Open "POs per SO average cost with Add Charges Step 1b", cnn

and I get anothere error "Invalid SQL statement, expected 'DELETE','INSERT','PROCEDURE",'SELECT',or 'UPDATE'"

And now this is where I'm at!

Help please!

ItchyII

 

[bboffin]

You can't use the Open method to run an INSERT query with parameters, you have to use the Execute method of an ADO Command object instead

A very brief summary of this is:

Dim oCmd as New ADODB.Command
With oCmd
    Set .ActiveConnection = MyConnection
    .CommandType = adCmdText
    .CommandText = sql
    .Execute ,Param1, param2, ...
End With

Bob Boffin

 

[itchyII]

Thanks for the reply Bob. Here is where I'm at. I added the code you gave me, but I'm still getting the same error ("Syntax error in INSERT INTO statement").

Function step1(SO As String) 'As ADODB.Recordset

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim sql As String

Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command

sql = "INSERT INTO POs per SO With AC SELECT [POs per SO average cost with Add Charges Step 1a].PartKey, [POs per SO average cost with Add Charges Step 1a].Partnumber, Avg([POs per SO average cost with Add Charges Step 1a].[Total Cost Incl AC]) AS [SumOfTotal Cost Incl AC] INTO [POs per SO With AC] " _
        & "FROM [POs per SO average cost with Add Charges Step 1a] GROUP BY [POs per SO average cost with Add Charges Step 1a].PartKey, [POs per SO average cost with Add Charges Step 1a].Partnumber;"

With cmd
    .ActiveConnection = cnn
    .CommandType = adCmdText
    .CommandText = sql
    .Execute , SO
End With

End Function

Any more ideas?

ItchyII

 

[PHV]

You may try this for an append query:
sql = "INSERT INTO [POs per SO With AC] SELECT PartKey, Partnumber, Avg([Total Cost Incl AC]) AS [SumOfTotal Cost Incl AC] " _
& "FROM [POs per SO average cost with Add Charges Step 1a] GROUP BY PartKey, Partnumber;"
Or this for a maketable query:
sql = "SELECT PartKey, Partnumber, Avg([Total Cost Incl AC]) AS [SumOfTotal Cost Incl AC] INSERT INTO [POs per SO With AC] " _
& "FROM [POs per SO average cost with Add Charges Step 1a] GROUP BY PartKey, Partnumber;"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[itchyII]

Thanks PHV!

I had to remove the word 'INSERT' fromyour statement for the make table query, because it was bugging on me, but it now works!

Excellent!

Itchy

 

[PHV]

Oops, sorry for the typo
Glad you fixed it yourself.