Extract To Excel from Access

[AndrewKent]

Hi there,
This links in with a thread I started a few weeks back. I have been able to write a VBA routine that will Create/Find/Edit records in an Access table from Excel. This works a charm however I need to expand this further.
To set the scene, there is a customer table (tblCustomerData). Each record could have up to 4 applicants (the customer and three addional applicants per record). As such, I need to capture each applicants address details. I have therefore created an Address table (tblAddressData) which captures ALL customers address details (there is a field called Address_Type within which will contain either 1,2,3,4 for each applicant). I have set a one to many relationship between tblCustomerData and tblAddressData (so, there could be up to 4 records in tblAddressData that all have a foriegn key that links to 1 record in tblCustomerData).
I'm currently writing a FIND rroutine to return a record from Access to Excel. The problem I'm having is that with the VBA code structure I have, I am unsure how to modify it so that it returns each possible address from the tblAddressData, that corresponds to the record I am searching for in tblCustomerData.
The code I have so far looks like this...

Sub FindRecord()
Dim DBConnection As ADODB.Connection
Dim DBRecordSet As ADODB.Recordset
Dim FilePath, Query As String
Dim FindRecord As Worksheet

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Set FindRecord = Worksheets("Find Record")
Set DBConnection = New ADODB.Connection
DBName = Worksheets("Matrix").Range("DatabaseName").Value
DBLocation = Worksheets("Matrix").Range("DatabaseLocation").Value
FilePath = DBLocation & DBName

With DBConnection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open FilePath
End With
Query = "SELECT * FROM tblCustomerData WHERE RecordID =" & Worksheets("Index").Range("Search").Value
Set DBRecordSet = New ADODB.Recordset
DBRecordSet.CursorLocation = adUseServer
DBRecordSet.Open Source:=Query, ActiveConnection:=DBConnection, CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, Options:=adCmdText

FindRecord.Activate
Range("A2:G" & Range("A65536").End(xlUp).Offset(1, 0).Row & "").ClearContents


With DBRecordSet
Worksheets("Find Record").Range("B2").Value = .Fields("FirstName")
Worksheets("Find Record").Range("C2").Value = .Fields("Surname")
Worksheets("Find Record").Range("D2").Value = .Fields("Level")
Worksheets("Find Record").Range("E2").Value = .Fields("TeamLeader")
Worksheets("Find Record").Range("F2").Value = .Fields("CSM")
Worksheets("Find Record").Range("G2").Value = .Fields("RACF")
End With


DBRecordSet.Close
DBConnection.Close
Set DBRecordSet = Nothing
Set DBConnection = Nothing

Worksheets("Index").Select
Range("H3:N3").ClearContents
Worksheets("Index").Range("H3").Value = Worksheets("Find Record").Range("A2").Value
Worksheets("Index").Range("I3").Value = Worksheets("Find Record").Range("B2").Value
Worksheets("Index").Range("J3").Value = Worksheets("Find Record").Range("C2").Value
Worksheets("Index").Range("K3").Value = Worksheets("Find Record").Range("D2").Value
Worksheets("Index").Range("L3").Value = Worksheets("Find Record").Range("E2").Value
Worksheets("Index").Range("M3").Value = Worksheets("Find Record").Range("F2").Value
Worksheets("Index").Range("N3").Value = Worksheets("Find Record").Range("G2").Value
Worksheets("Index").Activate
Range("A1").Select
End Sub
...I'm guessing that I need some sort of routine that for every RecordID within tblAddressData that matches my query, I need to return to Excel. I just don't know how to do it!
Can anyone help? Please let me know if this is confusing.
Andy

[CreganTur]

Welcome to the forum- always good to see new members!

When you reference your recordset it will only give you whatever record it is currently on, generally the first record, until you tell it to move to a different record. There are many methods for moving through a recordset: Movefirst, MoveLast, MoveNext, MovePrevious, Move(), etc.

What you need to do is move through your recordset from the start to the end, so that you can update all of the records from the recordset into your Excel spreadsheet. You can do this by looking for the EOF- this is the end of the recordset. The following example will loop through a recordset, printing the value of the first field to the Immediate Window, until it reaches the end (rst is the ADO recordset object):
Do Until rst.EOF
Debug.Print rst.Fields(0).Value
rst.MoveNext
Loop

This is a basic example, but it points you in the right direction- now you just need to adapt your existing code to utilize a loop and move methods so that all records in a recordset are touched.

Let us know if you get stuck or need any other help.

HTH:thumb

[AndrewKent]

Hi there,

Thanks for the welcome to the board!

Okay, so that things are clear for me, the recordset is the volume of records that are returned from my query, yes?

Secondly, I'm setting this up so that any query that is run will only ever bring back 1 record in the tblCustomerData table but could bring back up to 4 records in the tblAddressData table.

Should I therefore only loop the tblAddressData section or can I get away with looping the whole lot?

Also, where I have...


.Fields("FirstName")

...can this be updated to something like...


tblCustomerData.Fields("FirstName")

...if there are fields in different tables with the same name (I'm planning on adding a tblStaffData at some point which will need this).

Many thanks,

Andy

[CreganTur]

Okay, so that things are clear for me, the recordset is the volume of records that are returned from my query, yes?

This is correct. You have to iterate through all of the records in the recordset to see them.


Secondly, I'm setting this up so that any query that is run will only ever bring back 1 record in the tblCustomerData table but could bring back up to 4 records in the tblAddressData table.

Then you need to have the primary key from tblCustomerData as a foreign key in tblAddressData. Test your query in Access and make sure it's pulling what you expect.


Should I therefore only loop the tblAddressData section or can I get away with looping the whole lot?

the recordset is a singular entity. You have to iterate through the recordset itself. If you base it on a query that pulls from 2 tables it is still a single entity, it is not divided up by the tables that make up the query.