I have an ADO Query from Excel that accesses an Access parameter query. The query is designed so that if the parameter is left blank I still gett all records. This works fine in Access.
When executing from Excel, the parmeter works fine as long as I enter a value. If left blank (cell K2 in Excel), it still gives me the last parmater data, not all the records. The ADO code is below.
thx for the help!
Howard
Sub GetActuals()
Dim strRegion As String
Dim strCOT As String
'Import Actuals
Set CN = New ADODB.Connection
Set cmd = New ADODB.Command
CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";"
strRegion = wksActuals.Range("k1").Value
strCOT = wksActuals.Range("k2").Value
With cmd
.CommandType = adCmdStoredProc
.CommandText = "qry_Actuals"
.ActiveConnection = CN
End With
' Build Parameter
With prm
.Name = "COT"
.Value = strCOT
.Type = adVarChar
.Size = 50
.Direction = adParamInput
End With
' If All then run all COTS
'Append Parameter
cmd.Parameters.Append prm
Set adors = cmd.Execute
'Reset dataset for new data and copy recordset
'wksData_Daytrend.Range("dataset").ClearContents
wksActuals.Range("a2").CopyFromRecordset adors
adors.Close
CN.Close
Set prm = Nothing
Set adors = Nothing
Set CN = Nothing