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]

There are a couple ways to do it, but I think this is the easiest. In the database that contains form2, create a module (i.e. OpenForm2) that opens form2. Then, in the first database, create a Reference to the database that contains form2. In the first database, simply call OpenForm2. That should do it.

Function OpenForm2()

Docmd.OpenForm "form2"

End Function

 

[cavery]

Can I place that code in a command button?....(in form 1). something like cmdArchiveRecords?
You are aware that Form 2 is in a seperate database, I thought I would have to add the name of that database in code?

Thanks,
Clark

 

[JeremyNYC]

FancyPrairie's way looks like it should work just fine.

One other possibility, though. If the forms are the same and you just want the ability to look at different data, you could simply link to the other database, open another copy of Form1, and change the recordset on that form to point to the linked data instead of the local data.

Also, taking one more step backwards from this, why is the archived data in a separate database? Why not in the same table, with an Archive field flagging it as old? Certainly there are legitimate reasons for this, I would just want to make sure that you actually need to do this, rather than you just put it somewhere else because it's different data.

Jeremy =============
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[FancyPrairie]

When you issue the DoCmd.OpenForm method, it will try to open the form in the current database. That's why I suggested Referencing the second database. By creating a function in the second database (as I suggested in the previous post), when you call that function (via the first database), it will open form2.

To Reference your 2nd database, while in Code View, select TOOLS|REFERENCES... then browse and select your 2nd database. (Remember to create the module OpenForm2 in the 2nd database.) Once the Reference to the 2nd database is created, you can call the function OpenForm2 via the OnClick event of cmdArchiveRecords and Form2 will be opened.

It looks like you are archiving data. Not sure how you are doing it, but this is how I do it. I have 3 databases: Front-End, Back-End (contains "Live" data), and ArchiveDatabase. Assume that form1 allows the user to edit/view the "live" data that is linked to the Back-End. If the user wants to view the Archived data, in the OnClick event of cmdArchiveRecords, I relink the appropriate tables to the ArchiveDatabase (tables on both Back-End and ArchiveDatabase have the same name) and make visible a label on form1 that indicates that the user is viewing the archived data. I also change the caption of the command button cmdArchiveRecords to say something like "View Live Data" and also set the form so that the user can't change the data (since it is archived). Consequently, you can use the same form to edit/view both the live and archived data.

Basically, you are toggling between the live and archived data simply by relinking to the appropriate database. Note that in the OnClose event of the form, I always make sure that I'm linked to the Live database (Back-end) before closing so I don't have to worry about other forms looking at the wrong set of data.

Also note that your reports can do the same thing. One report can print either Live or Archived data, simply by relinking.

 

[cavery]

Jeremy-currently I have around 35,000 records, I want to archive alot of those records because of lack of use. Figured the best to achieve this would be to archive them. Didnt know of any other way but to put them in another database.....(thanks for the feedback)

FancyP-The concept you're using is exactly like the one I envisioned, just takes me longer becauses I'm not as experienced, still learning. In trying to grasp your theory, I placed the necessary code, but still having minor problems, mainly with form1(live data) and my cmd button and calling the function to open form2(archive data).

This is what I have for my module in form2:
Function funGetfrmProbUpdateARCHIVE()
DoCmd.OpenForm "frmProbUpdateARCHIVE"
End Function

Still getting an error when I click the Archive Records button, saying the name is misspelled wrong, etc.

I'll try to figure it out in the meantime, thanks for help.

 

[tmaloney]

Referencing other databases works well if the database always exists. I have found in developing that there are times I want to distribute a database without something I have referenced and it breaks it. (usually a function like chr() or left$()) If the other database will always be present a reference would work just fine.

The other option would be to programmatically link data from the 'other' database as it is needed and as mentioned, change the recordsource of the form.

There is yet another option. Using VBA, you could create another instance of Access and tell that other instance of Access to open the requested form. (I would test for the database to make sure it exists prior to this however.) The database will load fairly quick because you already have an instance running.

Tim

http://www.allianceconsultingllc.com

 

[FancyPrairie]

You need to create a Reference to your 2nd database by selecting TOOLS|REFERENCES... That way your 1st database knows where the function funGetfrmProbUpdateARCHIVE can be found. Without the reference, it won't work.

