Export To From Access 03 to Excel using VBA

[MarcusMaximus]

Hi I have a database with many staff members and each staff member has many Customers assigned to them. I want to export customer details to a separate excel file for each staff member. I don't want to have to do this manually as its going to take up time. I'd like to name the files according to the staff number also if possible.

I don't know how I can do this in Access. Do I write a module or a macro or something. I wrote a macro previous for something similar where i created 50 individual queries for each member of staff and used the TransferSpreadsheet function in a macro but this was too laborious.

Any Ideas.
Thanks

[MarcusMaximus]

Public Function ExportFiles()

    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.Open "SELECT DISTINCT tblcustomer.userid FROM tblcustomer"
    
    While Not rst.EOF
        Dim strquery As String
        strquery = "Select * from tblcustomer where userid = '" &   rst!USERID & "';"
           
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
                strquery, "W:\Folder\sub\sub\" & rst![USERID] & ".xls"
    
        rst.MoveNext
    
    Wend
    
End Function

Here's what i got but i get the following error:

Runtime error 3011

The microsoft jet database engine could not find the object 'Select * from tblcustomers where userid ='002';'.Make sure the object exists and you spelt its name and the path correctly.