Segregate access database conditionally

[repath]

Dear All,

I will take a hypothetical example:
I have a consolidated database which has the details all the students from class 1 to class 10 in a school (in Ms.Access)
All the data for all the students is maintained in a single table. (nearly 13 fields)

Now I want to segregate them into 10 different databases and distribute it to the 10 different classess so that they donot have to each others data, database being small, will be easier the users and the like.

What I want to do is to write a code that will segregate this single database into 10 different databases and create them on my desktop, so that I can give them independent copies of the same.

Any help I do appreciate.

With Thanks and Regards
Repath Athyala
Www.Repath.Word Press.Com

[ADezii]

What other Objects exist in the Database, such as: Forms, Reports, Querys, Macros, Modules, Tables, etc...?

[Lysander]

Can I suggest the following.

Call your master database 'Master' (obvious)
Create your 10 databases with all the forms, reports, tables etc that they need, but leave the main data table unpopulated.

In your master database, lets assume the table is 'StudentData'

Link your master database to each of the 10 sub database tables called 'StudentData' and in your master table call the attached links
StudentData1
StudentData2
etc.

Then write code to run 10 queries, each of which appends the relevant data to Studentdata1, studentdate2 etc

Run this code, then distribute the data.

This can be done another way, using Access security, but that will only work for Access 2003 and below and is more complicated.

Hope this helps.

[repath]

Dear ADezii,

the database has two tables, two forms and none other.

One form is to input data into existing database
the other is to retrieve data from the existing table on various conditions like top 10, more qualitative student, best in class, best in all classess, top students in each class, their performance per month, quarter, half-year and yearly and the other parameters.

[repath]

Dear Lysander,
what I thought is that,
1. create a new database on the desk top by selecting DISTINCT CLASS clause from the entire database.

2. This will give me values like class I, class II, class III,... class X and store them in an arrya say: arrClass

3. Now I want to create seperate access.mdb file for each class with the name of the class for each access mdb.

4. Create distinct tables (from the master table) with a select query (or any other)

Now keep all the 4 entries in a loop that runs based on the upper bound of the array.

Now entire task will be done in a single looping structure like

Start loop
Get distinct class names with SELECT DISTINCT clause
Create database file (eg: class1.mdb)
create seperate temp tables for each class
export this new table to respective database
export data retrieval form
close loop.

Could you please help me with.

With regards
Repath Athyala.

[ADezii]

1. I'm assuming your DB is named Class.mdb, but this is not relevant.
2. Either manually or programmaticall y, create 10 Tables named Class1, Class2, Class3,...Class 10. Each Table will consist of only Data relevant to that Class.
3. Execute the following Function 10 times passing the Class Number as an Argument each time. Now, 10, distinct, exact copies of the Database, consisting of all Objects, will be made and distributed to your Desktop, but each copy will contain only Data for that Class. Examples: Class7.mdb will contain the Class7 Table only, Class4.mdb will contain the Class4 Table only, etc.
4. The critical Code Lines that make this happen, namely Filter for each Class Data, are 33 and 34 below.
5. Function Definition:
   

   Public Function fExportAllDBObjects(bytClass As Byte)
   On Error Resume Next
   Const conPATH_TO_BKUPS As String = "C:\Documents and Settings\All Users\Desktop\"
   Dim strAbsoluteBkUpPath As String
   Dim aob As AccessObject
   Dim strDBName As String
   Dim wrkSpace As Workspace
   Dim dbBackup As Database
   
   DoCmd.Hourglass True
   
   'Retrive the Current Database Name only, strip out .mdb
   strDBName = Replace(Mid$(CurrentDb.Name, InStrRev(CurrentDb.Name, "\") + 1), ".mdb", "")
   
   'Make the Backup DB Name unique for each Class Number
   strDBName = strDBName & CStr(bytClass) & ".mdb"
   
   strAbsoluteBkUpPath = conPATH_TO_BKUPS & strDBName
   
   'If a Backup already exists for this Class, then DELETTE it
   If Dir$(strAbsoluteBkUpPath) <> "" Then
     Kill strAbsoluteBkUpPath
   End If
   
   'Get Default Workspace.
   Set wrkSpace = DBEngine.Workspaces(0)
   
   'Create the Database
   Set dbBackup = wrkSpace.CreateDatabase(strAbsoluteBkUpPath, dbLangGeneral)
   
   '***************************************************************************************
   'Export only the Table pertaining to each Class as defined by bytClass
       DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
                              acTable, "Class" & CStr(bytClass), "Class" & CStr(bytClass)
   '***************************************************************************************
   
   'Export all Queries
   For Each aob In CurrentData.AllQueries
     DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
                            acQuery, aob.Name, aob.Name
   Next
   
   'Export all Forms
   For Each aob In CurrentProject.AllForms
     DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
                            acForm, aob.Name, aob.Name
   Next
   
   'Export all Reports
   For Each aob In CurrentProject.AllReports
     DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
                            acReport, aob.Name, aob.Name
   Next
   
   'Export all Macros
   For Each aob In CurrentProject.AllMacros
     DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
                            acMacro, aob.Name, aob.Name
   Next
   
   'Export all Modules
   For Each aob In CurrentProject.AllModules
     DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
                            acModule, aob.Name, aob.Name
   Next
   
   DoCmd.Hourglass False
   End Function

6. Function Calls:
   

   Dim bytClassNum As Byte
   
   'Call the Function once for each Class
   For bytClassNum = 1 To 10
     Call fExportAllDBObjects(bytClassNum)
   Next

7. You will also have to insert code to Transfer the other Table.
8. I also tested the Code and it works quite well. If you have any questions whatsoever, please do not hesitate to ask.
9. If you wish, I can Attach the Demo that I created for this Thread, just let me know.

[repath]

Thank you aDezii,
Thank you very much. I am please to hear from you that you are ready to send the demo copy of the same.

Can you please send it to riphath@hotmail .com.

I appreciate your response and happy to see the code.
If required, I will customise it.

With sincere Thanks and regards
Repath Athyala

[ADezii]

I am Uploading the Demo to this Thread for the benefit of anyone who wishes to see Code also.