Home Excel VBA running Access query may return multiple records
Reply: 1

Excel VBA running Access query may return multiple records

Shaves
1#
Shaves Published in 2017-12-07 19:55:49Z

I'm running the following query in a macro (with Excel VBA) to an Access database. It works good when only 1 record is returned. I've been made aware that multiple records could be returned.

I'm stuck on trying to identify when there are multiple records so I can add each additional record to the worksheet. Once I know there are multiple records, I'll write the 1st record and then increment the row number (x) by 1 to write the 2nd, 3rd, etc.

I haven't had this situation before so I'm not sure how (1) to identify if there are multiple records and (2) how to write the 2nd record. Is the first field in the 2nd record going to be Recordset.Fields(11)?

Thanks for the help..........

Dim Recordset As ADODB.Recordset
Set Recordset = New ADODB.Recordset

vDataBase = "Y:\Groups\Corporate Accounting\AutoMonarch\ Translate\Titles.mdb"
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= '" & vDataBase & "';Persist Security Info=False"

vSql = ""
vSql = "Select bo_id, buyers_order_date, mileage, contact_name, contact_phone, cust_legal_name, address, city, state, zip, country, location_phone from buyerinfo where unit = '" & vArrUnit(q) & "'"

Call Recordset.Open(vSql, ConnectionString, adOpenForwardOnly, adLockReadOnly, CommandTypeEnum.adCmdText)

If Recordset.BOF And Recordset.EOF Then
'      do something to indicate no records returned
Else

   Cells(x, 4) = Recordset.Fields(1).Value
   Cells(x, 5) = Recordset.Fields(2).Value
   ....
   Cells(x, 12) = Recordset.Fields(10).Value

End If
ryguy72
2#
ryguy72 Reply to 2017-12-09 02:00:27Z

Sorry, but this sound like a weird setup. Can you run a 'Find Duplicates Query Wizard'? Maybe you can do a Countif in Excel, if that's your ask.

https://www.extendoffice.com/documents/excel/1499-count-duplicate-values-in-column.html

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.329119 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO