Is Linking tables the cause of my slow database? 3

[ruthcali]

I am using Access97 in a Novell environment.

I created a mde database called Projects.mde that resides on our network and all the users access it from there. (the database is NOT a front end_back end db)

Then i created a different database called Manager.mdb on a Windows NT server. The Manager database is only for managers, but the employees need to see some data for their use, so i went into the Projects.mde database and created a link to about 5 tables in the Managers database.

Users of the Projects database are now complaining that the whole database is VERY slow now.

Will just having the linked tables cause the whole database to be slow even if the users don't open the form that use the linked tables?

For example, all users for the Projects database use a form called Day1 whose recordsource is a local table. But, at the bottom of Day1, there are two combo boxes whose recordsources come from queries that come from the linked tables. There is also a command button on Day1 that opens a form whose recordsource is based on the linked tables and displays data based on the values of the two combo boxes.

Will just opening the Day1 form cause the database to slow down even if the user doesn't touch the combo box or the command button?

if so, how can i speed up the database. now it is taking about 2 minutes for a form to open!

yikes!

thanks,
Ruth

 

[ToeShot]

Yes the link tables are probally causing your problems of speed and everytime you access the projects database it will access your managers database because it will always connect the tables first. But There are also other problems
1) You have your Projects database residing somewhere on your network and point everyone to it. This also cause slow network and database speed because all the work is done on the pc holding the database. This becomes worse if the pc is a sever because it slows network traffic.
2) Also since you are pointing everyone there you are in actuallity opening up multiple instances of the database which will futher slow your database down along with some other problems.
3) With every multiple instance open you are creating another virtual connection to the linked table
4) If the Projects.mdb and the managers.mdb are on different severs this will cause some delay but not to an extreme unless one of those servers have a heavy amount of traffic.

My suggestion would be to split your Projects.mdb to a Front-End Back-End database enviroment with the Front-End application containg all forms, reports, queries, macros etc.. on all the users pc and the Back-End has just the table. This will allow all the users to do the work on the front-end and send a request for the data to the Back-end thus cutting down on network traffic and the work that the back-end has to do. Now your Front-End will be linke to the tables in the Back-End but your Back-End never has to be shut down(closed)saying that it has links to the Managers.mdb that are always connected. Also with front-end back-end enviorment you only have on instance of the data tables open keeping your database more stable and normal. This should allow everything to open faster. But note it may not depending on the Location of the managers.mdb compared to the Projects.mdb. you may want to your Network Administrator and see if you can set up a location for all your database apps to reside. Remember This is just a suggestion and may not fit your situation so investigate before implememnting. Good Luck

HTH

 

[ruthcali]

ToeShot,

thanks for your words of wisdom. but here is my main question with the front end/back end scenario:

i am constantly changing the forms,queries, etc. of the database. (ex: Can you add a button that does...).

If all the users have a copy of the database on their C drive, how can i make sure everyone has the updated copy of the database after i make a change?

Will i constantly have to bug the users and send them emails saying, 'sorry, but can you open the attached database and replace it with your existing one'.

Thanks,

 

[ToeShot]

Use database replication. Check the Help files and some search on the web. But from what I remeber you do all the work on the Master copy of the front end and with a click of the button it updates all front ends. That is my inderstanding of it. I have not played with it enough as of yet. I also have experienced bad things with trying to replicate back end tables and suggest avoiding that.

 

[UncleJack]

Yes, replication is the way to go. And it's absolutely essential that you split the DB into front/back or it will operate at glacial speed for the reasons cited above. Here's another tip that will speed access to the Manager.mdb database. Put an empty table in the Manager.mdb database and then open a DAO link from the workstations when they start up. Do not close the link to the Manager Empty table. This opens a communication channel to Manager and keeps it open so that it doesn't have to be opened each time you need data from the database.

Uncle Jack

 

[RSGB]

Hi

I solved this by having users open a dummy database on the network that in reality merely copies the latest mde front end file I've made onto their c: drive and then runs that.

This allows me to work on the master MDB file and whenever I want I can create an MDE file of it which will be used by the users next time they go into the database...

 

[ruthcali]

Toeshot,
thanks, i'll check into replication.

UncleJack,
About your advice:
"..open a DAO link from the workstations when they start up. Do not close the link to the Manager Empty table"

I have a form that opens when the Projects database opens called frmName and that form stays open until the database closes. Are you saying that for the frmName On_Load event, i should type something like:

dim db as database
dim rec as recordset

