Stored Query in Access runs from Excel but generates no results

[bluecjh]

I have a fairly complex union query stored in access (INSERT INTO table etc...)

This query runs fine inside Access inserting plenty of rows into my table. However, I want to be able to run the stored query from Excel. Using ADODB and a command object and after much trial and error the code runs :


Sub CommandAndExec()
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset

Set conn = New ADODB.Connection
With conn
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Users\bluecjh\documents\AccessTest.accdb"
.Open
End With

Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = conn
.CommandText = "qrySkillReport"
.CommandType = adCmdStoredProc
.Execute
End With


conn.Close
Set conn = Nothing
End Sub

But generates no rows in my table. Is there a command parameter that needs to be set, or is it in the connection. Thanks for any help.

BlueCJH

 

[SkipVought]

hi,

Is there a command parameter that needs to be set

Do you supply a parameter value when you run in Access and if so, for what citeria?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bluecjh]

Hi Skip,

No there is no parameter.

Chris.

BlueCJH

 

[bluecjh]

SORTED. There was an offending piece of SQL: use of the LIKE expression. I'm not sure what it was offending, because the query ran fine in access, but there your are...

BlueCJH

 

[SkipVought]

Would you please report the BEFORE and AFTER view of your SQL.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

In access you use * and ? as wildcards, but in ADO you have to use % and _ (ie the standard)

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

 

[bluecjh]

Skip

I cannot do the detail, because of security in my workplace, but the essentially I replaced

WHERE
e.[field a] LIKE 'Jspm*'

with:


WHERE
LEFT(E.[field a],4) = 'JSPM'


thanks.

BlueCJH

 

[SkipVought]

or you could have done as PHV suggested.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]