Combining two Microsoft Access Databases into one with a form to choose between them

Question

So I've been assigned to work on two identical databases which are very poorly made. Both are split up into front/back ends, but that have different data.

Is it possible to combine both databases into one and create a form to allow the user to choose which "database" they will interact with?

To clarify, the school that utilizes these databases has a database for the Fall semester and Spring semester. Both contain the exact same forms/queries/tables, but the specific data differs. e.g. Maybe a teacher teaches 3 classes in the fall, but 4 classes in the Spring (the database is way more complicated that this though). The structure of the data remains the same, but the specific data changes depending on the database.

So, if they need to access data, they need to open the corresponding database. As a result, they are constantly open/closing different databases. What they want to do is be able to just use one database and switch between Fall/Spring.

Is this at all possible?

Answer 1

Without knowing the exact structure of your data it's hard to give specific answers, but my gut feeling is most definitely these two databases could be combined. Combine all the data in tables of the same name, but add a "Semester" column to any table that would result in duplicated data. Then simply create the application frontend so wherever necessary the user would select the Semester they want to view, and pass it to your queries retrieving the data. You could even carry the "Semester" value from the beginning as you point out, and pass it from screen to screen (w/ a global application-level variable).

It's been a while since I've had to code in Access in my professional life, but I'm sure there's a way to accomplish your goal. Maybe post more details of your specific data structure, and we can all jump in and give pointers!

Answer 2

Here is a table linking function that I've used many times over the years. In order to use this, you'll have to provide a couple of functions:

1. ThisTableShouldBeChanged is passed the name of a table and returns true if the link to the table should be re-linked as part of the database switch. This will allow you to selectively exclude tables from the switch.
2. GetConnectionString should return the name of the new connection string. Constructing a connection string to another Access database is simple, just put the full database path into this format: ";DATABASE=path"

In order to do what you want, you just have to run this function when the user chooses a new databse to open. One word of advice: I would put an unmistakable element in the UI, or example the background color of the screens, that will signal to the user which database they are working on so there is no confusion.

This function can also be used to link tables to an ODBC or SQL Server connection when deploying into a production environment. That's what I generally use it for.

Public Function LinkTables() As Boolean
On Error GoTo HandleError

    Dim tdf As TableDef
    Dim tdfNew As TableDef
    Dim strName As String
    Dim tdfs As TableDefs
    Dim strSource As String
    Dim strConnect As String

    Dim blnThrowErr As Boolean

    Dim arr() As Variant

    Set tdfs = CurrentDb.TableDefs

    Dim intCount As Integer
    Dim i As Integer

    intCount = tdfs.Count

    strConnect = GetConnectionString()

    i = 1

    'Save a copy of the existing table names'
    ReDim arr(tdfs.Count, 2)
    For Each tdf In tdfs
        strName = tdf.Name

        If ThisTableShouldBeChanged(strName) Then
            If tdf.Connect <> "" Then

                strSource = tdf.SourceTableName

                arr(i, 1) = strName
                arr(i, 2) = strSource
                i = i + 1
            End If
        End If
    Next tdf

    tdfs.Refresh

    Dim strNameRep As String

    'Create new linked tables'
    For i = 1 To UBound(arr)

        If arr(i, 1) <> "" Then

            Set tdfNew = New TableDef

            strName = arr(i, 1)

            tdfNew.Name = arr(i, 1)
            tdfNew.SourceTableName = arr(i, 2)
            tdfNew.Connect = strConnect

            strNameRep = strName & "_temp"

            'rename the old table'
            tdfs(strName).Name = strNameRep

            tdfs.Refresh

            tdfs.Append tdfNew

            If Err.Number <> 0 Then

                Debug.Print Err.Description
                 tdfs(strNameRep).Name = strName

                Err.Clear
            Else
                tdfs.Delete strNameRep
            End If

            On Error GoTo HandleError

        End If
    Next i

    tdfs.Refresh
    LinkTables = True

ExitHere:
    Exit Function
HandleError:
    MsgBox Err.Description & " (" & Err.Number & ")"

    LinkTables = False
    Resume ExitHere
End Function

Answer 3

I think it should be possible to create queries for each of the tables with the original table name and name the new, combined tables with an extra letter or two. Let us say you have a table Students, the new table becomes JointStudents and you create a query Students:

SELECT F1, F2, F3 FROM JointStudents WHERE Semester=Forms!PickSem!txtSemester

This means that any form or query that refers to the table Students will now pick up the query students with the built in filter.

Answer 4

A least-initial-cost solution, setting up an interface to link to the appropriate semester, is still going to involve touching all the forms and adding another (for user-friendly control of the "active" db), and you'll still be incurring additional cost for any changes needed down the road (as they'll be made in two places rather than one).

You don't have to change the tables by hand: you can write SQL to do it. It's possible to write a procedure that steps through existing tables and adds a "Semester" column and populates it ... best to run it in both databases so that you can also populate the columns in the same procedure. If the tables are named the same in both databases, it would even be possible to run a procedure that would step through all of them, updating Fall, updating Spring, and appending Spring to Fall so that you have everything in one database.

A safer method would probably be to copy the Fall structure to a new table and modify that table, in case something happens and you need to "roll back" what you've done.

Given that this question was asked months ago and you're working with a school, you've probably either resolved the issue somehow or have had to put it aside, but if there's still a need for it, I can post VBA that should help.