set db=Managers 'do i have to type the full path of this database?
set rec=db.openrecordset ("select * from tblEmpty&quot

RSGB: How in the world do you make the computer automatically copy a database onto your user's C: drive??? Does it copy it all the time, or only when you change the front end?

Thanks everyone!!

 

[UncleJack]

No, I'm not saying that. Here's the code to use to open a channel to an empty back end table named Empty that is linked to the master database. Put this in a module and call it from your Autoexec macro using a RunCode command:

Private Sub SpeedAccess()
'Establish link to backend database to speed data access
On error resume next
Set DB = CurrentDb
Dim rstEmpty As Recordset
Set rstEmpty = DB.OpenRecordset("Empty&quot
End Sub

This will make sure there's a communication link to the back end at all times the front end is open without having to worry about forms, etc.

Uncle Jack

 

[RSGB]

Hi Ruthcali,

I've put the code for the dummy database in this thread:

thread181-52728

In answer to your question, it copies it over every time, not just when there's a more recent version. This was quicker to do than add in a check whether there is a local version already and then some file comparison code but I agree this would be a good idea.

Laterzzz...

 

[JimHorton]

ruthcalli,
Another thing to look at regarding the slowness issue. If the NT service packs on machines where the client and the network .mdb resides are different, this will cause extreme slowness, there was a thread here regarding that with a link to the MSKB article verifiying that it is a known problem. The fix was just to make the service packs equal.
I've not used replication before, but it was my understanding that is was for data only, not form, report, etc. objects. I have a working solution to the issue of updating users with front-end .mdbs on their local machine, if you're interested, let me know. But essentially, the pseudo code is that the front end opens a bit of code upon startup that scans the msysobjects table of the 'master' copy of the front-end .mdb which is placed on the network as changes are made. Any object newer than the local copy is deleted and replaced, except for tables, unless they're local static tables. The only thing, from a practical sense, that can't be copied is the object in use, which is the module executing the code. The checking process takes, for all practical purposes, no time. If updates are required, depending on the size and number of objects, it could take a minute or so. There's more to it than that but that's the gist of it.
--Jim

 

[ruthcali]

Uncle Jack,
thanks for the code. (i had to change it into a Function in order to run it using the runcode macro).

I tested the database with and without the function and the speed seemed about the same. but i was testing a copy of the database that no other users were using at the moment. i guess the more users that are in the database-the slower it gets, right?

RSGB,
thanks for the link. i'll try it out.

Jim,
thanks for the advice about the service pack, but all users here use Novell. Only the managers.mdb database is on Windows NT. We access the NT server using Network Neighborhood.

"I have a working solution to the issue of updating users with front-end .mdbs on their local machine, if you're interested, let me know."
yes yes yes yes yes!! i'm interested

Thanks,
ruth

 

[JimHorton]

ruth,
OK, here's alot of code...I recommend pasting into Word and then doing a search/replace on the chunks of spaces that you get when you copy a post. I've gutted alot of the code, and only left the main gist of the code, for clarity. I had significant error handling, event logging, and security issues that I dealt with, but the unedited block of code was just to big to put here. Anyway, here goes, let me know if you have any questions...

Function upd_Objects()
'This code is in it's own module--this module CANT be replaced in with this procedure!
On Error GoTo errUPD
Dim varX As Variant, EMPID As String, lRet As Long, rst As Recordset, strOname As String, lType As Long
Dim strDbRemPath As String, db As Database
'below table tblSystemInfo is linked table existing in the main data.mdb, we fetch the location of the master.mdb from it.
varX = DLookup("MasterMdbLocation", "tblSystemInfo&quot
If IsNull(varX) Then
MsgBox "Error connecting to attached database"
Exit Function
End If
strDbRemPath = varX
Set db = CurrentDb 'You'll want to open a workspace with the secured user instead of the simple currentdb.


'Additional issues, not in this block of code, which has been significantly gutted:
'1. Security. You need to open a workspace with the superuser id/pwd in order to import secured objects.
'2. Error handling. I'm just showing a message here, but you'll want to set a flag when you're at the TransferDb section,
'so that on any transfer error you can un-rename the 'zz' object.

'Start by opening recordset of the newer objects. The query is a LEFT JOIN--so new objects get imported--
'of the linked master MsysObjects to the Local one, with criteria being dateupdate > local.dateupdate
'Or local.name is null (from left join, this enables import of new objects).

'Important to note that the Master mdb is *not* the development mdb. it's just a copy that sits out there and
'no one touches, and when an update is called for it's exported to the master from the development .mdb
'In actuality, the master need not have all the objects in it--it can have only the newest ones.
Set rst = db.OpenRecordset("qryUpdatable_Objects&quot
If rst.EOF And rst.BOF Then 'then no updates, no big deal, just exit quietly
Exit Function
End If
'Now start looping (optionally let users know what's going on)
'Forms!frmSplash.txtStatus = "Please wait while program objects are updated..."
DoEvents
Do Until rst.EOF
strOname = rst!Name 'Name & Type are the only fields from MsysObjects in the query
lType = rst!Type
'The basic steps:
'1. Rename the object, in case of catastrophic error, you can un-rename it
'2. Import the new object
'3. If the import had no errors, delete the renamed original
Select Case lType
Case Is = -32768 'forms
DoCmd.Rename "zz" & strOname, acForm, strOname
DoCmd.TransferDatabase acImport, "Microsoft Access", strDbRemPath, acForm, strOname, strOname
DoCmd.DeleteObject acForm, "zz" & strOname
Case Is = -32766 'scripts/macros
DoCmd.Rename "zz" & strOname, acReport, strOname
DoCmd.TransferDatabase acImport, "Microsoft Access", strDbRemPath, acMacro, strOname, strOname
DoCmd.DeleteObject acMacro, "zz" & strOname
Case Is = -32761 'modules
'2 modules to avoid: this one, and the one containing all global variables
If strOname <> &quot;modUpdObj&quot; And strOname <> &quot;modGlobalVariables&quot; Then
DoCmd.DeleteObject acModule, strOname
DoCmd.TransferDatabase acImport, &quot;Microsoft Access&quot;, strDbRemPath, acModule, strOname, strOname
End If
Case Is = -32764 'reports
DoCmd.Rename &quot;zz&quot; & strOname, acReport, strOname
DoCmd.TransferDatabase acImport, &quot;Microsoft Access&quot;, strDbRemPath, acReport, strOname, strOname
DoCmd.DeleteObject acReport, &quot;zz&quot; & strOname
Case Is = 5 'query
If Left$(strOname, 1) <> &quot;~&quot; And strOname <> &quot;qryUpdatable_Objects&quot; Then 'Embedded queries from forms, comboboxes are to be avoided
DoCmd.Rename &quot;zz&quot; & strOname, acQuery, strOname
DoCmd.TransferDatabase acImport, &quot;Microsoft Access&quot;, strDbRemPath, acQuery, strOname, strOname
DoCmd.DeleteObject acQuery, &quot;zz&quot; & strOname
End If
Case Is = 1 'local table
If Left$(strOname, 4) <> &quot;Msys&quot; Then 'avoid system tables
DoCmd.Rename &quot;zz&quot; & strOname, acTable, strOname
DoCmd.TransferDatabase acImport, &quot;Microsoft Access&quot;, strDbRemPath, acTable, strOname, strOname
DoCmd.DeleteObject acTable, &quot;zz&quot; & strOname
End If
Case Is = 4 'isam attached
If UCase(Left$(strOname, 2)) = &quot;ZZ&quot; Then
DoCmd.Rename &quot;zz&quot; & strOname, acTable, strOname
DoCmd.TransferDatabase acImport, &quot;Microsoft Access&quot;, strDbRemPath, acTable, strOname, strOname
DoCmd.DeleteObject acTable, &quot;zz&quot; & strOname
End If
Case Is = 6 'odbc attached HERE THERE MAY BE SOME USER ID/PWD ISSUES DEPENDING ON ODBC OPTIONS & BACKEND SECURITY
DoCmd.Rename &quot;zz&quot; & strOname, acTable, strOname
DoCmd.TransferDatabase acImport, &quot;Microsoft Access&quot;, strDbRemPath, acTable, strOname, strOname
DoCmd.DeleteObject acTable, &quot;zz&quot; & strOname
Case Else

End Select
rst.MoveNext
Loop

exUPD:
Exit Function

errUPD:
'add logic to the error trapping to deal with re-un-re-nameing, etc.
MsgBox Err.Number & &quot; &quot; & Err.Description
Resume exUPD

End Function

 

[ToeShot]

Jim
I have been looking for a way to complete a specific task on a project I am working on. Your post has given me the starting blocks. Great Job on the post.

Dave )

 

[ruthcali]

Wow jim,

i'm impressed. i can't wait to try the code! I'll let you know how it goes.

 

[ruthcali]

Jim,
i'm going on vacation so i haven't successfully completed playing with the code.

Please forgive me if i don't respond until next year. (if i can find a computer while i'm on vacation, i'll play with it then).

HAPPY NEW YEAR!!

 

[JimHorton]

Ruth,
I'm out of town for a week or two also...have a great vacation!
--Jim