However, I would strongly recommend using the method of relinking that I suggested in my previous post. That way you don't have to worry about maintaining another form (or 2). And the user can view and report on the Archived data the same way they view/report on the Live Data. Simply place a button on the form the says "View Archive Data". In the OnClick event call the function below. For Example,

Private Sub cmdViewArchive_Click()

Dim strDatabase As String

If (InStr(cmdViewArchive, "Archive&quot) Then
strDatabase = "NameOfArchiveDatabase"
cmdViewArchive.Caption = "View Live Data"
Else
strDatabase = "NameOfLiveDatabase"
cmdViewArchive.Caption = "View Archived Data"
End If

Call LinkTable(strDatabase, "Table1", "Table2&quot

End Sub


'+********************************************************************************************
'*
'$ Function: LinkTable
'*
'* Author: FancyPrairie
'*
'* Date: December, 2000
'*
'* Purpose: This rouine will link the table(s) specified to the database specified.
'*
'* Arguments: strLinkToDBname ... name of database (path\.mdb) where tables reside
'* varTblName ........ List of table(s) that need to be linked to strLinkToDBName
'* Note that if the varTblName = "All", then all of the
'* tables in the current database will be linked to the
'* database specified.
'*
'-********************************************************************************************
'
Function LinkTable(strLinkToDBname As String, _
ParamArray varTblName() As Variant)

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

Dim dbs As Database
Dim tdf As TableDef

Dim i As Integer

Set dbs = CurrentDb

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

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

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

ExitProcedure:

Exit Function

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

ErrHandler:

MsgBox Err.Description, vbExclamation

Resume ExitProcedure


End Function

 

[FancyPrairie]

Forgot to mention, the names of the tables that reside in the archived database must have the same name and structure of the the live tables.

This method works great because you don't have to maintain separate forms for live and archived, and you don't have to bother changing the Recordsource. Changing the Recordsource can become a problem, especially if you have several subforms and queries to deal with. Also, suppose you want to report on the Archive data. You would not only have to change the Recordsource of the Report Criteria form, but also the reports themselves and change the queries that they use (and RecordSources for charts). Could become a real hastle to maintain. By relinking via code solves all of the problems. Just remember to make sure that you have relinked back to the live database before closing your forms. Otherwise all of your forms (i.e. maintenance forms, lookup forms, etc) are going to have check to see if they are linked to the live or archived database. It's easier if they assume they are linked to the live database.

One final thing. Make sure you let the user know that they are view/reporting archived data. To do this, simply toggle a label's visibility on/off.

 

[JeremyNYC]

A couple of comments on your method, FP. I definitely like it a lot. But two things concern me:

=I would still want code to check what I'm linked to upon startup, as it's impossible to eliminate the possibility of a messy closedown, such as the kicking out of a power cord.

=I would think that this would be used only for massive data sets. It may be that that's how you use it, but Clark is talking about an extra 35,000 records, and I would think that for something like that, it would still be more efficient to add a field to indicate archive status and use that in the criteria of the recordsource of forms and reports. Keeping the data all together like this would, among other advantages, make it possible to look at both sets of data at once.

Still, though, I like your method a lot, and will remember it for times when I use datasets that require separate archives. Thanks for the illustration.

Jeremy =============
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[FancyPrairie]

JeremyNYC is correct. I failed to mention that about checking to see what you are lined to at startup. Actually, I check to see if I'm linked to the Live, Test, or Archived database. If it's the Archived database, I relink to Live. If the user is a member of the development group, then all of my forms display the word Live or Test so I don't accidently change records in a live database.

 

[cavery]

FancyP, due to time restraints, I want to continue to try and use the first option you gave, before I explore the other option..... (plus I'm not that experienced with all the terms, etc.)I was able to reference my Archive database(frm2) in my Current Database(frm 1). So thats ok, but my problem resides in my cmd button and the module in my archive database.

I'm still in the fog regarding calling the function you mentioned above.

This is the code I have for my Archive command button:
Private Sub cmdArchive_Click()
Call frmProbUpdateARCHIVE
End Sub

You mentioned using:
Function OpenfrmProbUpdateARCHIVE()
DoCmd.OpenForm "frmProbUpdateARCHIVE"
End Function

But when I use that I still get an error, I'm having a hard time codeing that in my Archive command buttown. Maybe I'm not using the correct methodology???

FYI, my module(frm2) has this code:

Public Function frmProbUpdateARCHIVE()
End Function

Can you steer me in the right direction please??....I would appreciate it. Starts to both you and Jeremy, been very helpful as usual....

Thanks,
Clark
Honda of America MFG.

 

[FancyPrairie]

The following code should reside in your 2nd database:

Function OpenfrmProbUpdateARCHIVE()
DoCmd.OpenForm "frmProbUpdateARCHIVE"
End Function

In the OnClick event of the Archive command button of the 1st database, include the following (assuming you have referenced you 2nd database and the name of your command button is "cmdArchive&quot.

Sub cmdArchive_Click()

Call OpenfrmProbUpdateARCHIVE

End Sub

Now, when you click the Archive command button, the form frmProbUpdateARCHIVE (that resides in the 2nd database) will be opened.

 

[cavery]

FancyP,
Thanks, everything is in place and looks good, small problem, whenever I do click on the Archive records command button, I get the error messsage...3078, cannot find table or query 'qryProbUdate'. When I click debug, it goes to my module and highlights: DoCmd.OpenForm "frmProbUpdateARCHIVE"

The qryProbUpdate is the recordsource for form 1. I also use that same query in frm 2, but call it qryProbUpdateARCHIVE. Dont know if thats where the problem resides, but I'm still double checking everything, just wanted to get back to you and get your opinion.

Thanks again.

 

[FancyPrairie]

I don't know. The query form2's RecordSource property is referring to would have to reside in database2. Note that database1 and database2 could have queries with the same name. If a form is opened in database1 then it will be looking for a query in database1. Likewise for for a form opened in database2. It will be looking for a query in database2.

 

[cavery]

Fancy P,
I think I know the problem is, my current database, (which has form1) has all of the necessary tables, queries, reports etc. and it works perfectly fine. I created a seperate datbase, called Archive Database (which has form2)which has a duplicate copy of all the tables, queries, reports, tables, etc that the current database has. Basically everything is the same, but has a different name...such as qryProbUpdate in my current database is qryProbUpdateARCHIVE in my archived database.

Since I got the error that the query was missing, I added qryProbUpdateARCHIVE to my current database and it worked fine. (well all I have to do is add all the other data etc The purpose of creating the Archive database is to store old data and to eliminate unnecessary code in the current database. But if I have to add all of the ARCHIVE tables, queries,etc isnt that defeating the purpose?

Or maybe I can add all of the necessary data in my current database(tables, records, etc.)except the records that I want archived? Dont know if I can do that.....hope I didnt confuse you, if I need I will explain in better dialect....

Thanks
Clark

 

[FancyPrairie]

You're creating an incredible headache for yourself. It sounds like you're cloning a database and then changing the names of objects (queries, etc) in the cloned database. Suppose you get all of the names changed in your second database (which really doesn't need to be done) and then need to make minor or major modifications to the first database. Are you going to have to reclone the first database again and rename everything in the second database again?

I think the simplest way to do it is as I suggested earlier, simply relink to the archived database when you want to reference it.

Let's say you have data in 3 tables that need to be archived; Table1, Table2, and Table3. And, let's say you have 3 databases: FrontEnd.mdb, BackEnd.mdb, and Archive.mdb. BackEnd.mdb contains the 3 tables. Archive.mdb also contains 3 tables with the same names and structure. When you want to refer to both sets of tables in the same SQL statement (query), just use the IN clause of the SQL statement.

[SELECT | INSERT] INTO destination IN
{path | ["path" "type"] | ["" [type; DATABASE = path]]}
FROM tableexpression IN
{path | ["path" "type"] | ["" [type; DATABASE = path]]}


Even though your tables have the same name, you can reference them individually by database name.

What this buys you is that now the forms, queries, reports etc. can toggle between the Live data (BackEnd database) and the Archived data (Archive Database). Therefore, if you make a change to a form or report, you don't have to worry about makeing the same changes in another database.

It will take you a lot less work to implement this method, then the way you are currently proceeding. All you need to do is have a command button that calls the function in my earlier post and pass it the name of the database you want to link to (Live or arhive) and optionally the names of the tables (separated by commas). Note that if you don't specify the names of the tables, all of the tables will be relinked. Also, at startup, just call this routine to link to the live database. (As I stated earlier, I check to see which database I'm linked to prior to relinking. If I'm already linked to the live database at startup, I don't bother relinking at startup.)

Another thing to consider:

To archive the data you must copy data from TableLive1 to TableArchive1, TableLive2 to TableArchive2, and TableLive3 to TableArchive3. Once copied, you then need to delete it from the Live tables. (Concern: Suppose right in the middle of archiving, the system crashes or something. Now you have the same data in 2 places (Live and Archive) because it didn't have a change to delete the data from Live after it was archived. Therefore, to prevent this from happening, you should make sure you're using Transaction Processing so the you can RollBack transactions if they fail.)

 

[cavery]

FancyP,
Thanks for the feedback, you are correct this method would be easier to maintain, and is the way I envisioned my archive database to work. In your archived database, do you only have the same tables as the live data? or do you include all the necessary queries, reports, forms etc? sounds like those arent necessary for this method, which would save alot of time.

I copied all of the code, from your earlier post from Jan.24th, and placed it in the "On Click event. My concern are in parenthesis:

strDatabase = "PD42000" (this is the name of my "live database, should I include the .mdb?)
cmdViewArchive.Caption = "View Archived Data"
End If

Call LinkTable(strDatabase, "Table1", "Table2&quot
(Also I want to link to all the tables for now, do I simply state Call LinkTable?


Last concern is the following:
If (varTblName(0) = "All&quot Then
For Each tdf In dbs.TableDefs
tdf.Connect = ";DATABASE=" & strLinkToDBname

(You mentioned to add the name of the database where tables reside. My database resides on my desktop, so strLinkToDBname would be strLinkDesktop/PD4 Archive.mdb? for some reason it isnt recognized as being valid)


Thanks again,...
Clark

 

[FancyPrairie]

1. Yes, the only thing you need in your Archive database is the tables that contain the archived data.
2. When you call the routine to relink the tables, you need to provide the full name of the database, including path (i.e. "path\LiveDatabase.mdb" or "path\ArchiveDatabase.mdb&quot
3. Here's how you call the function to relink all of the tables to the live database:
Call LinkTable("YourPath\PD42000.mdb","All&quot
4. Not sure what's going on with this, unless this is how you are passing the string to LinkTable. Assume that your database resides in "c:\My Documents", then the call to LinkTable would look like this:
Call LinkTable("c:\My Documents\PD4 Archive.mdb","All&quot

Remember, be sure to toggle a label's caption from Archive to Live so that you know which database you're working with when you are in a form or report.

This method also works great for a test database. You could have a button that relinks to a test database rather than an archived database.

Note: Looking over the LinkTable function again, it could be modified so that all you would have to do is pass the name of the database and not have to pass the "All" argument. But if you get this working, we can deal with that later if you want.

If you are having problems, then use debug to step thru your code to see what's going on. To use debug, type the word Stop right before the line of code you want to examine or place the cursor on the line and press F9. F9 sets a breakpoint (toggles on/off). Now execute your code. When the program excecutes the stop statement or encounters the breakpoint line, the code will pause. To examine a variable, place the cursor over the variable (works like a tooltip) or in Debug's Immediate Window type ?variableName. To continue execution, presss F8 to step thru your code 1 line at a time. Press F5 to continue execution until the next stop statment, breakpoint, or to the code exits.

Let me know how all of this works.

 

[cavery]

FancyP, I made the necessary changes, looks good but I get an error on the second line of code. I did debug the code several times to no avail, the error states the expression has no value: If (InStr(cmdViewArchive, "Archive&quot) Then...

This is the line of code from your earlier post, believe the error are coming from "Archive"? should something else go there?...I placed my code for my cmd button below, this is based off of your previous code:


Private Sub cmdViewArchive_Click()

Dim strDatabase As String

If (InStr(cmdViewArchive, "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("M:\Data\PD4Master.mdb", "All&quot

End Sub



So far thats the only error I receive, I'll continue to correct and test in the mean time....
Thanks again....
Clark