LINKED TABLE PERFORMANCE DESIGN QUESTION 2

[shelron]

Okay, ere it goes,

I have 3 Access XP database applications that serve a section of the company. They all contain and have information about and from, the companies main database which is a Sysbase product.

I have set my applications with normalized tables, unfortunately, the main database does not.

So what I do is periodicly take dumps of data through a report writing program, dump it to excel, weed through it, correct it, (get rid of already existing records that are in my application), etc., then import the data into a temporary table in my app, then write an append query to get the desired additional records into the correct table.

Whew,, makes me tired just writing it, never mind doing it. Hence the issue:

The plan is this, Our main application guys are going to create shadow tables updated nightly that I can access. (I do not have rights to the main application and I aint gettin em anytime soon) They will essentially update these tables every night through a procedure. The shadow tables will be in access xp.

The second part is where I can now link to these tables from my application to obtain relatively up-to- date data without having to go through the nonsense described above.

Now since their tables are NOT normalized, I am going to need to do some manipulation to make the data line up with my tables.

Here are some of my thoughts on implementing this:

1 way would be to create tables that match mine, via a query of the master, (pulling aliases etc.) and then link my individual application tables to these record sets.

question -- are their negative performance issues with this method? (nt2000)(my applications are split, with the front ends residing on the users local drive.)

question -- how do you start creating a procedure that runs every night to update the tables. (access to access)

2nd way would be to use their tables (the shadow ones) linked directly into my application, this would require a complete rewrite, and I'm afraid that I will lose my referential integrity with cascade update and delete controls.


couple of business rules:

1) I won't be getting access to the sybase tables other than possible a view.

2) This shadow method is desirable for three reasons, current data, less maintenance on my part, and less maintenance on ITprogrammers part because if I have most of the info, I can edit without request to them.

I would greatly appreciate positive critisim of this approach and will welcome an alternate solution that meets the rules.

Ron

 

[Darrylles]

Hiya,

Q1 - data on the server, frontend on user.
No performance issues, but when you make a change to the front-end then all clients must be updated (not an happy task).
Certain forms (rare, intermittent use would be better on the server), so that you can make a change and all clients ses the change.

Q2 - I think that MS Scheduler would be a good bet.
Create a small seperate Access application which simply runs the update query(ies) in your main Access application when it is started by MS Scheduler.

Probable better solutions will follow from others.

Regards,

Darrylle




"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[JeremyNYC]

Q1-As far as rolling out new front ends, it needs not be painful at all. I do it in about a minute's time. Clearly, though, I cheat by putting in some effort when I set up the system at each client's.

I use batch files to accomplish it. When I want to bring a new user into the fold, that user gets (in addition to an account belonging to the proper groups) a batch file. The batch file creates directories on your PC (if needed), copies over the newest version of the front end, if you don't already have it, registers necessary DLLs, if not already registered, and fires up the database, using the proper MDW.

If you're interested, check out the Deploying Databases article on my website.

Q2-Depending on the type of usage your database gets, you could do this one of several ways. The scheduler should work, though I would make sure that it checks to see if anyone else is in the database. Another way would be to run it as the last person in the databas logs off, if it hasn't been run that day (parameters could be tweaked to make sure this happens at reasonable times of the day, etc).

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

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

 

[shelron]

Thank you both for your input,

Scheduler looks pretty straight forward.

Ron

 

[Mickle]

Ron,
This is the first post I've replied to so you might need to be patient. (been using this forum for six month, so better do my part)

On the topic of kicking off jobs overnight for updating. You could make a form with an event linked to the On Timer event. Set the timer for 15minutes. So every 15 mins it runs your procudure which only kicks off if the time is say between 0100 and 0115. Therefore, it only kicks off once and at at time you specify - you can narrow the range if you require a more precise time. You need to leave your machine on overnight to make this work.

Jeremy, I tried once using the last person to exit the database sets a backup/update routine in motion. I found that users were not happy about this and if the procedure took too long they ctrl-alt-deleted or switched the machine off - of course this played havic with the procdure.

Updating your tables - I have exactly the same problem. This took a while to set up but it has payed off 10 times over. What I did was: (say it was the "Employees" table you are updating)
1) Make a copy of your master Employees Table: say Emp
2) Make a copy of your sysbase table in access (using import makes it easy)
3)Write a routine that:
a) clears the Emp table and sysbase table
b) populate the sysbase table from Sysbase
c) Write as many qureies (delete and update) to scrub your data
d) Update your Emp table (which is fully normalised)
e) Use append and update queries to update you master Employees table.
4) Kick it all off at 1:00am

The reason I have for creating the tables is access as to linking them is performance and it gives you full control over your data and access error messages.

Cheers
Mick