1

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

9
  • Not sure what you mean by 'swap and change' nor why this is an issue. I have used both in the same db, even the same procedure. Commented Oct 31, 2018 at 5:38
  • Hi June7, I would prefer to use ADODB over DAO. I simply want to get the ADODB function working. Commented Oct 31, 2018 at 6:13
  • Why would you prefer adodb? Also, a Text parameter corresponds to adVarWChar, Access supports Unicode characters. While internally VBA does store strings as BSTR, you really never pass them to a database engine in that format. Commented Oct 31, 2018 at 7:00
  • Hi Erik. adVarWChar returns same error as stated above. The reason that I prefer ADO is that it is supported on other platforms whereas DAO is not - might I add that the issue of ADO over DAO is insignificant here. My question is how do I get the ADO code working? Commented Oct 31, 2018 at 7:07
  • 1
    ADO expects different Like wild card characters. Change Like "*" & [Local] & "*" to Like "%" & [Local] & "%" Commented Oct 31, 2018 at 17:45

1 Answer 1

1

Thanks for your input. Here is the solution: 1. Modify the SQL statement as HansUp suggested.

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;

Interestingly, this query does not return any values if used within the Access UI

  1. Cast the ADO parameters as adBSTR - adVarChar and adVarWChar both returned Error 3708 Parameter object is improperly defined. Inconsistent or incomplete information was provided.
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.