Insert Query/Select Query works in access, but not in vba

Question

I have created a query that works great with no errors in Access, and while trying to translate this to my vba setup I can't figure out why I am not getting any values, even though the query clearly works in access, and causes no errors on the VBA side.

Pulling my hair out here, I have created a side table to see if I could "pseudo-insert" the calculated value, but I get the same issue as above - insert works with no errors in access, goes through in vba with no issues, but doesn't actually insert any data.

I have copied the string queries while pausing code to make sure EVERYTHING matches up between the Access query that works and the VBA query, and haven't found any differences.

I read somewhere since I am trying to pull a "first line" data piece that there may be some HDR setting that I could change, but when I tried to apply any fixes I found they opened another instance of excel and opened a dialogue box.

Please let me know what I am doing wrong here.

Public Function PullNextLineItemNumB(QuoteNum) As Integer
Dim strQuery As String
Dim ConnDB As New ADODB.Connection
Dim myRecordset As ADODB.Recordset
Dim QuoteModifiedNum As String

ConnDB.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0; data 
source=" & ThisWorkbook.Path & "\OEE Info.accdb"

'Query to try and make Access dump the value "18" into the table so I can 
grab it after the query is finished, sidestepping excel not working
strQuery = "INSERT INTO TempTableColm (TempColm) SELECT 
MAX(MID([Quote_Number_Line],InStr(1,[Quote_Number_Line]," & Chr(34) & "-" 
& Chr(34) & ")+1)) AS MaxNum from UnifiedQuoteLog where Quote_Number_Line 
like '" & QuoteNum & "*'"
ConnDB.Execute strQuery

'Original query, returns "18" as expected in Access, and null or empty in 
the recordset
strQuery = "SELECT MAX(MID([Quote_Number_Line],InStr(1, 
[Quote_Number_Line]," & Chr(34) & "-" & Chr(34) & ")+1)) from 
UnifiedQuoteLog where Quote_Number_Line like '" & QuoteNum & "*'"

Set myRecordset = ConnDB.Execute(strQuery)
Do Until myRecordset.EOF
For Each fld In myRecordset.Fields
    Debug.Print fld.Name & "=" & fld.Value
Next fld
myRecordset.MoveNext
Loop

myRecordset.Close
Set myRecordset = Nothing
ConnDB.Close
Set ConnDB = Nothing

End Function

Actual output from access is "18" which is expected, output from excel's vba recordset is always null or empty string.

Mike Candiello · Accepted Answer · 2019-10-30 15:16:10Z

1

It appears I solved the problem, while looking into this apparently the excel operator using ADODB with access is % for LIKE and NOT * (because reasons). As soon as I made that change everything started working.

Can someone explain why that is a thing? I really want to understand why this was a design choice.

answered Oct 30, 2019 at 15:16

Mike Candiello

255 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Hel O'Ween Over a year ago

Access is the weird one here, as % is the standard wildcard in SQL. So Excel, via ADO, follows the standard.

Mike Candiello Over a year ago

Yea I started with using % on other queries in vba when talking with access (I'm used to SQL server syntax), but when I transferred it over to access for testing, it yelled at me. New job, new quirks about databases and programs to learn I guess.

Hel O'Ween Over a year ago

This article might help with that. tl;dr: in Access, you can use both Access or SQL wildcards, but you must use one set exclusively in your queries. You can't mix 'em up.

Collectives™ on Stack Overflow

Insert Query/Select Query works in access, but not in vba

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related