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
- I'm assuming your DB is named Class.mdb, but this is not relevant.
- Either manually or programmatically, create 10 Tables named Class1, Class2, Class3,...Class10. Each Table will consist of only Data relevant to that Class.
- 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.
- The critical Code Lines that make this happen, namely Filter for each Class Data, are 33 and 34 below.
- 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
- Function Calls:
- Dim bytClassNum As Byte
-
-
'Call the Function once for each Class
-
For bytClassNum = 1 To 10
-
Call fExportAllDBObjects(bytClassNum)
-
Next
- You will also have to insert code to Transfer the other Table.
- I also tested the Code and it works quite well. If you have any questions whatsoever, please do not hesitate to ask.
- If you wish, I can Attach the Demo that I created for this Thread, just let me know.
7 2176
What other Objects exist in the Database, such as: Forms, Reports, Querys, Macros, Modules, Tables, etc...?
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.
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.
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.
- I'm assuming your DB is named Class.mdb, but this is not relevant.
- Either manually or programmatically, create 10 Tables named Class1, Class2, Class3,...Class10. Each Table will consist of only Data relevant to that Class.
- 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.
- The critical Code Lines that make this happen, namely Filter for each Class Data, are 33 and 34 below.
- 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
- Function Calls:
- Dim bytClassNum As Byte
-
-
'Call the Function once for each Class
-
For bytClassNum = 1 To 10
-
Call fExportAllDBObjects(bytClassNum)
-
Next
- You will also have to insert code to Transfer the other Table.
- I also tested the Code and it works quite well. If you have any questions whatsoever, please do not hesitate to ask.
- If you wish, I can Attach the Demo that I created for this Thread, just let me know.
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
I am Uploading the Demo to this Thread for the benefit of anyone who wishes to see Code also.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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....
|
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...
|
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....
|
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,...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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...
|
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...
| |