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
Cells(x, 4) = Recordset.Fields(1).Value
Cells(x, 5) = Recordset.Fields(2).Value
Cells(x, 12) = Recordset.Fields(10).Value