Export multiple Tbls from one Qry

[Steve_A]

I am not quite sure if this belongs in Exporting or Queries so if it is in
the wrong place let me know.

I am using Access 2007, my in database I have a table (SupplierRD) with
318,585 records that are related to 346 suppliers (SuppNum). What they want
now is one excel file for each of the 346 suppliers, each file should be
named after that supplier number (6 numeric digits).

This is a sample of the data, there are about 20 more fields but this gets
the idea across I hope. This small table would build two tables and export
to two files, one named 405983.xls and the other would be 011307.xls to
C:\work1\suppliers\

ID PN MC SuppNum
120 100T1532-023 775561001832 405983
121 100T1532-023 775561001832 405983
711 111T2035-001 775561043248 405983
712 111T2035-001 775561043248 405983
713 111T2035-002 775561043255 405983
716 111T2035-002 775561043255 405983
406 111T2007-003 775561002947 011307
407 111T2007-003 775561002947 011307
408 111T2007-004 775561002954 011307
410 111T2007-004 775561002954 011307
419 111T2008-010 775561036887 011307
423 111T2008-010 775561036887 011307
443 111T2009-005 775561036895 011307
444 111T2009-005 775561036895 011307
445 111T2009-006 775561003010 011307
448 111T2009-006 775561003010 011307

Thanks

 

[Ken Snell]

See this article:
Create a Query and Export multiple "filtered" versions of a Query (based on
data in another table) to separate EXCEL files via TransferSpreadsheet (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#FilterExportSepFiles

 

[Steve_A]

Ken I am getting a bit lost, I have made a copy of my database and have tried
to rename my fields to match your naming convention.

Tabel name = EmployeesTable
with my SuppNum renamed to ManagerID

where I am getting lost is in the DLookup is the ManagerNameField something
from the Temp Query?

I am getting a error of
Run-time error '3075':

Syntax error (missing operator) in query expression 'ManagerID='.

when I do a debug it highlites

strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = " & rstMgr!ManagerID.Value)

 

[Ken Snell]

The code step
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = " & rstMgr!ManagerID.Value)

assumes that ManagerID field is a numeric data type. You probably have a
text data type, so change the code to this:
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = '" & rstMgr!ManagerID.Value & "'")

 

[Steve_A]

Well Ken I still have a glitch I think, below is what I have now

Form1 has a button on it that has cmdExport event on click

--------------------------------------------------------------
Option Compare Database

Private Sub cmdExport_Click()
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and field
names
' with the real names of the EmployeesTable table and the ManagerID field
strSQL = "SELECT DISTINCT ManagerID FROM EmployeesTable;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of ManagerID values and create a query for each
ManagerID
' so that the data can be exported -- the code assumes that the actual names
' of the managers are in a lookup table -- again, replace generic names with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False

' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = '" & rstMgr!ManagerID.Value & "'")

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM EmployeesTable WHERE " & _
"ManagerID = " & rstMgr!ManagerID.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

' Replace C:\FolderName\ with actual path
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\1Work\" & strMgr & Format(Now(), _
"ddMMMyyy_hhnn") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

End Sub
------------------------------------------------------------------------
I have 2 tables

Table 1 is named "EmployeesTable"
Fields are
ID Autonumber
PN Txt 100T1432-003
MC Txt 775561001196
ManagerID Txt 405983
ManagerNameField Txt WESTWOOD MFG
matlDescp Txt TENSION FITTING
siteCode Txt S
Rc Txt 01
RdLine Txt 001
EC Txt A
RecType Txt ;ENG LTR /REV NO A03
CGNO Txt 09
sht Txt *

Table 2 is named managersTable
Fields are
ManagerID Txt 405983
ManagernameField Txt WESTWOOD MFG

When I run it this is what I am getting

Run-time error '3464'
Data type mismatch in criteria expression.

this is what it is highliting

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\1Work\DaynaRaymond052710\" & strMgr &
Format(Now(), _
"ddMMMyyy_hhnn") & ".xls"

There were two queries created, on was named "q_WESTWOOD MFG" it has one
field named [ManagerID] from the "EmployeesTable"
When I run this manualy I get a message of "Data type mismatch in criteria
expression"

the other was "zExportQuery" is has one field of "1" with a criteria of "0"
When I run this manualy there is no data but it does have all my fields in it.


it does create a file of "WESTWOOD MFG05Jun10156_1222.xls" but it does not
have any data in it, just a tab named "q_WESTWOOD_MFG"

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

 

[Ken Snell]

You also need to delimit the ManagerID field in the creation of the SQL
statement for the query that is providing the data being exported:

strSQL = "SELECT * FROM EmployeesTable WHERE " & _
"ManagerID = '" & rstMgr!ManagerID.Value & "';"


I've modified your code below -- watch for line-wrapping from the
newsreader:


Private Sub cmdExport_Click()
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and field
names
' with the real names of the EmployeesTable table and the ManagerID field
strSQL = "SELECT DISTINCT ManagerID FROM EmployeesTable;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of ManagerID values and create a query for each
ManagerID
' so that the data can be exported -- the code assumes that the actual names
' of the managers are in a lookup table -- again, replace generic names with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False

' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = '" & rstMgr!ManagerID.Value & "'")

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM EmployeesTable WHERE " & _
"ManagerID = '" & rstMgr!ManagerID.Value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

' Replace C:\FolderName\ with actual path
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\1Work\" & strMgr & Format(Now(), _
"ddMMMyyy_hhnn") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

End Sub

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Well Ken I still have a glitch I think, below is what I have now

Form1 has a button on it that has cmdExport event on click

--------------------------------------------------------------
Option Compare Database

Private Sub cmdExport_Click()
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and field
names
' with the real names of the EmployeesTable table and the ManagerID field
strSQL = "SELECT DISTINCT ManagerID FROM managersTable;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of ManagerID values and create a query for each
ManagerID
' so that the data can be exported -- the code assumes that the actual
names
' of the managers are in a lookup table -- again, replace generic names
with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False

' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = '" & rstMgr!ManagerID.Value & "'")

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM EmployeesTable WHERE " & _
"ManagerID = " & rstMgr!ManagerID.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

' Replace C:\FolderName\ with actual path
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\1Work\" & strMgr & Format(Now(), _
"ddMMMyyy_hhnn") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

End Sub
------------------------------------------------------------------------
I have 2 tables

Table 1 is named "EmployeesTable"
Fields are
ID Autonumber
PN Txt 100T1432-003
MC Txt 775561001196
ManagerID Txt 405983
ManagerNameField Txt WESTWOOD MFG
matlDescp Txt TENSION FITTING
siteCode Txt S
Rc Txt 01
RdLine Txt 001
EC Txt A
RecType Txt ;ENG LTR /REV NO A03
CGNO Txt 09
sht Txt *

Table 2 is named managersTable
Fields are
ManagerID Txt 405983
ManagernameField Txt WESTWOOD MFG

When I run it this is what I am getting

Run-time error '3464'
Data type mismatch in criteria expression.

this is what it is highliting

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\1Work\DaynaRaymond052710\" & strMgr &
Format(Now(), _
"ddMMMyyy_hhnn") & ".xls"

There were two queries created, on was named "q_WESTWOOD MFG" it has one
field named [ManagerID] from the "EmployeesTable"
When I run this manualy I get a message of "Data type mismatch in criteria
expression"

the other was "zExportQuery" is has one field of "1" with a criteria of
"0"
When I run this manualy there is no data but it does have all my fields in
it.


it does create a file of "WESTWOOD MFG05Jun10156_1222.xls" but it does not
have any data in it, just a tab named "q_WESTWOOD_MFG"

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

The code step
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = " & rstMgr!ManagerID.Value)

assumes that ManagerID field is a numeric data type. You probably have a
text data type, so change the code to this:
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = '" & rstMgr!ManagerID.Value & "'")

--

Ken Snell
http://www.accessmvp.com/KDSnell/





.