Open a form in a external database....... 4

[cavery]

I have a form that I'm using in my current database, called form 1. I want to add a command button on form 1 that will retrieve and open form 2, which is located in another database. Any suggestions?

Both of the forms are exactly alike, but I've archived alot of records from form1 to form2. I basically want to give users the ability to open up form2(archive records) without having to close the current database. By placing a command button on form1, this will eliminate the need to open another database. Any help would be appreciated!

Thanks,
Clark
Honda of America

 

[FancyPrairie]

Sorry, my mistake. In my haste, I sometimes forget to add the Caption property.

The way you have this code setup won't work as expected. If the command button text = "View Archive Data", then you want to link to the Archived Database when they select the button. Else link to the Live Database. The way you have it written, it will always link to the Live Database. It should look like this:

Private Sub cmdViewArchive_Click()

Dim strDatabase As String

If (InStr(cmdViewArchive.Caption, "Archive&quot) Then
strDatabase = "C:\PD4 ARCHIVE.mdb"
cmdViewArchive.Caption = "View Live Data"
Else
strDatabase = "M:\Data\PD4Master.mdb"
cmdViewArchive.Caption = "View Archived Data"
End If

Call LinkTable(strDatabase, "All&quot

End Sub

 

[cavery]

FancyP, Thanks I thought something didnt look right. I am getting closer, everything works, including the caption for view live data and archive, but I did get an error in the function. (the third line of code) the error was " 3219 Invalid Operation"

I placed an asterik where the error is highlighted:

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

For Each tdf In dbs.TableDefs
* tdf.Connect = ";DATABASE=" & strLinkToDBname
tdf.RefreshLink
Next

Else

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

End If


I assume whatever the problem is, it will also show after the ELSE statment? since the code is exactly alike.

Thanks
Clark

 

[FancyPrairie]

You're trying to relink System tables. Sorry 'bout that. Forget to check for them. Replace your Function LinkTable with this version or add the stuff in red to yours.

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") 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 = ";DATABASE=" & strLinkToDBname
                tdf.RefreshLink

End If

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

'********************
'*  Exit Procedure  *
'********************
        
ExitProcedure:

    Exit Function

'****************************
'*  Error Recovery Section  *
'****************************
        
ErrHandler:
        
    MsgBox Err.Description, vbExclamation
        
    Resume ExitProcedure
    

End Function

 

[cavery]

FancyP, Thanks for the updated version, for some reason I still get the same error:
&quot;Invalid Operation&quot; 3219.

And the line of code below is highlighted:
tdf.Connect = &quot;;DATABASE=&quot; & strLinkToDBname

All of my captions are working and when I chose end, I can still press the View live data button, but the error displays again. Could there something wrong with my Archived Database?....

Thanks
Clark
Honda of America, MFG.

 

[cavery]

FancyP, FYI while debugging, when I place the cursor over
TableDefs for the following code below:
Set tdf = dbs.TableDefs(CStr(varTblName(i)))

It states, <Item not found in this collection



Clark

 

[FancyPrairie]

Set a breakpoint on the line tdf.Connect = &quot;;DATABASE=&quot; & strLinkToDBname (or place the word Stop above the line). Then execute the code and when the program pauses execution, examine tdf.Name. tdf.Name contains the name of the table that the program is attempting to relink. Is it a valid table name? Does it exist in both databases? Also examine strLinkToDBname. This is the name (path\YourDBname.mdb) of the database you're attempting to relink to.

If you still can't determine the problem, try calling the function by passing the names of the tables you want to relink and see if that works. For example, Call LinkTable (&quot;path\YourDbName.mdb&quot;,&quot;Table1&quot;,&quot;Table2&quot;,&quot;Table3&quot

Let me know what you find out. I've tested the code and it works fine.

 

[FancyPrairie]

Received your last post while I was typing mine. So, concerning your last post, examine varTblName(i). It should be a valid table name that exists in both databases.

By the way, you have set a Reference to &quot;Microsoft DAO 3.6 Object Library&quot;.

 

[cavery]

FancyP, In regards to the call function:
'Call LinkTable(&quot;C:WINDOWS\Desktop\PD4 Archive.mdb&quot;, &quot;All&quot
Am I attempting to relink to the Archive database or my live database? That will help to solve this confusion on my part.

2. I've checked the references and DAO is 3.5,( dont have 3.63) dont know if that makes a difference.

3. As your suggestion stated, I changed the code to include a specific table (tblCRFMaster), when I ran the code I got error message cannot find input table, query, 'tblCRFMaster'. When I debugged, tdf.RefreshLink was highlighted.

Both tables are spelled exactly alike so I'm kind of in the fog. I also placed a breakpoint as suggested the TDFname didnt display anything. I'll continue to research....

Thanks again.
Clark

 

[cavery]

FancyP, FYI, now TDFname is displaying 'tblCRFMaster' after I debug. But I'm still getting the error message.....seems to be getting closer to solving this.


Thanks,
Clark

 

[FancyPrairie]

If you don't have a reference set to &quot;Microsoft DAO 3.6 Object library&quot;, search you disk for the file &quot;Dao360.dll&quot;. It should be there.

What you're trying to do is to let the user toggle between live data and archived data. By doing it this way, you can use the same forms/reports/queries.

This is how I set mine up.

MyDatabase_App.mdb ... This database contains my forms, reports, queries, macros, and modules (Front-End) and links to the tables in the Back-End database.

MyDatabase_Tbl.mdb ... This database contains my LIVE tables only (Back-End)

MyDatabase_Archive.mdb ... This database contains only the tables that I need to archive. Note that MyDatabase_Tbl.mdb may contains 15 tables, but I only archive 3. So there are only 3 tables in this database. These tables are an exact clone of the tables that reside in MyDatabase_Tbl.mdb (even the table names are the same. If fact, this is a must).

Note that MyDatabase_Tbl.mdb and MyDatabase_Archive.mdb could have the same name, just located in a different folder. I prefer to name them differently, and keep them together in the same folder.


MyDatabase_Tbl MyDatabase_Archive
-------------- ------------------
Table1 Table1
Table2 Table2
Table3 Table3
Table4
Table5
...
Table15

Initially, all of the tables in MyDatabase_App.mdb (Front-End) are linked to MyDatabase_Tbl.mdb (Back-End). If the user selects &quot;View Archived Data&quot;, then you want to link Table1, Table2, and Table3 to MyDatabase_Archive.mdb. Now, when the user wants to view the Live data again, they select the button labeled &quot;View Live Data&quot;. Table1, Table2, and Table3 will be linked to MyDatabase_Tbl.mdb.

In the OnClick event of the button, do this:

Private Sub cmdViewArchive_Click()

Dim strDatabase As String

'*****************************************************
'* If the Caption of the command button says:
'* &quot;View Archive Data&quot; then, set Database name to
'* MyDatabase_Archive.mdb. Else set it to
'* MyDatabase_Live.mdb.
'*****************************************************

If (InStr(cmdViewArchive.Caption, &quot;Archive&quot) Then
strDatabase = &quot;path\MyDatabase_Archive.mdb&quot;
cmdViewArchive.Caption = &quot;View Live Data&quot;
Else
strDatabase = &quot;path\MyDatabase_Tbl.mdb&quot;
cmdViewArchive.Caption = &quot;View Archived Data&quot;
End If

Call LinkTable(strDatabase, &quot;Table1&quot;, &quot;Table2&quot;, &quot;Table3&quot

End Sub

Set a break point at the statement &quot;Call LinkTable...&quot; and step thru the code. The first time thru, assuming that you are initially linked to the live database, you should be relinking to the archived database. Therefore, strDatabase should contains the path and name of the archived database and the table names should be tables that reside in the archived database. Step thru the code to make sure the names are what you expect them to be. After the tables are relinked, goto Tools|Database Utilities...|Link Manager via the database window. You should be able to see that your tables are now linked to the Archived database. Now, via your form, select the button to relink to the live database. Your code will pause at the &quot;Call LinkTable...&quot; statement. Ensure that the variable strDatabase points to you Live database and the tables reside in the live database. Step thru your code to ensure the variables are what you expect them to be. You may find it more helpful to use Debug's Immediate window (In code view goto &quot;View|Immediate Window&quot. To examine the contents of a variable in the Immediate window, just type a question mark and the variable name and press enter (i.e. ?strDatabase).

 

[cavery]

FancyP, what I've found out is the code is running fine, (I stepped thru the code) but it stops on the second tdf.Refreshlink after the else statement

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

My guess is for some reason its not relinking???

When I look in Link Manager, there are no tables that are linked, just the original tables for the live data....but when I looked in my archve database, link manager, all of my tables were linked to my live database???
I'm still searching and investigating, but both of the tables are exactly alike......thanks for giving me feedback, I'm definetly learning new things with this code...

Clark

 

[FancyPrairie]

It sounds to me like you have 2 databases: Your live database that contains your tables, forms, queries, reports, modules, and macros. Then 2nd database is basically a clone and contains your archived data. If so, this is not the scenerio you want. You need 3 databases:

1. Front-End DB (contains forms, queries, reports, macros, and modules)
2. Back-end DB (contains live tables only)
3. Archive DB (contains archived tables only)

If your databases are NOT setup this way, make a copy of each of your databases before proceeding, in case things get screwed up.

If your main live databases contains your TABLES, forms, queries, reports, macros and modules, then you need to move your tables to a back-end database. To do this, use the Database splitter. From the database window goto TOOLS|DATABASE UTILITIES...|DATABASE SPLITTER. This tool will split your database into 2 databases, one containing your tables (back-end) and the other containing everything else (front-end).

Once that is completed, create a new database and import just the archived tables.

Your should now have 3 databases.

1. Front-End DB (contains forms, queries, reports, macros, and modules)
2. Back-End DB (contains live tables only)
3. Archived DB (contains only the archived tables)

Now modify our code so that it refers to the correct database names and execute it.

Good Luck!

 

[cavery]

FancyP, that was the problem, I didnt have a backend, just running everything from the front end. My database was setup as the following:
1.Front-end (tables, reports, query, forms, macros, etc.)
2.Archive database (tables I imported from my front end)
3. There were also several other databases that had other tables and names, dont know why....I think thats why this database has so many problems, with speed, corrupt tables etc.
But besides that I changed the code, to reflect the backend table and it works fine, no errors! Thanks a million you are a savior!!! there is one other issue, as far as when I did split the database, the tables were sent to the backend database. Should I delete the tables that are still in my front end database? they are still there, and I didnt know if they were deleted automatically, manually, or they arent deleted at all?? again, thanks alot, I'm on course to getting this database normalized! its a big mess.....

Clark
Honda of America.

 

[FancyPrairie]

No the tables should not be in your front-end. However, I don't believe they are. If there is a right-arrow to the left of the table name, that indicates that the tables are linked to the back-end (or some database). You can also goto Tools|Database Utilities|Link Table Manager to see which database the tables are linked to.

However, if indeed the tables are still in the front-end and are not linked to the back-end and exist in the back-end also, then you need to delete them from the front-end. Be sure to BACKUP your front-end database before deleting the tables, in case things aren't what you think.

 

[cavery]

You are correct the tables were linked to my new backend I just created, but some were linked to &quot;PD4 Archive&quot; and there was another database that was out there.....have any advice on how to get some order restored with all these seperate databases? again, it is a mess!...but you've been more than helpful thanks again....

Clark

 

[FancyPrairie]

Do all of these &quot;other&quot; databases contain forms, etc. or just the tables? Are other people using these &quot;other&quot; databases for input and reporting?

If not, then I would move all of my tables to one database (back-end) and link to them via the front-end. All of the forms, queries, reports, macros, and modules should be copied to one database (front-end). The archive database should only contain the tables used for archiving.

When you're done you should only have 3 databases:

1. Front-End ... Contains forms, queries, macros, modules and reports and links to the tables in the back-end.

2. Back-End ... Contains tables only

3. Archive DB ... Contains archived tables only (tables have the same name and structure as the tables in the back-end)

 

[cavery]

Yep the majority the &quot;other database&quot; has tables, some of which are the same, forms, report, they were created by someone years ago....dont know if they ever knew of a front-end or back-end. What is the supportive factor to have your database setup that way, in regards to Front-end and Back-end? Is it mainly for speed? less corrupt tables? Its hard to believe whomever created this, would not have considered using the database splitter and having seperate databases.


Clark

 

[FancyPrairie]

It's standard practice. It provides several advantages.

1. When user's are excuting code, traversing thru forms, etc., there is a chance of the database becoming corrupted. And, sometimes, can't be repaired. In an unsplit database, not only do you loose your forms, etc. but you also loose your data. In a split database, chances are, you will only lose your front-end not the data. The front-end can always be copied back to the user's machine.

2. In an unsplit database, how will the user's share the data? They could all run the same copy of the database, but not recommended.

3. Generally, the front-end is copied to the user's machine and the back-end resides on a server. The forms will display faster without having to go across the network.

4. In an unsplit database, it will be pretty difficult to implement changes to forms, code, etc. You will have to make the changes and test them on a test database, then copy just the changes you made (code, forms) to the live database (and hope you didn't miss something). In a split database, you make your changes in a test database and copy the fixed database to the user's machine.

There are many other advantages.

 

[chrissie1]

I think FP deserves a star just for the endless amount off patience he has.
actually he deserves a medal, but that is not up to me. Christiaan Baes
Belgium
&quot;What a wonderfull world&quot; - Louis armstrong

 

[FancyPrairie]

Thanks Chrissie1.