Archive in existing DB or Separate? 2

[perrymans]

What do you suggest, when archiving table information (old records, not replication). Do you leave it in the same DB or create a new archive DB?

I would think with a seperate archive DB, you could maintain the same object names, and use your same frontend (copied and links change of course) to access the old data.

Pros? Cons?

Thanks. Sean.

 

[FancyPrairie]

I prefer duplicating the layout of the live tables and placing the archived tables in a separate database. Note that the names of the archived tables are the same name as the live tables.

On the main edit screen and the report criteria screen, the user is allowed to press a button to view the archived data (I do it via the menubar). The button says "View Archived Data". Upon pressing it, the button label changes to "View Live Data" and all controls on the main edit form are locked and the words "Archive Data (Read Only)" are displayed on the form so the user knows what set of data they are looking at. In addition, the tables are relinked to the archived database. When the user selects "View Live Data", the controls are enabled, "Archive Data..." label becomes invisible, and the tables are relinked to the live database.

This concept works great, because you can use the same reports and forms to view the live data as well as the archived data.

There's one gotcha. When you open the forms (main edit form, report critera form, etc), you need to check to see which database you're linked to (live or archived). I prefer to assume that the tables are always linked to the live database. Therefore, upon closing the form, if it's linked to the archived database, I relink it to the live database. That way, any other forms that will be opened will be pointing to the live database. Note that you should still check, upon opening a form, as to which database the tables are linked to.

Here's the code:

Private Sub cmdViewArchive_Click()

Dim strDatabase As String

