Simple VB Code needed to execute SQL and export output to excel

[EDN Admin]

Hi,
Im completely new to using VB on Access. Im trying to execute a fairly simple operation:
1) I have a table called Master Table. My aim is to split this into various segments (same table structure as Master Table) and export them as Excel files
2) Master Table has thousands of rows. Theres a column called Split To which holds a name
3) There is a 1-column table called Team Member List which has a unique list of names (the names are guaranteed to match the ones in [Master Table].[Split To]
4) The VB code Im writing should read through each row of [Team Member List], and extract those rows where [Master Table].[Split to] = current record value of Team Member List
5) The code Ive written (below) reads Team Member List fine. Im stuck at the place where I need to extract from Master Table and export to Excel
Public Sub splitDB()<br/>
Dim db As DAO.Database<br/>
Dim strSQL As QueryDef<br/>
Dim newStrSql As String<br/>
Dim outputCount As Integer<br/>
<br/>
Dim myRecordSet As DAO.Recordset<br/>
Dim OutputRecords As DAO.Recordset<br/>
Dim rowCount As Integer<br/>
Set db = CurrentDb()<br/>
Set myRecordSet = db.OpenRecordset("Team Member List")
With myRecordSet<br/>
Do While Not myRecordSet.EOF<br/>
If .RecordCount <> 0 Then<br/>
Extract into output files<br/>
The next row is just to keep track of how many rows there are (Debug mode)<br/>
rowCount = myRecordSet.RecordCount<br/>
<br/>
Set strSQL = db.CreateQueryDef("", "SELECT * FROM [Master Table] WHERE [Split to] = " & strCrt & "")<br/>
CODE NEEDED HERE TO EXECUTE THE ABOVE QUERY AND EXPORT TO EXCEL <br/>
myRecordSet.MoveNext<br/>
<br/>
End If<br/>
Loop<br/>
End With<br/>
myRecordSet.Close
End Sub

View the full article