ADODB Connection Execute ...

[Magster]

Hi,
I've used docmd.openquery to run this stored action query below and it works just fine. But I really want to use the folowing code and I just can't get to work and that is really frustrating! Can anyone tell me why I can't get this to work? Other ADO code works just fine, just can't seem to make this type of code work:

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
' establish the connectin and execute an action query
Set cnn = CurrentProject.Connection
cnn.Execute "qryReport-03ProjectAndAbsenceLogMatches"
cnn.Close

The following error is displayed: Run-time error "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE", 'SELECT', or 'UPDATE'.

Below is the beginning of the SQL code for the stored action query named qryReport-03ProjectAndAbsenceLogMatches:

INSERT INTO [LocalTempTblProjects-Report03] ( intProjectID, txtEmployeeName, StartDate, EndDate, intFiscalYear, IntProjectNo, ProjectName, txtPriority, ReceivedDate, txtOfficeCode, txtPMCNumber, DueDate, Contact, ContactPhone )
SELECT etc...

 

[KernelK]

That is because the ADO Execute Method takes the actual SQL query text as input, not the name of a stored query.

You would use it like this:

cnn.Execute "INSERT INTO [LocalTempTblProjects-Report03] ( intProjectID, txtEmployeeName, StartDate, EndDate, intFiscalYear, IntProjectNo, ProjectName, txtPriority, ReceivedDate, txtOfficeCode, txtPMCNumber, DueDate, Contact, ContactPhone )
SELECT etc..."

You could also just use DoCmd.RunSQL("SQL TEXT HERE") and then you wouldn't even have to create an ADO connection object. But what you probably want to do is just keep your query saved the way it is, but use

DoCMD.OpenQuery("qryReport-03ProjectAndAbsenceLogMatches")

No ADO Connection object is necessary.

 

[Magster]

Thank you - I appreciate the answer - you explained it simply and I like that!