Split database into smaller db based on values

  • Thread starter Thread starter VBANovice
  • Start date Start date
V

VBANovice

I have a database with multiple tables. I need to break the database into
smaller files based on states.

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.

any help would be appreciated
 
The approach of splitting data into multiple tables (in Access, they are
'tables', not dbs) "based on states" is quite common ... for spreadsheets!

Access is a relational database. It's powerful features and functions
depend on well-normalized relational data. If you try to feed it 'sheet
data, both you and Access will have to work overtime to overcome the 'sheet.

Take advantage of the fact that there are a large number of folks here in
the newsgroups who've been using this product for a number of years.
Instead of starting out stating "how" you want to do something, post a more
detailed description of "why" and "what" (i.e., the underlying business need
that you're trying to solve).

Folks will offer their ideas/approaches, and you can decide what approach
fits best. The more you can tell us about what you are trying to
accomplish, the more detailed suggestions we can offer.

(... and there may be a need to have state related data all in one place --
e.g., you're sending out data to the STATES! ... but we don't know that yet,
and there may be other ways to get that done ... i.e., save all state data
in a single table, then use your queries to Export data to spreadsheets for
the states to use.)

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
VBANovice said:
I have a database with multiple tables. I need to break the database into
smaller files based on states.
Click to expand...

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.
Click to expand...

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.
 
You're right. I try to ask questions in a way that specifically shows me how
a technique works because I want to (a) learn that technique and (b) then try
to see if it solves my current problem.

Really the business problem I'm trying to solve is that I have an Excel file
that needs to import data from the database. over 800 users each having to
repeat the data extracting into their spreadsheet 8-10 times each month.
Typically this happens in a 1-2 day window. The database prototype doesn't
seem to be able to handle very many concurrent users and upsizing to a
enterprise dbms is not an option. The alternative I'm investigating is
publishing out smaller versions of the db to solve the concurrent user issue.


Excel seems to be the best solution for a front end since the users will be
creating multiple what if scenarios. but there is so much data that Excel
doens't seem to be the best solution for data storage.

any thoughts on that would be appreciated.
 
Let me try paraphrasing ... you let me know where I go off track ...

You have 800 users who want to use Excel to manipulate some data (i.e.,
whatif scenarios).

You have a database that holds the data they want to see/use.

When they want to see/use it, they ALL want to see/use it at the same time
("1-2 day window").

They repeat this crunch 8-10 times each month.

I'm pretty sure that Access isn't really the tool to try to manage 800
simultaneous users -- if that's a given, then you'll need a way to reduce
the demand if you're going to keep using Access. Your idea of splitting the
large db into smaller ones that fewer users would be simultaneously hitting
is one possibility.

Another possibility might be to have your large db generate (i.e., export)
the 800 separate sets of data that those users would need. I can imagine
(i.e., this is pure speculation) that you could create a procedure that
steps through a list (i.e., a table) of the output datasets that are needed,
generates and exports each one, then waits for the next time.

In fact, you could consider building that procedure to run every night,
generating the exported output datasets so the users could have fresh data
each morning.

Let me know if I paraphrased your situation correctly, or if the alternative
satisfies the business need.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
That's 90% right. the only difference is that the data doesn't get refreshed
but 1 time a month, there for that 1-2 day period is the 2nd monday or
tuesday of the month.

I used Dirk G's code to build out a database for each level I need then have
included code in the Excel file that will copy the file from the sever to the
users laptop. Next, the Excel file will query the database on the laptop
rather than the server...thereby eliminating the concurrent user problem.

wish I could use SQL Server so that i didn't have 800 micro databases in the
wild but not an option here.

thanks
 
Why have the Excel file "query the database"? Why not have Access write out
an Excel data set that the user can touch directly?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Back
Top