Opening Database 2 from Database 1 4

[suedemick]

I was wondering if I can expect any problems if I try to open a second Access database from a first database.

Database 1: split, back end on the server.

Database 2: not split, located on server. Some tables are links to Database 1, other tables are populated by importing information daily from text files downloaded from customer websites and our AS400 (we are not allowed to link directly to our AS400). The tables that are linked to Database 1 are not modified in Database 2, they merely provide information for the reports.

The boss would like a command button in Database 1 that will open Database 2. Since Database 2 is linked to tables in Database 1, I'm worried that I might be setting myself up for corruption in Database 1. I was wondering if I should close all add/edit forms and reports in Database 1 before opening Database 2, in case the user has an unsaved record in Database 1. After opening Database 2, should I close Database 1 entirely? Might I cause a problem having both databases open at the same time on the same machine?

Thanks for your thoughts.

 

[suedemick]

This is suedemick:

Have I offended the experts here at Tek-Tips? No one has responded to my questions in quite some time, and I'm wondering if I have been blackballed and just don't know it. I don't feel that I am a very knowledgeable Access developer(when I joined, I indicated I was a programmer only because it is my job to maintain our company's Access database, not because my skills equal those of the experts here), so I would never presume to try to provide answers to others' questions since the questions are almost always deeper than my knowledge extends. I always try to say thank you in advance and after responses have been given. I'm very sorry if I have violated the rules somehow. Could someone please tell me what I have done wrong?

Thank you,
Sue

 

[Terpsfan]

There are a couple of ways of doing this. You can use the Shell command to open the database...first example. Or you can use the opendatabase event for the workspace object to open the second database. However I wouldn't recommend the second method because if you already have a database open, it opens the database invisibly.

Private Sub cmdOpenDatabase_Click()

Const strDatabase2 As String = "Database 2 File path"
Const strAccessPath As String = "C:\Program Files\Microsoft Office\Office\Msaccess.exe"

Shell (AccessPath & " " & strDatabase2), vbMaximizedFocus

End Sub

 

[cmmrfrds]

You can select directly from the tables in another database by fully qualified name. This way you don't need to link if you don't want to.

select * from c:\atestdir\newtest.mdb.dbo_orders

You can also open a connection to the second database and again select with a fully qualified name that includes the connection name.

 

[suedemick]

Omega36,

Thank you, thank you for responding!!! So, you don't think I will cause myself any corruption problems by opening a second database which is linked to tables in the first database (which is still open)?

I'll give this a try, and thanks again,
Sue

 

[psemianonymous]

Hard questions get less answers than easy questions, and not all questions have *any* good answers. So to answer your second question, the only thing you did wrong was to ask a difficult question.

As an answer to your question: I don't know if opening the same backend via two separate front-ends will be more likely to cause corruption than multiple same front-ends. Probably not, though shutting one connection while simultaneously opening a second connection may be just weird enough to fling your files into a jumble (metaphorically).


With Access 97 you can't open a second database from a first database properly - you can use a tool from

http://www.trigeminal.com

called TSI SOON - shut one open 'nother. This will do your job, but it requires you register the DLL, something I was not willing to do.

You can do what I did, which was to use shell (see above) and DoCmd.Quit acQuitSaveNone. Possibly problematic, but good enough for me.


With Access2000+, use can use the command Application.CloseCurrentDatabase and OpenCurrentDatabase to do what you could not in 97.

 

[suedemick]

cmmfrds,

Thank you for your suggestion! This is a direction I hadn't considered. Maybe it would be safer to do it this way instead of linking. (I live in terror of corrupting database 1,even though I have the daily backup to fall back on)

Thanks,
Sue

 

[psemianonymous]

Oops, maybe it's Access2002+ where CloseCurrentDatabase / OpenCurrentDatabase works properly, I have nothing to verify this though.

(the problem is that when you close the database, all your code stops running, thus you never open the 2nd database after closing the original)

 

[Terpsfan]

What version of Access are you using Suede? What method you use depends really on what you want to do. If you just want access to the tables or queries in database 2, I would go with the linking suggestion by cmmrfrds. If you need to look at forms, reports, etc I would go with the Shell command. I would make sure that Database 2 is in Shared Mode. I don't think you'll run into database corruption if you open the second, perhaps some locking situations...

 

[suedemick]

Omega36,

I am using Access 2000. Database 1 has a front end with lots forms, queries and reports (58,000 KB). Database 2 is quite small (currently 4,000 KB, but under developement).

A little background as to why I'm concerned about corruption:
I've read that if an Access database becomes too large, it tends to corrupt more easily. I have had two nasty experiences with database corruption, both of which occurred after the database front end grew to more than 50,000 KB (even with daily compact and repair).

Database 2 is in shared mode, and I am doing daily development in a separate copy, so that they can use the database during the day. I WAS having Database 2 compact on close, but I've had the compact fail a few time (creating db1.mdb), so now I compact it manually. I'm concerned that the Database 2 has some hidden flaw which could bring both database crashing down if both are open.

Am I paranoid?

Thanks,
Suede

 

[ccburn]

Sue,

You need to open a form in design view -bring up the 'Event procedure' for one of your command buttons. -click debug -click compile dbname. This will compile your application and could possibly reduce the file size greatly.

I notice your front end is huge- you mentioned compacting but you did not mention compiling your code.

-ccburn

 

[suedemick]

ccburn,

Thanks for the suggestion; I compile my code if I make any changes to the code or to the underlying form/report query, or if I've added any objects to forms or reports that have code attached. When I'm done for the day, I compact my design copy of the front end two times, then copy it to the server so users can download a new version (hopefully) every morning.

