I have the following issue with an adodb parameter query that fails with an error or returns an empty recordset. However a similar DAO query succeeds.
This is all in MS Access
First the SQL - Find_Post_Code - This is designed to take either or both parameters:
PARAMETERS [Local] Text ( 255 ), FState Text ( 255 );
SELECT PCodes.postcode, PCodes.locality, PCodes.state, PCodes.long, PCodes.lat
FROM PCodes
WHERE (((PCodes.locality) Like "*" & [Local] & "*") AND ((PCodes.state)=[FState])) OR (((PCodes.locality) Like "*" & [Local] & "*") AND ((([PCodes].[state]) Like [FState]) Is Null))
ORDER BY PCodes.locality;
Now the ADODB Function:
Public Function GetPostCode(varLocality As Variant, varState As Variant) As String
'//Purpose: Return Post Code for a varLocality and/or varState
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
On Error GoTo Handle_err
With cmd
.CommandText = "Find_Post_Code"
.CommandType = adCmdStoredProc
Set .ActiveConnection = CurrentProject.Connection
.Parameters.Append .CreateParameter("Local", adVarChar, adParamInput, , CStr(Nz(varLocality, "")))
.Parameters.Append .CreateParameter("FState", adVarChar, adParamInput, , CStr(Nz(varState, "")))
rst.CursorType = adOpenStatic
Set rst = .Execute
End With
If Not rst.EOF Then
GetPostCode = rst!postcode
End If
rst.Close
Handle_err:
If Err Then
MsgBox "Error " & Format(Err.Number) & " " & Err.Description, vbCritical, AppTitle
Err.Clear
End If
Set rst = Nothing
Set cmd = Nothing
End Function
This fails with the error message: Error 3708 Parameter object is improperly defined. Inconsistent or incomplete information was provided.
If I cast the parameter as adBSTR there is no error, but the resultant recordset has no data
However, If I use the following DAO function all is well
Public Function GetPostCode2(Locality As String, State As String) As String
'//Purpose: Return Post Code for a varLocality and/or varState
Dim db As Database
Dim qd As DAO.QueryDef
Dim prmLocality As DAO.Parameter
Dim prmFSTate As DAO.Parameter
Dim rst As DAO.Recordset
On Error GoTo Handle_err
Set db = CurrentDb
Set qd = db.QueryDefs("Find_Post_Code")
Set prmLocality = qd.Parameters!Local
prmLocality.Value = Locality
Set prmFSTate = qd.Parameters!FState
prmFSTate.Value = State
Set rst = qd.OpenRecordset
If Not rst.EOF Then
GetPostCode2 = rst!postcode
End If
rst.Close
Handle_err:
If Err Then
MsgBox "Error " & Format(Err.Number) & " " & Err.Description, vbCritical, AppTitle
Err.Clear
End If
Set rst = Nothing
Set prmLocality = Nothing
Set prmFSTate = Nothing
Set qd = Nothing
Set db = Nothing
End Function
I would prefer not to swap and change between ADODB and DAO to accomplish my aims. Your help would be greatly appreciated
Textparameter corresponds toadVarWChar, Access supports Unicode characters. While internally VBA does store strings as BSTR, you really never pass them to a database engine in that format.Likewild card characters. ChangeLike "*" & [Local] & "*"toLike "%" & [Local] & "%"