0

I'm trying to write a simple VBA function that, when given an email address, will look it up in an Access database and return the ID of the contact associated with it.

Here is the VBA code:

(There is a string variable named EmailAddress previously defined.)

    Dim Cnn As ADODB.Connection
    Dim STR As String
    
    Set Cnn = New ADODB.Connection
    Cnn.Provider = "Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyDB.accdb;Persist Security Info=False"
    Cnn.Open
    
    STR = "SELECT CompanyContact.ContactID FROM CompanyContact WHERE CompanyContact.Email LIKE '*mailto:' & ? & '*'"
    
    Dim cmd As New ADODB.Command
    Dim Rec As New ADODB.Recordset
    
    cmd.Name = "GetContactID"
    cmd.CommandText = STR
    cmd.CommandType = adCmdText
    cmd.Parameters.Append cmd.CreateParameter("Email", adVarChar, adParamInput, Len(EmailAddress), EmailAddress)
    Set cmd.ActiveConnection = Cnn
    Set Rec = cmd.Execute

Note: the column CompanyContact.Email is of type Hyperlink, hence the LIKE comparison and the string concatenation with mailto:.

When I run this code, Rec.EOF is immediatly true (so, no records returned), despite the fact that a matching record does exist in the DB.

To confirm this, I copy paste the exact same query string into Access and run it. It prompts me for the value of ?, I paste in the email address, and it returns the expected ID.

Query in Access Query result in Access

I haven't had any luck debugging this. I've changed the query string to SELECT 'Test' & ? and have confirmed that the email address is being correctly passed to Access (it returns a single record with the expected result). I just don't know why the WHERE statement isn't working via ADODB, when the same statement seems to work fine directly in Access.

Keith Stein
  • 6,235
  • 4
  • 17
  • 36
  • 1
    With ADO, you need SQL-92 wildcard `%` instead of `*` --- see [Access wildcard character reference](https://support.microsoft.com/en-us/office/access-wildcard-character-reference-af00c501-7972-40ee-8889-e18abaad12d1) for more details. – HansUp Feb 22 '23 at 00:10
  • @HansUp That was it! I don't know why `*` worked directly in Access, but changing to `%` got it working in ADODB. Please post as an answer so I can accept. – Keith Stein Feb 22 '23 at 01:43
  • Access defaults to DAO. Instead of opening recordset to just retrieve a single value, could use DLookup. – June7 Feb 22 '23 at 07:03
  • Does this answer your question? [ADODB wild card operators other than %](https://stackoverflow.com/questions/39149378/adodb-wild-card-operators-other-than) – June7 Feb 22 '23 at 13:53
  • @June7 This VBA isn't being run from inside Access, it's being run from another Office application. So I don't think DLookup would be available. – Keith Stein Feb 22 '23 at 16:55
  • Could with an Access application object instead of a connection. Possibly connection is less resource intensive and it satisfies. – June7 Feb 22 '23 at 18:44

0 Answers0