How do I create a loop in Access VBA/SQL to select records and output to Excel?

Question

Hello,

I need some help on writing some VBA/SQL code into a module in Access, which should do the following:

• Loop through Table 'Schools', with just one field (SchoolName).

• Use each value in the first table to select records from Table 'ChildData', with several fields about individual children, including which school they attend (SchoolName).

• For each school, export the data for its attending children into a separate Excel workbook named for that school.

Can anyone give me any starting points? Please be aware that I know very little about either VBA or SQL and am pretty much starting from scratch, therefore even the most basic explanations would be very helpful!

Thanks, AVN

Answer 1

It would be simpler to create a query which joins the two tables on school, and then use a pivot in excel with school as the page, and use the show pages to get each school on a separate page

If you want to do it the more complicated way then in VBA you need a recordset from the schools table which shows all the schools, and a recordset from the childdata with the required fields, and a parameter against the school field.

Then in VBA you use a do loop control, which proceeds while school.EOF=false and for each school record get the childdata recordset, and put it to Excel

I would tend to do this in Excel VBA and use copyfromrecordset to put the data into Excel -simple enough to create a new sheet for each school and set the sheet name to school. You would need to add the relevant references to the VBA project - DAO or ADO - to be able to connect to the Access data.

Answer 2

I can't see anything in your question that indicates you need anything complicated.

The first step is to create a query that joins the schools and students and when that runs, export to Excel via the menus. If that works, then you need to alter it so that you are outputting one school at a time. The "easy" way to do this would be to add to your query a parameter on School so that each time the query runs, you're displaying only one school.

Now, if you want to automate that, it becomes more complicated because the parameter will get in the way.

So, my suggestion would be to create an unbound form that has a combo box on it that displays the list of schools. You then use a reference to that combo box as criteria for your query, and use that to drive the code behind a command button:

  Private Sub cmdOutputSchool_Click()
    If IsNull(Me!cmbSchool) Then
       MsgBox "You must first choose a school from the dropdown.", _
         vbExclamation, "Choose a school"
    Else
       DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
         "qrySchoolOutput", CurrentProject.Path & "\" & Me!cmbSchool & ".xls"
    End If
  End Sub

Now, this doesn't automate the process, but it at least makes it easy to output each report in succession.

If you wanted it to be automated, one easy way to do that would be to bind the form to the Schools table, and bind your query to the display of the School Name on the form. Then you could have the command button walk through the records of the form and output the report for each school:

  Private Sub cmdOutputSchool_Click()
    With Me.RecordsetClone
      .MoveFirst
      Do Until .EOF
        Me.Bookmark = .Bookmark
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
          "qrySchoolOutput", CurrentProject.Path & "\" & Me!SchoolName & ".xls"
        .MoveNext
      Loop
    End With
  End Sub

This will output a spreadsheet for each school in the table you've bound to the form.

Answer 3

As mentioned, you can use "" inside a string to get a quote. Example:

MsgBox "I am an ""Example""."

As for the other ,you don't really need VBA to do this. You can do it with a query:

SELECT ChildData.* INTO [Excel 8.0;HDR=YES;IMEX=2;DATABASE=C:\Example.xls].[MyWorksheet]
FROM ChildData INNER JOIN Schools ON ChildData.SchoolName = Schools.SchoolName;

You can of course do the same thing from VBA if you really want to like so:

CurrentDB.Execute "SELECT ChildData.* INTO [Excel 8.0;HDR=YES;IMEX=2;DATABASE=C:\Example.xls].[MyWorksheet] FROM ChildData INNER JOIN Schools ON ChildData.SchoolName = Schools.SchoolName;"

Still another way would be to create a select query that pulled your data and then use DoCmd.OutputTo:

DoCmd.OutputTo acOutputQuery,"MyQuery",acFormatXLS,"C:\Test.xls"

You can also use DoCmd.TransferSpreadsheet:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "MyQuery", "C:\Test.xls", True