Remote access for Access DB?

[beachcomber]

Hi,

I'm relatively new to remote access issues. I'm
more familiar with programming. So, I could
really use someone to help point me in the right
direction here. We have an Access program
that will be used by several remote users. We
would like the remote users to all share the
same database for the program (we would like the
database to be on a main server accessible by the
remote users).

Can anyone point me in the right direction for
how to solve this problem? Would putting the Access
database on a server and setting up a VPN
to the server be the way to handle this?
Can the remote users simply set up an ODBC connection
to the remote server with the database?
Any advice, suggestions?

Thanks for any help.

Beachcomber

 

[Keberhard]

I don't know if this is what you are looking for, but could you use the database splitter and put the back end on the server and the front ends on each user's computer?

 

[beachcomber]

Hi,

Thanks for your response. Yes, this is basically what I'm
looking to do. I guess my question is though, how will
the users who will be working on remote pcs (mostly from their homes) be able to access the database on the server which will be in our office.

Thanks,
Beachcomber

 

[Keberhard]

hmmm...this is something that i've been looking into as well...i'm trying to learn data access pages or asp so that i can put a few databases online. i'm not having much luck with the learning process.

the only thing i can think to tell you now (and i don't have much experience with this) is to maybe use pc anywhere or another program like that so that the users can "dial into" the server?

sorry i can't be more help!

 

[krsherm]

You can use ODBC or ADO (via the connection string) to retrieve data from anywhere in the world from Access. The problem has to do with network loading... it will be very very very very slow - you wouldn't want to use bound forms with multiple subforms. You really need to grab only the data that you need for the given task... which will make you a better programmer. My suggestion would be to upsize to MSDE or SQL Server. The server that the database (Access, MSDE, SQL Server) resides on will need to be running IIS and will need to be exposed to the internet. It's not very difficult and there are some excellent phone books... err, Access books at your local bookstore that will show you the way.

But, yes, it can be done. (I do it.)

Kevin

 

[beachcomber]

Hi krsherm,

Thanks for the tip. I'm not that familiar with network
loading. Could you explain to me briefly why this would
cause the application to run so slowly?

Beachcomber

 

[krsherm]

With an Access application, all data processing is handled by the JET engine, which ONLY resides on the client machine. So if your user is at home, and they are accessing an Access back end via ADO or ODBC (via the internet), and they open a bound form (say with the recordsource being the customers table), ALL of the data in the customers table will transfer over the internet so that it can be processed on the local machine.

If the client (your end user at home) wants to check on the status of Order 1234 for Customer xyz, there will be a tremendous amount of data crossing over the "wire" and, as such, it will run very slow.

With SQL Server (or MSDE) that processing of the data is done on the Server machine and only the pertinent results are sent to the client over the wire. So if your forms recordsource was set to "SELECT * FROM Customers WHERE CustomerName = 'xyz'" then that query would be run on the server and only the relevant data would be sent over the connection.

These issues are (almost) trivial when you have a dozen users accessing the data from a LAN... but they become paramount when accessing the data via ODBC or ADO over a remote connection.

There are some excellent books that address this, and the learning curve is nowhere near as steep as you might think it is. It is also the future....

Rock ON!

Kevin

 

[beachcomber]

Hi Kevin,

Thanks for that great response.

Best wishes,
Beachcomber

 

[oharab]

Using remote pcs with Access is asking for trouble! You have already mentioned asp and that is a good solution for this. Another thing to consider is replication.
With this a seperate database is created for the remote users. Using the replication manager, the replicated databases can be updated to send all their changed records to the master copy and update their copy with the latest data.
It might not be any good for you, I guess it depends on what you use the dB for, but it's another thing to consider.

B ----------------------------------
Ben O'Hara
bo104@westyorkshire.pnn.police.uk
----------------------------------

 

[cmmrfrds]

There are several products in different price ranges that will allow you to run access from a remote location. One of the more popular and more expensive is using Citrix. This is usually done by companies that can afford a little more expense. A less expensive alternative to Citrix is GoToMyPC

https://www.gotomypc.com/ad/corp/home

which does basically the same thing but much less expensive. These products only send keystokes so there is very little network traffic. The main advantage is that you can take any Access application and it will run the same as it does locally.

 

[beachcomber]

Hi Ben,

Just out of curiosity, what problems could
a person run into using remote pcs with
Access? Also, you mentioned the replication
manager. This sounds very interesting to
me. Where can I get more information on this?
Is this an Access tool?

Thanks,
Beachcomber

 

[oharab]

I am just very wary of using access databases long distance. Even a dodgy network can trash an access database. By connecting to it remotely y ou are increasing the chance of database corruption many times.
That said, the solutions recommended by cmmrfrds would be ok, as they do not send the data down the networks, just the on screen display.
Replication is part of access. Like I said, I've never used it, but have a look on the m$ website for replication manager and look on places like google for more info.
Alternatively start a new thread in the forums to ask those who might know more than I.

Good luck

B ----------------------------------
Ben O'Hara
bo104@westyorkshire.pnn.police.uk
----------------------------------

 

[petermeachem]

Replication works very well when it works, but can easily result in a corrupt database. Particularly if you use memo fields.
I've no idea how much citrix costs, but I don't think it trivial and brings with it a raft of other problems, Access being one of them. You might try some of the other remote control programmes, radmin from sunbelt looks good for me. I've not tried it over the internet as yet, it's fine on the local network. Peter Meachem
peter@accuflight.com

 

[beachcomber]

Hi Ben,

Thanks for the info. That was very helpful. With everything that I've been reading on this and other sites, I'm starting to lean towards an asp web based solution.

Thanks,
Beachcomber

 

[beachcomber]

Hi Peter,

Thanks for that information. As I mentioned to Ben, I'm leaning towards a web-based solution now but just the same that inside scoop about replication manager is good to know for any future needs.

Thanks,
Beachcomber