Access 2000 Moving Record source

[Whiteeagle]

Hi all

I have inherated a bit of a dilema and not sure about the correct way of solving the problem.

The company I work for have 60 identical but seperate databases which are all in seperate folders on the network for the 60 offices that use them. I have taken over looking after these databases and supporting them.

Each database have the same tables and forms but I would like to create a seperate database that will be able to show all 60 database tables without having to open each database seperately.

I know what I would like to do is to link the database tables to the new database and bring in each of the tables I want to use such as the Users Table and the Users group table, but I dont want to end up having a copy of all 60 user forms one for each user table to be able to edit the details or a menu system that has 60 buttons to press. I would like to use one form but to be able to change the form record source to one of the 60 database user tables I want to edit and update.

I would also like to have a menu system that will allow me to tell the form which table record source I want to connect too

Is this possible? Hope Ive expalined myself if not please email me and I will elaborate further

Thanks in advance

White Eagle

[ADezii]

Hi all

I have inherated a bit of a dilema and not sure about the correct way of solving the problem.

The company I work for have 60 identical but seperate databases which are all in seperate folders on the network for the 60 offices that use them. I have taken over looking after these databases and supporting them.

Each database have the same tables and forms but I would like to create a seperate database that will be able to show all 60 database tables without having to open each database seperately.

I know what I would like to do is to link the database tables to the new database and bring in each of the tables I want to use such as the Users Table and the Users group table, but I dont want to end up having a copy of all 60 user forms one for each user table to be able to edit the details or a menu system that has 60 buttons to press. I would like to use one form but to be able to change the form record source to one of the 60 database user tables I want to edit and update.

I would also like to have a menu system that will allow me to tell the form which table record source I want to connect too

Is this possible? Hope Ive expalined myself if not please email me and I will elaborate further

Thanks in advance

White Eagle

Everything you ask is in fact possible, but it would be a rather extensive undertaking. I'll provide you with a solution for your 1st question which is quoted below, and hopefully, you can figure out the rest.

Each database have the same tables and forms but I would like to create a seperate database that will be able to show all 60 database tables without having to open each database seperately.

1. Create a Table on the MASTER Database, yours I'll assume.
2. Name this Table tblExternalTables.
3. Create 3 Fields in this Table as listed below:
   1. Path - TEXT (125)
   2. Table Name - TEXT (50)
   3. Description - TEXT (125)
4. Populate this Table with all the relevant Data for the 60 Tables.
5. Sample Data that I used for test purposes was
   

   Path                                   Table Name             Description
   C:\Test\HMAU_DATA.MDB                  tblEmployees           Employee Biographical Data
    

6. Create a Combo Box on a Form based on tblExternalTables and call it cboExternalTables.
7. The Column Count should be 3, and the first 2 Columns should be Hidden. Ony the Description Field is present in cboExternalTables.
8. Place the following code in the AfterUpdate() Event of tblExternalTables:
   

   Private Sub cboExternalTables_AfterUpdate()
   If IsNull(Me![cboExternalTables]) Then Exit Sub
    
   Dim strDBPath As String, strTableName As String
    
   strDBPath = Me![cboExternalTables].Column(0)
   strTableName = Me![cboExternalTables].Column(1)
    
   'Dim appAccess As Access.Application Declared in Form Module
   'Create new instance of Microsoft Access.
   Set appAccess = CreateObject("Access.Application")
       appAccess.Visible = True
    
   'Open database in Microsoft Access window.
   appAccess.OpenCurrentDatabase strDBPath
    
   'Open specific Table.
   appAccess.DoCmd.OpenTable strTableName
    
   'Keep External Table Window manageable so as not to be
   'confused with Main Access Window
   appAccess.DoCmd.Restore
   End Sub

9. When a Database Description is clicked in the Combo Box, that Database will be opened as though it were the Current Database, and the corresponding Table will be opened and restored.
10. Good luck in your undertaking.