Switching backend mdb

[GraeagleBill]

I have a simple app that obtains its RecordSource based on data for a particular year, e.g., MyBackend2011.mdb; MyBackend2012.mdb; MyBackend2013.mdb. The default when launched is the current year, but I'd like to give the client access to previous years. The databases have identical table structures. What I think I want to do is "re-link" table(s) to a different backend. How do I do that in code?
Thanks,
Bill

[June7]

Here is code I use to reset table links:

Public Sub NormalizeTableLinks()
'Relink tables with UNC pathing
'Use the variables provided to do the manipulations desired
Dim td As TableDef
Dim db As DAO.Database
Dim strOld As String
Dim strNew As String
'replace the following strings as needed
strOld = "R:\Lab\Database\Data\LabData.accdb"
strNew = gstrBasePath & "Data\LabData.accdb"
Set db = CurrentDb
For Each td In db.TableDefs
    If InStr(td.Connect, strOld) > 0 Then
        Debug.Print td.Name
        Debug.Print "Old Link: " & td.Connect
        td.Connect = Replace(td.Connect, strOld, strNew)
        td.RefreshLink
        Debug.Print "New Link: " & td.Connect
    End If
Next td
db.TableDefs.Refresh
End Sub

Why do you have a database for each year? Do you have that much data?

[GraeagleBill]

The DB's apply to an annual event. As such, all the data pertains specifically to a given year. In the past, the client (my friend) has been updating via datasheet view. As a helping friend, I'm simply creating some simple forms and reports whereby it's no longer necessary to operating directly on the table(s). So, having established a naming convention tied to the years, I'd like to give the client the facility to look at past years.

Your sample should serve to solve the problem.

Thanks,
Bill

[June7]

I would make one db unless there is possibility of exceeding 2gb file size.

[GraeagleBill]

Hummmm......I could do that by simply switching between tables whose names difer by a suffix denoting the year. There's only one table, so the RecoreSource SQL expression would be simple to change dynamically. Is that more-or-less what you had in mine?
Bill

[June7]

I would not have separate tables for each year. I would have one table and filter records by the year in forms and reports. That's how my db works for 30 years of records.

[GraeagleBill]

It does make more sense to add a year field to each record where the value of the field indicates the year to which the record pertains. With this approach, I'd have to set the new field appropriately and then merge the tables from the existing DB's. More Hummmmmm............what about the RecordID autonumber as the primary key as the tables are merged?

[June7]

Combining existing data that relies on autonumber as pk/fk is a significant challenge. Not impossible but can be tedious. Is it worth it for your situation? Not for me to say.

Another field for year might not be required. Is there already a date/time field in the table structure? If so, the year can always be extracted from that value.

[GraeagleBill]

Well, this database I've "inherited" isn't what you and I would call normalized by any stretch of the imagination. I think for the long term view, it probably makes sense to restructure the one table into two where the primary table has a one-to-many relationship with data records that are peculiar to a given year. I'll have to give this some more thought as to how much code I'd have to develop to insure the integrity of the resulting tables.
Thanks,
Bill

[June7]

Records are different from year-to-year? Expanded the table for more info?

If there is not presently a parent/child relationship dependent on the autonumber field as pk/fk, then combining the tables from the multiple databases is not an issue. Append years chronologically to the oldest file and let records take new ID. Add fields to the table needed for later years data.

Could split the table but that is a 1-to-1 relationship. Not much gained by splitting. Sure, fits true normalization (no empty fields) but empty fields can be tolerated (I do). It is a balancing act between normalization and ease of data entry/output.

[GraeagleBill]

As the table currently exist. There are fields that identify an individual, name, address, etc. plus fields for that individual that are unique to a given year. The two table split with a one-to-many would be a table of individuals whose RecordID's link to the second table where the records include the year to which the records pertain. Were I to simply add a year field to each DB and then merge the DB's, I would be duplicating all the individual identification and hense the possiblilty of updating such information for any given year but failing to propagate the updates. Of course, the same would happen if I switch from one DB to another where the individual appears multiple times.

[June7]

Sounds like combining the tables would be a first and simple step.

Then splitting into 1-to-many is not so tricky. Much easier than trying to combine dbs that already have dependencies.

[GraeagleBill]

Yes, by a long shot. If there were dependencies in the multiple DB's, then I would use the code you shared at the outset of this thread.

Thanks,
Bill

[GraeagleBill]

June7,
I've split the table into two tables and established a one-to-many relationship between them, ROSTER, APPENDAGES. The data pertaining to any particular year is in the APPENDAGE table, which includes a field entitled RetYear. I created a quaery where the APPENDAGE table is INNER JOINed to the ROSTER. When I run the query, everything is exactly as one would expect. As a test, I specified "2012" as the criteria in the query and ran the query to see what would typically be the RecordSource for a form or report, having set Me.Filter. The code below is an example of a form's code sheet wherein the filter is set to a particular year. The code dies on the Me.FilterOn issuing the message "You canceled the previous operation". If I set Me.Filter and omit the Me.FilterOn, where the form's properties stipulates "Allow Filters" equals "Yes". The filter is ignored and I get whatever record is encountered with the first match with the RecordID -> RecID relationship.
Any idea what might be happening?
Thanks,
Bill

Option Compare Database
Option Explicit
Private Const TwipsPerInch = 1440
Private Const ControlWidth = 3060 'The image control width 2.125" x TwipsPerInch
Dim a() As String
Dim CurYear As String

Private Sub Form_Open(Cancel As Integer)

CurYear = Year(DATE)
Me.lblFrmHeading.Caption = "GCC " & CurYear & " Retreat Attendee Details"
Me.Filter = "RetYear = " & CurYear
Me.FilterOn = True
Me.Requery

a = Split(Me.OpenArgs, ";")

Me.RecordsetClone.FindFirst "[RecordID] = " & a(0)
Me.Bookmark = Me.RecordsetClone.Bookmark

MsgBox Me.RecID & " " & Me.RetYear


End Sub

[GraeagleBill]

SIGH! You'd think I'd learned something by now.

Me.Filter = "RetYear = " & """" & CurYear & """"