I agree, the front end IS huge, but I think it comes by it honestly: with approx. 340 forms/subforms, 460 reports/subreports, and 840 queries. That's why I was reluctant to create all the new tables and reports for Database 2 directly in Database 1; it would have added approx. 4,000 more KB to the database. Actually, the front end it bigger than the back end!

I have periodically dumped the front end into a new clean database. That reduced the size somewhat, but it eventually grew back to 50,00 KB. Am I missing something I should be doing?

Thanks,
Suede

 

[cmmrfrds]

Here are a couple of threads that deals with multiple database queries and have examples.

thread705-567768
thread701-585222

 

[suedemick]

cmmrfrds,

Thanks for the links; I'll check them out!
Suedemick

 

[MichaelRed]

"Sounds Like" (from the game?) you have a lot of users. After all,

[tab]340 forms/subforms
[tab]460 reports/subreports
[tab]840 queries

SEEMS like a bit more than any single user system (or sngle user) will ever effectively use.

If this is the case, I would suggest at least an honest effort to identify the users by groups -who share the same 'interests'. To a large extent, it may be possible to then divide the db/app into user groups. Hopefully there would be significant groups of the forms & reports which are ONLY used by specific groups and the db/app FRONT END could be divided into the smaller groups. Of course, there will often be a couple of users who do not like being 'left out' of ANYTHING, but you can try to sell the concept that they are perfectly welcome to have access to all of the various Front Ends - but need to do so as independent entities - not a monolithic MONSTER. ONE selling point would be that hte smaller db/app front ends will improve performance on the individual basis.






MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[psemianonymous]

Sounds like to me a form is copied whenever you need to change the data source, and/or you are using different tables to store the same info. Thus the form/query/report bloating.


I can't honestly think of 800 discrete actions to perform, but I bet having 12 forms, one for each month, plus 4 forms, one for each quarter, plus one for the yearly, PLUS all those from last year...those can add up.


But maybe this application is huge. But it probably isn't.

 

[Samulayo]

Why don't you use a batch (*.bat) file to do the closing of db1 and open db2 and then just stick a button to run the bat file on the open db2 button. I use this method to allow the user to make backups.
off the top of my head i cannot remember the close command. All you need is to write it in notepad and save as a *.bat file and then in access use a RunApp command to run the file.
This way you dont have to worry about access closing and not running the second part of the code, or making db2 open and then close db1


<close command> &quot;C:\{path}\db1.mdb&quot;
&quot;C:\{path}\db2.mdb&quot;

all this does is close db1 and then open db2
If i remember the command to close a program ill post a follow-up.



Regards

Samulayo

 

[suedemick]

MichaelRed and foolio12,

Thank you for your thoughts and suggestions! Yes, this is more than a single user system; we have about twenty users. In the beginning, I strongly suggested that the front end database could be split into separate pieces, each representing one of the areas of focus, but the boss likes to think of it as &quot;one-stop-shopping.&quot; I've tried the performance card, but it didn't work.

We have five major areas of focus: Health/Environmental issues, Quality Control, Human Resources, Customer Quotations, and Supply Chain. Each of these areas have one to nine subtopics, such as infomation about our employees, customers, vendors, customer/internal complaints, maintenance on equipment, job training, etc. There is also an area under each topic that allows the users to add/edit information in the related look-up tables (the tables that support the combo boxes that are used in the add/edit forms and report criteria selection forms: date ranges, department names, one or all employees, one or all customers, complaint types, material categories, job classifications, etc.)

None of the forms are being copied and I have (for the most part) normalized all the data to third normal form (am I saying that correctly?). I have no form/query/report bloating. I do have LOTS of queries, and, if I ever get the time, I'd like to see if I could compare the criteria to see if I have any duplicates that could be eliminated. Many queries run the various combo boxes used throughout the program (no duplicates there), many of the reports share the same query, but often the criteria for various reports cannot be combined in the same query.

The boss would like to have Database2 be added as major section six (with minor section seven in the beginning stages of development), but, at least during development, I convinced him it was better to keep it separate. Now he wants it either merged into Database1 or at least accessable from Database1.

I was trying to avoid adding a new section to Database 1, because importing some of the data from the textfiles (downloaded from a website and from the AS400) is creating related error tables, usually containing one record, the last record in the textfile, which is the end of file symbol (?), a square. Every time we import the seven textfiles, three error tables are created, and I currently go in and manually delete them every so often. My next step is to figure out how to delete the tables in code. So far, I have not found any documentation about error tables, but I've not searched extensively. This has been the main reason I have not yet wanted to import all the new tables, forms, queries and reports from database2 into database1. What happens with all the error tables, in case they do not get deleted, when I synchronize my design master and replica?

Thanks for any thoughts you may have,
Sue

 

[suedemick]

Samulayo,

Using a batch file sounds like a great suggestion! One concern I have with abruptly quitting database1 is, what if the user has a table open (in one of the add/edit forms) with a record unsaved? Should I somehow, before running the batch file, loop through all the forms that are linked to tables or display combo boxes linked to tables, save any unsaved records and gracefully close all forms, reports, and queries, then close the database1 and then open database2? Of course, there is always the potential that the user has failed to complete required information in an unsaved record, but maybe the error handling to catch that.

Thanks for the idea,
Sue

 

[Samulayo]

Sue, the use of a batch file to close the database would result in the same situation as if someone had just pressed the [x] close access window button. Any error messages _should_ be displayed before the window closes. Any unsaved records would (unless you use an update query or similar) just auto-save with the data currently in them, but then again, why would the user open db2 without finishing the job on db1?
You could always have a save confirmation msgbox on form_close.


Regards

Samulayo