Linked DB performance

[vicsaccess]

i have several small DB's and i'm trying to centralize the information into a master DB that can do all the upper management equations in one location rather than having to open several. so far i have been successful but have hit a road block. i have linked all the tables to the central DB, used union queries to bring all the data together and then have my queries set to do all the differant equations that are needed. my road block that i have run into is our network is so slow that its locking up the DB. my original idea was to use the union queries and then a make table query from that so that i would only have to access the network one time to gather all the data and then use the made tables to run the queries but i ran into a problem of the make table queries have too many errors with the rewriting of the tables. does anyone have any idea's that might help?

[June7]

What kind of errors?

[vicsaccess]

actually June i don't remember, i just recreated the make table and it brought up alot of popups telling me i was going to write over records which is annoying considering it has to do 5 make tables but no errors. is there a way to turn off the popups on this action and can i then turn off the link to the tables somehow after the new table is made?

[June7]

Not if you just run query object from Navigation pane.

If you are using code, post it for analysis.

Table links can be deleted.

[vicsaccess]

what i have been trying is the tables are linked, when the operator opens the start page the "on load" event runs the make table queries. it works great other than the popups. how or where can i get code that will do this?

[vicsaccess]

is there a good program to help write code or a tutorial that is better than another. I need to learn, just needing a good fix for this one right now.

[June7]

VBA like:

1.
DoCmd.SetWarnings False
DoCmd.OpenQuery "query name"
DoCmd.RunSQL "UPDATE tablename SET fieldname=something"
DoCmd.SetWarnings True

2.
CurrentDb.Execute "query name"
CurrentDb.Execute "UPDATE tablename SET fieldname=something"

[vicsaccess]

Thanks June7, would it be better to have these databases linked to the central or have the individual DB's export daily or weekly? i noticed last night that all the DB's were showing open but all the employee's were logged out? i'm assuming this was because of the central DB being open and linked to them? wouldn't this give the operators a read only warning if someone was in the central DB first and an operator tried logging in? in all reality the central DB does not have to be a live version, only up todate within a day or so. i was looking around and saw referance to a run saved import/export command but could not find how to do it. that would in my thoughts be better, put it on a "on load" event to export to another location and then have the central DB pick it up "on load"? sorry some of this may sound good and some may sound off the wall, i'm learning as i go and need to start learning some code but not sure how much learning curve there is to it.

[NTC]

not sure exactly what is going on that would be involving union queries...... to consolidate several 'small' dbs - one must import the tables into a single common back end... if the tables have differing names and exist for differing purposes then it is quite straight forward. If there are tables common to more than 1 db that are static but duplicative - such as a table of country/state names obviously does not need to be brought in...

The challenge in consolidating databases is whether there are duplicate table names - or in general - whenever one must consolidate data into a single table. That is far more challenging because then one must determine which primary key is to survive - and how to deal with the existing foreign keys of linking tables. This is an advanced analysis & undertaking.

[June7]

I also am not sure what this structure is. Why are there satellite dbs linked into a 'central' db?

Open central db should not cause the satellites to show as open - at least that has been my observation with my dbs.

[vicsaccess]

sorry for the confusion guys. Again please forgive me i'm learning as I go at the same time using the knowledge of the experts here to keep me on track. each satellite DB is exactly the same other than I renamed the tables of the 4 tables that need to be joined in the central with their numerical location. example is DB location 31, the 4 tables are named 31shift, 31product, 31downtime and 31order. each of the 12 DB's are named that way. in the central DB all the tables are linked to the satellite DB's and then i'm using a union query in the central DB to join each of the 31shift to 41shift to 51 shift and so on. I handled the primary key as trimming the line number to the front of the existing primary key so that they come out looking like 311000, 311001, 4120000 and 513222. maybe this is a good path to go or maybe there is a better, trial and error is where i'm at. it works great thru the queries but the problem is the time involved to get the data and run the queries. that's where my thought came from if I could turn off the links then maybe I could get the data, make table and then off the link and run the queries from the made table. not sure what is the best way. then I got to looking around some more and saw some information on being able to run saved exports? would that be the better option, put it on a "onload" event to run a macro or such to export the data to another DB? i'm needing advice and direction here. the central DB actually will only need to be accessed once a week or so, not hourly or daily.

[June7]

I still don't understand why you have 12 dbs which essentially means 12 sets of 4 tables, all of which could have been 1 set in 1 db.

[ssanfu]

I, too, am confused.

This is really strange. I've never seen anyone do this. So, 48 linked tables. No wonder this is slow!
Constantly deleting, then creating (make table query) tables is a sure way to end up with a corrupted dB.
A better way is to create the table once. Then delete records and append new records.

Questions:
Why 12 identical dB's?
Are they split dB's?
How many users are in each of the dB's?

Is the Central dB split?
How many users?

I'm very curious.....

[vicsaccess]

please explain. at this point they are all operational across a shared network and i'm not sure if it could be changed but i'm willing to look at it. are you talking about a central DB and all operators using it at the same time? one more thing, our network is extremely slow and is an advantage for the individual operators and they don't have to wait as much on the network.

[June7]

A multi-user db should be split design. Data tables are in the 'backend' located on shared server. Everything else is in 'frontend' which is copied to each user's workstation - just as each user has their on install of Word or Excel, they have their own 'install' of db frontend. The frontend links to backend.

However, if you have a slow network impeding the current structure, it probably won't improve with a consolidated backend.