how to connect to remote database ?

Paul11 · 11 Mar 2008 at 16:26

what is the best (simplest) way to remote connect to another location to share an access database? i dont want to control a computer remotely, just to share the database file.

looking at vnc, hamachi, remote desktop, goto my pc, and am getting confused.

any help much appreciated
thanks

Gaz1988 · 11 Mar 2008 at 16:49

cant you just create another database and link to the tables on the shared database?

Paul11 · 11 Mar 2008 at 16:50

you mean "link" across the internet ?

mynsa · 11 Mar 2008 at 17:51

the easiest way would probably be remote desktop, this is because it is already installed and gives you flexibility i.e. when access isnt working you can diagnose the problem.

There may be a few problems if there is a router or firewall blocking the RDP traffic - can you already connect to the computer using RDP?

Paul11 · 11 Mar 2008 at 20:17

not tried rdp yet.

will this allow me just to have my access open as the front end and connect to remote machine via a mapped drive or similar for the data file ??

atomiser · 12 Mar 2008 at 08:04

no, rdp is a remote desktop tool. have you ever tried to use an access database over a network before? the experience i have has been pretty horrible...

something like hamachi is your best bet as it is a vpn tunnel between two machines and you do not need to reconfigure any network level devices for it to work - install on the pc's you want to share between, create the network, and connect.

good luck!

p.s. if it were me i would *much* rather be doing it over a client such as rdp rather than natively!

Paul11 · 12 Mar 2008 at 08:54

atomiser said:
no, rdp is a remote desktop tool. have you ever tried to use an access database over a network before? the experience i have has been pretty horrible...

something like hamachi is your best bet as it is a vpn tunnel between two machines and you do not need to reconfigure any network level devices for it to work - install on the pc's you want to share between, create the network, and connect.

good luck!

p.s. if it were me i would *much* rather be doing it over a client such as rdp rather than natively!

when you say use access over a network is horrible,isnt that the same as using it over hamachi? will that sill be horrilbe to use?

when you say, "over a client" do you mean hamachi ? and what do you mean by "natively"

excuse the silly questions, i have never set up any remote access before.

bledd · 12 Mar 2008 at 09:00

hamachi = no network config needed, easy as pie

mynsa · 12 Mar 2008 at 11:06

with RDP you will need to work on the machine with access on it, i.e. build you forms on over RDP on the computer with access on it.

Do you need to extract or work with the data on the access database at external locations?

Paul11 · 12 Mar 2008 at 11:22

the set up i need is:

to have one person working on access at one location

and me to log in via the internet to their pc and work from my location on the same database

in simple terms, 2 people usign the same database from two different locations.

bledd · 12 Mar 2008 at 11:23

hamachi you want mate, it'll create a network link between that machine and yours

atomiser · 12 Mar 2008 at 12:38

"when you say use access over a network is horrible, isnt that the same as using it over hamachi? will that sill be horrilbe to use?"

yep - it is - and it will probably be horrible over this too - but it's a solution if thats the route you want to take.

"when you say, "over a client" do you mean hamachi ? and what do you mean by "natively""
hamachi is a client, and it makes a vpn between two hosts. the context in which i meant was that ms access is rubbish across a network, therefore i would prefer to be using a remote desktop client (which is very network efficient) to control a machine with access installed that had local access to the ms access database file.

"excuse the silly questions, i have never set up any remote access before."
no problem - it can sometimes be a bit of a tricky concept to get your head round if your not familiar with how it works.

the bottom line is: ms access across a network is rubbish because it's not designed to be used in this fashion. your best way forward is to use an efficient means of accessing a host (i.e. remote desktop) that has the ms access data preferably locally.

Paul11 · 12 Mar 2008 at 12:46

bledd. said:
hamachi you want mate, it'll create a network link between that machine and yours

thank you bledd, will have a playaround with this.

atomiser said:
the bottom line is: ms access across a network is rubbish because it's not designed to be used in this fashion. your best way forward is to use an efficient means of accessing a host (i.e. remote desktop) that has the ms access data preferably locally.

when you say remote desktop, you mean built in to windows?
do you mean, to control the other machine as if you were on it?
when you say has the data locally, how can we have two machines both with data locally in different locations?
i want to have two people using it at the same time (one on a local machine and the other accessing that machine over internet)

thank you again for your patience of me not getting it

atomiser · 12 Mar 2008 at 13:11

what im basically getting at is access is nasty across a network because it was never designed to be used in that fashion. if you have a need to make it work then hamachi is your best solution. a sidenote to add on here is that i've only ever experienced an access database across a network being the only person having it open. are you absolutely positive that multiple people can have the database open simultaneously? i think your going to hit a brick wall here because to the best of my knowledge access is not client/server 'compatible' and it locks the file once the first person has opened it - for what you want i think you are going to need ms sql or oracle or some other such client server database. i'm happy to stand corrected on this though.

Paul11 · 12 Mar 2008 at 13:41

atomiser said:
what im basically getting at is access is nasty across a network because it was never designed to be used in that fashion. if you have a need to make it work then hamachi is your best solution. a sidenote to add on here is that i've only ever experienced an access database across a network being the only person having it open. are you absolutely positive that multiple people can have the database open simultaneously? i think your going to hit a brick wall here because to the best of my knowledge access is not client/server 'compatible' and it locks the file once the first person has opened it - for what you want i think you are going to need ms sql or oracle or some other such client server database. i'm happy to stand corrected on this though.

right, i got you now.

i am not sure either if access will allow multiple access to its database, although i did look at the sql option and someone told me access would do it, but i think i will test it myself and see.

thank you ..

edit:

found this...

Why can't I have multiple users open a shared MS Access database at the same time?

The easiest (and most stable ) way to fix this is to set up a "Front End" and "Back-End" situation.

Build a new DB and IMPORT only the TABLES from the ORIGINAL DB. Place this DB on the Network where all of the DB's Users can access it. This is the "Back End".

Replace all of the existing tables in your ORIGINAL DB with LINKS to the Tables in the NEW DB. It is now the "Front-End".

Now you can give each user thier own COPY of the "Front-End" and they will all add/edit data in the same tables (the "Back End"). The only time you'll have an issue is IF two people try to access the SAME RECORD at the same time (whoever gets there 1st will "lock" the record, but no-one will "lock" the DB).