Database Linking

[Soulspike]

Hello all again,

I am looking for some recommendations from the experts. I have a database that tracks failure information a many different sites. I need all of these satalite datbases to upload their information into a Master Database on a server somewhere. I also want to be able to restore the satalite databases with their location information if I send out an updated database. Currently I have around 20 linked tables and I append data to or from the linked tables. It seems to be really slow for the small amounts of data that it transfers. Is there a better way to accomplish this?

Thank you for taking the time to read this.

Damon

[sierra7]

Hi Damon
I'm not totally clear about your situation here.
Do you have many sites with 20 tables each
or
do you have about 20 sites each with one table, and your Master database links to each as separate external tables?

Do you want to see data at the remote locations in real-time (over the link) or can you up-load on demand to duplicate tables within your Master database?

How many records are in the remote data tables and how fast are they changing?

Whenever you query data on an Access table it reads ALL the data from the table to the processor, so you may have response time problems depending upons the volume of the remote data and the speed of connection.

I can imaging that if you have forms that look at data from many locations simultaneously, then reponse times could well be slow and if the application permitted it would be better to look at data periodically cached in local tables.

S7

[Soulspike]

Thank you for your response. To clarify your questions

Q1 I will have about 20 sites around the US useing the same database. Need them to update about 5 or so tables from their local copy of a database to the server. Currently I do that through some linked tables and append queries. It doesnt give me the ability to update data though if they do make changes to a record.

Q2 I dont want them to see the data real time, All I need is for them to be able to download their specific locations data from the master database. As I make revisions and changes to the database they will recieve an empty copy of it. Then they need to grab any information they saved on the master DB

Q3 For the most part we are looking at less then 5000 records, adding no more then 1000 per year.

Q4 Accessing data from the master db is extremely slow. takes 10+ minutes. I would like to speed this up if possible.

I hope I answered your questions. I hope some of the experts here can shed some lite on a better way to perform these functions


Thank you

[sierra7]

Hi Damon
Thanks for clarifying your problem.

I have no experience of running Access over a WAN so I will leave others to inquire how you are doing this, and the possibility of improving connection speed.

From your answer to Q3 it appears to me that database 'Replication' may assist your situation (I.e. you dont need real-time at either end). I have only one experience of using it to synchronize a laptop and a server, so I can't comment on what speed you would achieve over a WAN, but it would resolve your issues in Q1, and get new records and updates to existing from remote stations, without running queries.

Perhaps others could advise how to automate the process to run daily to multiple sites, and how each site could syncronize with just the data relevant to themselves. I'm sure it must be possible.

I was a little concerened about your answer to Q2, "As I make revisions and changes to the database they will recieve an empty copy of it." but on reflection I suppose this will still work with Replication, as the first time a remote 'logs-on' from the revised software and syncronizes to the Master they will automatically download their own data.

I would be tempted to set-up a prototype system and test Replication over the LAN. (The wizard is very easy to use, but BACKUP your system first!). I would then set the network card in the 'satalite' computer to run at 10MHz (instead of (100MHz) to observe the difference.

I shall watch with interest to see what others, more expert than myself, can suggest.

S7

[Soulspike]

[font=Verdana][size=2]It looks like replication is the answer. I have been useing access for a long time and never even noticed that option. But reading about it now that you mentioned it, sounds like exactly what I need. Thank you very much for your input.[/size][/font]

[sierra7]

[font=Verdana][size=2]It looks like replication is the answer. I have been useing access for a long time and never even noticed that option. But reading about it now that you mentioned it, sounds like exactly what I need. Thank you very much for your input.[/size][/font]

Glad to have been of help!
When I first opened you posting I thought it may have been about programmatically linking Front-ends to Back-ends or the like but was surprised to see it involved a WAN

How do you do this?
I would be interested in viewing a remote system rather than using LogMeIn, which requires taking over a local desktop.

Best of luck with Replication. It is certaily worth a good read around the subject before deploying it nation wide but I am sure there are some good references out there because it seems such a useful facility.

I've had only limited experience but was impressed how simply it worked.

You might start a new thread with 'Replication' in the title to find out experiences of other members.

S7

[Jim Doherty]

Glad to have been of help!
When I first opened you posting I thought it may have been about programmatically linking Front-ends to Back-ends or the like but was surprised to see it involved a WAN

How do you do this?
I would be interested in viewing a remote system rather than using LogMeIn, which requires taking over a local desktop.

Best of luck with Replication. It is certaily worth a good read around the subject before deploying it nation wide but I am sure there are some good references out there because it seems such a useful facility.

I've had only limited experience but was impressed how simply it worked.

You might start a new thread with 'Replication' in the title to find out experiences of other members.

S7

you might want to have a look at how Access performs under Citrix Metaframe. It has been around now quite some time and not everyone knows about it but when used to view and input to an access database over widely spread sites it certainly eradicates speed issues as you see an image of the database on the server yet your visual perception and operation of the database is as though it is on your desktop. Not cheap and not an option for everyone but for corporates requiring a solution when they do not have robust rdbms (SQL server,oracle) it gives them at least the choice when having to work with Access databases over a WAN.

And no I don't work for Citrix before anyone asks LOL!

Regards

Jim :)

[sierra7]

Thanks, that is certainly interesting Jim.

I looked at Citrix when it first came out. I was with a 'corporate' at the time who were balking at the cost of upgrading 486 computers to Pentiums with 32Mb RAM. It sounds like I ought to look at it again.

It could potentially be a solution for SoulSpike if they were to re-think their stratergy and maintain a central database, rather than having satalites synchronizing with the Master.

S7

[Jim Doherty]

Thanks, that is certainly interesting Jim.

I looked at Citrix when it first came out. I was with a 'corporate' at the time who were balking at the cost of upgrading 486 computers to Pentiums with 32Mb RAM. It sounds like I ought to look at it again.

It could potentially be a solution for SoulSpike if they were to re-think their stratergy and maintain a central database, rather than having satalites synchronizing with the Master.

S7

I agree with you it could certainly kick a few attached linked tables into touch for sure :)

Jim

[Soulspike]

That sounds like a great Idea too, checking with my company to see if that is available. I know we use it for some other tools so it probably wouldnt hard to implement. I have not done much with access and multiple users are there any major considerations when designing a DB for multiple users?


Thank you all for your expertise.

[Jim Doherty]

That sounds like a great Idea too, checking with my company to see if that is available. I know we use it for some other tools so it probably wouldnt hard to implement. I have not done much with access and multiple users are there any major considerations when designing a DB for multiple users?


Thank you all for your expertise.

In a word?......concurrency....don't let your user base grow to big remember Access is not the best beast for handling multiple concurrent connections seemlessly. If you need that scale up one to SQL server or Oracle

Jim