473,326 Members | 2,126 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Segregate access database conditionally

repath
14
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.WordPress.Com
Dec 31 '10 #1

✓ answered by ADezii

  1. I'm assuming your DB is named Class.mdb, but this is not relevant.
  2. Either manually or programmatically, create 10 Tables named Class1, Class2, Class3,...Class10. 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:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fExportAllDBObjects(bytClass As Byte)
    2. On Error Resume Next
    3. Const conPATH_TO_BKUPS As String = "C:\Documents and Settings\All Users\Desktop\"
    4. Dim strAbsoluteBkUpPath As String
    5. Dim aob As AccessObject
    6. Dim strDBName As String
    7. Dim wrkSpace As Workspace
    8. Dim dbBackup As Database
    9.  
    10. DoCmd.Hourglass True
    11.  
    12. 'Retrive the Current Database Name only, strip out .mdb
    13. strDBName = Replace(Mid$(CurrentDb.Name, InStrRev(CurrentDb.Name, "\") + 1), ".mdb", "")
    14.  
    15. 'Make the Backup DB Name unique for each Class Number
    16. strDBName = strDBName & CStr(bytClass) & ".mdb"
    17.  
    18. strAbsoluteBkUpPath = conPATH_TO_BKUPS & strDBName
    19.  
    20. 'If a Backup already exists for this Class, then DELETTE it
    21. If Dir$(strAbsoluteBkUpPath) <> "" Then
    22.   Kill strAbsoluteBkUpPath
    23. End If
    24.  
    25. 'Get Default Workspace.
    26. Set wrkSpace = DBEngine.Workspaces(0)
    27.  
    28. 'Create the Database
    29. Set dbBackup = wrkSpace.CreateDatabase(strAbsoluteBkUpPath, dbLangGeneral)
    30.  
    31. '***************************************************************************************
    32. 'Export only the Table pertaining to each Class as defined by bytClass
    33.     DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
    34.                            acTable, "Class" & CStr(bytClass), "Class" & CStr(bytClass)
    35. '***************************************************************************************
    36.  
    37. 'Export all Queries
    38. For Each aob In CurrentData.AllQueries
    39.   DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
    40.                          acQuery, aob.Name, aob.Name
    41. Next
    42.  
    43. 'Export all Forms
    44. For Each aob In CurrentProject.AllForms
    45.   DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
    46.                          acForm, aob.Name, aob.Name
    47. Next
    48.  
    49. 'Export all Reports
    50. For Each aob In CurrentProject.AllReports
    51.   DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
    52.                          acReport, aob.Name, aob.Name
    53. Next
    54.  
    55. 'Export all Macros
    56. For Each aob In CurrentProject.AllMacros
    57.   DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
    58.                          acMacro, aob.Name, aob.Name
    59. Next
    60.  
    61. 'Export all Modules
    62. For Each aob In CurrentProject.AllModules
    63.   DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
    64.                          acModule, aob.Name, aob.Name
    65. Next
    66.  
    67. DoCmd.Hourglass False
    68. End Function
  6. Function Calls:
    Expand|Select|Wrap|Line Numbers
    1. Dim bytClassNum As Byte
    2.  
    3. 'Call the Function once for each Class
    4. For bytClassNum = 1 To 10
    5.   Call fExportAllDBObjects(bytClassNum)
    6. 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.

7 2176
ADezii
8,834 Expert 8TB
What other Objects exist in the Database, such as: Forms, Reports, Querys, Macros, Modules, Tables, etc...?
Dec 31 '10 #2
Lysander
344 Expert 100+
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.
Jan 2 '11 #3
repath
14
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.
Jan 7 '11 #4
repath
14
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.
Jan 7 '11 #5
ADezii
8,834 Expert 8TB
  1. I'm assuming your DB is named Class.mdb, but this is not relevant.
  2. Either manually or programmatically, create 10 Tables named Class1, Class2, Class3,...Class10. 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:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fExportAllDBObjects(bytClass As Byte)
    2. On Error Resume Next
    3. Const conPATH_TO_BKUPS As String = "C:\Documents and Settings\All Users\Desktop\"
    4. Dim strAbsoluteBkUpPath As String
    5. Dim aob As AccessObject
    6. Dim strDBName As String
    7. Dim wrkSpace As Workspace
    8. Dim dbBackup As Database
    9.  
    10. DoCmd.Hourglass True
    11.  
    12. 'Retrive the Current Database Name only, strip out .mdb
    13. strDBName = Replace(Mid$(CurrentDb.Name, InStrRev(CurrentDb.Name, "\") + 1), ".mdb", "")
    14.  
    15. 'Make the Backup DB Name unique for each Class Number
    16. strDBName = strDBName & CStr(bytClass) & ".mdb"
    17.  
    18. strAbsoluteBkUpPath = conPATH_TO_BKUPS & strDBName
    19.  
    20. 'If a Backup already exists for this Class, then DELETTE it
    21. If Dir$(strAbsoluteBkUpPath) <> "" Then
    22.   Kill strAbsoluteBkUpPath
    23. End If
    24.  
    25. 'Get Default Workspace.
    26. Set wrkSpace = DBEngine.Workspaces(0)
    27.  
    28. 'Create the Database
    29. Set dbBackup = wrkSpace.CreateDatabase(strAbsoluteBkUpPath, dbLangGeneral)
    30.  
    31. '***************************************************************************************
    32. 'Export only the Table pertaining to each Class as defined by bytClass
    33.     DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
    34.                            acTable, "Class" & CStr(bytClass), "Class" & CStr(bytClass)
    35. '***************************************************************************************
    36.  
    37. 'Export all Queries
    38. For Each aob In CurrentData.AllQueries
    39.   DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
    40.                          acQuery, aob.Name, aob.Name
    41. Next
    42.  
    43. 'Export all Forms
    44. For Each aob In CurrentProject.AllForms
    45.   DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
    46.                          acForm, aob.Name, aob.Name
    47. Next
    48.  
    49. 'Export all Reports
    50. For Each aob In CurrentProject.AllReports
    51.   DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
    52.                          acReport, aob.Name, aob.Name
    53. Next
    54.  
    55. 'Export all Macros
    56. For Each aob In CurrentProject.AllMacros
    57.   DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
    58.                          acMacro, aob.Name, aob.Name
    59. Next
    60.  
    61. 'Export all Modules
    62. For Each aob In CurrentProject.AllModules
    63.   DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
    64.                          acModule, aob.Name, aob.Name
    65. Next
    66.  
    67. DoCmd.Hourglass False
    68. End Function
  6. Function Calls:
    Expand|Select|Wrap|Line Numbers
    1. Dim bytClassNum As Byte
    2.  
    3. 'Call the Function once for each Class
    4. For bytClassNum = 1 To 10
    5.   Call fExportAllDBObjects(bytClassNum)
    6. 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.
Jan 7 '11 #6
repath
14
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
Jan 10 '11 #7
ADezii
8,834 Expert 8TB
I am Uploading the Demo to this Thread for the benefit of anyone who wishes to see Code also.
Attached Files
File Type: zip Class.zip (22.3 KB, 107 views)
Jan 10 '11 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

25
by: cory | last post by:
Hi, I have an Access database and am having an ASP.NEt application written for it. It is almost complete. I have a hosting company that I signed up with a month ago but before I did anything I...
10
by: MHenry | last post by:
Hi, We were going merrily along for 6 years using this database to record all client checks that came into our office, including information about what the checks were for. Suddenly, network...
5
by: premmehrotra | last post by:
I currently have a multi-user access database which is put on a shared drive L: on a Windows Servers. Entire database is one file premdb.mdb. Users access this database from their laptops....
8
by: John Baker | last post by:
Hi: I am URGENTLY in need of some book or web site OR tool that will help me integrate a relatively simple access application into a web page or pages. This is a time recording system (by...
7
by: carl.manaster | last post by:
I'm new to this game. I can find my way around C# without any trouble, and I've used Access, a little bit, in the past. Now a friend wants an application of mine to read from his Access database....
15
by: philip | last post by:
On a form, I have a datagridview. This datagridview is constructed on a dataset filled by a tableadapter. The table adapter do very well what it must do when filling dataset. Insertions,...
7
by: Allison | last post by:
Hi -- we are a small manufacturing looking for a multi-user database to take customer orders (nothing too complicated, with 3 users total). We think we should be using Access, but are wondering...
2
by: jritacco | last post by:
I have a user that is somehow managing to open multiple copies of a single Access database. We are using Access 2003 and everything I have tried makes it IMPOSSIBLE to open the same database more...
18
by: surfrat_ | last post by:
Hi, I am having the following problems in getting Microsoft Visual Studio 2005 Professional to link to an Access .mdb database. Please help me to sort this out. Problem 1: The Microsoft...
21
by: nihad.nasim | last post by:
Hi there, I have a database in Access that I need on the web. The web page should connect to the database and write records for certain tables and view records for others. I want to know a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.