If (InStr(cmdViewArchive.Caption, "Archive&quot) Then
strDatabase = "C:\NameOfArchiveDatabase.mdb"
cmdViewArchive.Caption = "View Live Data"
' DISABLE CONTROLS HERE
' MAKE VISIBLE "Archived Data (Read Only)"
Else
strDatabase = "c:\NameofLiveDatabase.mdb"
cmdViewArchive.Caption = "View Archived Database"
' ENABLE CONTROLS HERE
' MAKE INVISIBLE "Archived Data (Read Only)"
End If

Call LinkTable(strDatabase, "All&quot

End Sub

This is the function that relinks. You pass it the path and name of the database you want to link to and the names of the tables you want to relink (separated by commas). If you want to link all of the tables, simply pass the string "All".

Function LinkTable(strLinkToDBname As String, _
ParamArray varTblName() As Variant)

'********************************
'* Declaration Specifications *
'********************************

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef

Dim i As Integer

Set dbs = CurrentDb

'***********************
'* Relink the tables *
'***********************

If (varTblName(0) = "All&quot Then

For Each tdf In dbs.TableDefs
If (Left$(tdf.Name, 4) <> &quot;MSys&quot And (Left$(tdf.Name, 4) <> &quot;USys&quot And (Left$(tdf.Name, 1) <> &quot;~&quot Then
tdf.Connect = &quot;;DATABASE=&quot; & strLinkToDBname
tdf.RefreshLink
End If
Next

Else

For i = 0 To UBound(varTblName)
Set tdf = dbs.TableDefs(CStr(varTblName(i)))
tdf.Connect = &quot;;DATABASE=&quot; & strLinkToDBname
tdf.RefreshLink
Next i

End If

'********************
'* Exit Procedure *
'********************

ExitProcedure:

Exit Function

'****************************
'* Error Recovery Section *
'****************************

ErrHandler:

MsgBox Err.Description, vbExclamation

Resume ExitProcedure


End Function

 

[perrymans]

This is a dream come true for me. Unfortunately, no matter how many times I press for a star, it only gives one.

I am so grateful that you went ahead and posted the code for this. I am sure you could tell it was going to be my next question anyway.

Thanks again! Sean.

 

[perrymans]

I placed the cmdbutton on my main form (switchboard).

Everything seems to work great so far, except...

If I set to Archived data, then exit the DB without pushing the cmdViewArchive again to relink to Live Data, then when the DB is reopened, the cmdButton says &quot;View Archived Data&quot; but the tables are still linked to the archive.

Is there a way to force a relink to live data on exit of the DB? Or check the link when the DB opens?

Thanks. Sean.

 

[perrymans]

Also, what does:

&quot;If (InStr(cmdViewArchive.Caption, &quot;Archive&quot) Then&quot;

perform? Is it looking for the Caption to say &quot;Archive&quot;?

Also, I think my problem is with calling the Function you gave. I saved it as a module modLinkTables. How do I use it after that?

Thanks. Sean.

 

[FancyPrairie]

Several things I always do. When the database is first opened, I check to make sure my tables are linked to the live database. If not, I relink them to the live database. Second, when I open a form, I make sure it's initially linked to the live database. If not, I relink to the live database. Third, when I close a form that allowed me to changed from Live to Archive, I make sure the tables are linked to the Live database prior to closing. If not, I relink them to the live database before closing.

You can make these checks in the following areas:
AutoExec ... Have your Autoexe macro call a function that checks to see if the tables are linked to the live or archived database. If linked to archive, then relink to live.
OnFormOpen ... Check when form is opened
OnFormClose .. Check when form is closed.

Note that the OnFormClose event will be when the tables are relinked by to live. If you relink back to the live database prior to closing, it will be very seldom that they will need to be relinked at startup or when a form is opened.

The statement, If (InStr(cmdViewArchive.Caption, &quot;Archive&quot) Then is checking to see if the caption of the command button contains the word Archive. If it does, then I know that I need to relink to the Archive database. I could have set the If statement so that it read like this:

If (cmdViewArchive.Caption = &quot;View Archived Data&quot then

Either way will work.

If you saved it as a module. Then there's no problem. Just call it.

 

[perrymans]

I have never worked with the AutoExec macro, don't know where it is or how to change it. I know it is what Access runs on startup, but that's it.

Could you also tell me what the call would look like? I am sure it is not

Call LinkTable

I am just now learning that it is better to use modules, not form code. So I don't know how Modules interact or the rules for calling them (except for as the value of an unbound textbox).

Sorry to be such a pain.

Thanks. Sean.

 

[FancyPrairie]

First, create a new module. Inside the module copy and paste the code below. After you make the necessary changes to the code, save it and name the module modStartUp. Then open a new macro, in the Action column, select Run Code. In the Function Name field, enter =StartUp(). Exit the macro and name it AutoExec. Your done.

What this code does is checks to see what database a given table is linked to (via GetLinkedDbName). If it's not linked to your live database, it relinks the tables to the live database (either all of the tables or the ones you specify).

Note that in the OnClose event of your forms, all you need to do is call ReLinkToLiveDb.

Function StartUp()

    Call RelinkToLiveDb()

End Function

Function RelinkToLiveDb()

    Dim strDBName as String

    strDBName = GetLinkedDBName("TheNameOfOneOfYourLiveTables")
    if (strDBName <> &quot;ThePathAndNameOfYourLiveDatabase&quot;) then
        Call LinkTable(&quot;ThePathAndNameOfYourLiveDatabase&quot;, &quot;All&quot;)
    End IF

End Function

Public Function GetLinkedDBName(strTblName As String) As String
         
'********************************
'*  Declaration Specifications  *
'********************************

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    
    Dim i As Integer
    
    Dim strDBName As String
    
'****************************************************************************************
'*  Open the current database and read the &quot;Connect&quot; property of the current table.     *
'*  For example, the &quot;Connect&quot; property of the table &quot;tblMMC_MsgBox&quot; equals             *
'*  &quot;;DATABASE=\\mhs22\mhs_lib\MMC_Library_Access70_Tbl.mda&quot;.  Therefore, the string    *
'*  returned to the caller would equal &quot;\\mhs22\mhs_lib\MMC_Library_Access70_Tbl.mda&quot;.  *
'****************************************************************************************

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    
    strDBName = dbs.TableDefs(strTblName).Connect

    If (Len(strDBName) > 0) Then
        GetLinkedDBName = Right(strDBName, Len(strDBName) - (InStr(1, strDBName, &quot;DATABASE=&quot;) + 8))
    Else
        GetLinkedDBName = &quot;table not found&quot;
    End If
    
'********************
'*  Exit Procedure  *
'********************

ExitProcedure:

    Exit Function
    
'****************************
'*  Error Recovery Section  *
'****************************

ErrHandler:
    
    If (Err.number = 3265) Then     'IFT, name of table could not be found
        Err.Raise Err.number, &quot;Error occcurred in function GetLinkedDBName&quot;, &quot;The table &quot;&quot;&quot; & strTblName & &quot;&quot;&quot; does not exist.&quot;
    Else
        Err.Raise Err.number, &quot;Error occcurred in function GetLinkedDBName&quot;, Err.Description
    End If
    
    Resume ExitProcedure
    
End Function

 

[perrymans]

You have helped me so much with just this one thread! I've picked upknow-how on several things, and am able to do something with Archiving that I hadn't even conceived. I keep giving stars, but it only shows one. Hopefully it is still putting a vote in for each one.

One last question, do I need the

Function LinkTable(strLinkToDBname As String, _
ParamArray varTblName() As Variant)

posted earlier? When was this used?



ANYONE else reading this can vote FancyPrairie's info as helpful too.

Thanks again! Sean.

 

[FancyPrairie]

Yes, you still need it because that's how you relink from live to archive and from archive to live. Let's sum things up:

Function StartUp() ... This is launched by AutoExec at startup and calls the function RelinkToLiveDB

Function RelinkToLiveDb ... This function calls GetLinkedDbName. If the return value of GetLinkedDbName is not equal to the path and name of the live database, then RelinkToLiveDb calls LinkTable to relink your tables to the live database.

Function GetLinkedDBName ... This function returns the path and name of the database the table you pass it, is linked to.

Function LinkTable ... Links the tables you specify (or ALL of them) to the database you specify. That is, links the tables to either the archived database or the live database.

The OnClose/OnOpen events of affected forms should call RelinkToLiveDb. In 99.9% of time, it will simply verify that you are already linked to the live database. However, in the rare case that you're not, it will relink for you.

 

[perrymans]

Thanks again. I understand the big picture now. ANd I'll leave you alone about it.

Thanks again! Sean.

 

[JumpinJimRivers]

Hello,

I am trying to relink my tables to another db and found this old thread. The initial code posted here is exactly what I need but I tried it and I keep getting an Invalid Operation error on:

tdf.Connect = &quot;;DATABASE=&quot; & strLinkToDBname

And I don't know why! Any help is appreciated!



Here's my exact code:

Option Compare Database
Option Explicit

Public Sub LinkTablesToDatabase()

Dim strDatabase As String

If Forms!frmMainMenu!cboSelectInactiveJob = &quot;&quot; Then
strDatabase = &quot;C:\My Documents\Access\PMP_be.mdb&quot;
' DISABLE CONTROLS HERE
' MAKE VISIBLE &quot;Archived Data (Read Only)&quot;
Else
strDatabase = &quot;C:\My Documents\Access\Archive.mdb&quot;
' ENABLE CONTROLS HERE
' MAKE INVISIBLE &quot;Archived Data (Read Only)&quot;
End If

LinkTable strDatabase, &quot;All&quot;

End Sub

Function LinkTable(strLinkToDBname As String, ParamArray varTblName() As Variant)
On Error GoTo ErrorHandler

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef

Set dbs = CurrentDb
If (varTblName(0) = &quot;All&quot Then
For Each tdf In dbs.TableDefs
If (Left$(tdf.Name, 13) <> &quot;tblNonJobData&quot Then
tdf.Connect = &quot;;DATABASE=&quot; & strLinkToDBname
tdf.RefreshLink
End If
Next
Else
Dim i As Integer
For i = 0 To UBound(varTblName)
Set tdf = dbs.TableDefs(CStr(varTblName(i)))
tdf.Connect = &quot;;DATABASE=&quot; & strLinkToDBname
tdf.RefreshLink
Next i
End If

ExitProcedure:
Exit Function

ErrorHandler:
MsgBox Err.Description, vbExclamation
Resume ExitProcedure

End Function

 

[FancyPrairie]

I believe your problem is that you are trying to relink system tables (i.e. tables that begin with &quot;MSys&quot;, &quot;USys&quot;, and temporary files that begin with &quot;~&quot. Note my first post, it contains the following line of code

If (Left$(tdf.Name, 4) <> &quot;MSys&quot And (Left$(tdf.Name, 4) <> &quot;USys&quot And (Left$(tdf.Name, 1) <> &quot;~&quot Then

The above line of code is what you are missing.

 

[jimlee]

Ok, i've read a number of posts on this topic so far and nothing has really helped me. However FancyPrairie's code was EXACTLY what I was looking for. Nice one, thankyou, have a star.

jimlad