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

Excel VBA running Access query may return multiple records

user1602
1#
user1602 Published in April 25, 2018, 12:17 pm

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
You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO