VBANovice said:
I have a database with multiple tables. I need to break the database into
smaller files based on states.
Why? Are you going to have to send each state database out to someone?
I've been able to do this one at a time by using the transfer Database
function in a Macro but i'd like to set this up to loop through the
different
state values in my Locations table and save each new database into a
different folder.
for example: run query for orders where state = CA
then transfer the query data into a table in a file of the same name but
directory C:/CA/mydata.mdb
then OR
C:/OR/mydata.mdb.
The easiest way, I think, would be to set up a form to provide the state to
be exported (in a text box) as a parameter to a query. That text box need
not be visible to the user -- it could have its Visible property set to No.
For the moment, I'll assume that the form itself is visible, and has a
command button that the user clicks to trigger the process.
Suppose the following:
+ Table named "StateData" containing the data to be exported
This table has a field named "State"
+ Form named "frmExportStates"
+ Text box named "txtState" on frmExportStates
+ Command button named "cmdExport" on frmExportStates
+ Query named "qryExportState"
The SQL of qryExportState would be something like:
SELECT * FROM StateData
WHERE State = Forms!frmExportStates!txtState;
Code for the Click event of cmdExport would be something like this:
'------ start of example code ------
Private Sub cmdExport_Click()
Const conBaseFolder As String = "C:\"
' NOTE: Probably this path should be changed.
' You may not even have permission to write to the
' root folder of C:.
Dim rs As DAO.Recordset
Dim dbState As DAO.Database
Dim strStateFolder As String
Dim strStateDB As String
Set rs = CurrentDb.OpenRecordset( _
"SELECT DISTINCT State FROM Locations", _
dbOpenSnapshot)
With rs
Do Until .EOF
' Make sure the target folder exists.
strStateFolder = conBaseFolder & !State
If Len(Dir(strStateFolder, vbDirectory)) = 0 Then
MkDir strStateFolder
End If
' Delete the old state DB, if it exists.
strStateDB = strStateFolder & "\MyData.mdb"
If Len(Dir(strStateDB)) > 0 Then
Kill strStateDB
End If
' Create state database.
Set dbState = DBEngine.CreateDatabase(strStateDB, dbLangGeneral)
dbState.Close
Set dbState = Nothing
' Move the data for the current state into the state database.
Me.txtState = !State
DoCmd.TransferDatabase acExport, "Microsoft Access", _
strStateDB, acTable, _
"qryExportState", "StateData"
.MoveNext
Loop
.Close
End With
End Sub
'------ end of example code ------
That's just air code, and error-handling is left up to you.
