Local db file to append to network file

[Montez659]

We have a small problem that I am trying to think through the solution to and could use some help. We have a db split on our network that runs fine (albeit a bit slow) which is fine for people in our main office. We also have Reps out in the field that could access the db on our network, but it moves so slow that it literally freezes up their computer.

I was wanting to create a local copy of the db (or really, just two of the tables) and then append to the exisitng network db whenever that Rep comes into the main office (once a month). A couple of problems:

1. How do I keep the autonumber from creating the same ID in the real db and the local copy?
2. The second table is related on the autonumber ID with a one-to-many relationship. If the ID of the original record (in the local db) changed, it would affect this table's data as well.

Any thoughts on how to approach this? All help/opinions are definitely appreciated!

 

[boblarson]

The optimal way to use an Access database remotely would be to use Terminal Services or Citrix. If you are using the mdb file format you could potentially use Replication but that can be something which can introduce other problems. See this here:
http://dfenton.com/DFA/Replication/index.php?title=Main_Page

 

[Montez659]

If I were in charge...


Ironically, the co. that I work for is using Citrix, but that seems to be part of the problem.

But I don't work for IT and don't have sufficient enough priveleges (or knowledge for that matter) to really be working with Citrix.

I know that what you suggest is the best option, just not a viable one at this time. If it comes down to it, the Rep can save up their paperwork and add to the db on their monthly trip in, but I was trying to avoid double-duty for them.

 

[boblarson]

But I don't work for IT and don't have sufficient enough priveleges (or knowledge for that matter) to really be working with Citrix.

Yes, you have to work with the Citrix administrator but it is something we do here and it isn't that hard. We have a copy of the frontend linked up to the file server where the backend resides. We have a script that runs (I can give you that if you wish) to copy the frontend to a temporary folder that gets created for each person when they log on to Citrix. Then a shortcut is published on Citrix to run that batch file which copies it and starts it. Very simple and yet allows you to run it all from various locations. We have locations in Kentucky, Texas, California, Oregon, and more which access it all via Citrix and that saves our bacon.

I know that what you suggest is the best option, just not a viable one at this time. If it comes down to it, the Rep can save up their paperwork and add to the db on their monthly trip in, but I was trying to avoid double-duty for them.

Well, if they are accessing it outside of the LAN (either via WAN or just through the Internet) you are playing Russian Roulette with your data.

 

[Montez659]

Hmmm...well, some stuff for me to think about at least. I appreciate the insight!