General access connectivity question..

[quahtrader]

Hi,

I have a number of stores running an access based point of sale system. Currently when I want info from the database(s), I use RDP to connect to a store, use Access to query, etc. It's a pain to do this for 5 or 6 stores and I'm looking into trying to "centralize" this stuff.

I spent 15 years doing mainframe systems programming - so database stuff is not new to me - but PC stuff like this is. I've done a lot of searching but can't really figure out what the options are.



I'd like to be able to "connect" to these "remote" databases from my PC - using either access on my pc or MySQL. I'd prefer MySQL. I want to be able to "see" the remote databases as if I were actually on their system.

It seems so simple, yet I can see anything obvious for some reason.

I'm not looking for total hand-holding here, but some direction would be very appreciated!

[RuralGuy]

The internet is so slow compared to a LAN that the POS system would be almost unusable if you centralized the BackEnd over it, IMHO. Just my $0.02

[quahtrader]

The internet is so slow compared to a LAN that the POS system would be almost unusable if you centralized the BackEnd over it, IMHO. Just my $0.02

Sorry, I wasn't clear - I don't want to run to POS system over the internet.

All I want to do it connect to the database(s) over the internet in order to pull some data from the database that the POS uses.

For example, there is a field in a table in the database that contains the amount that should have been deposited that day. I want to see this for all 5 stores. Right now I have to:

1. RDP into the computer.
2. Run my access query (and hope I don't get bumped out of RDP because someone starts using the computer)
3. Exit out of RDP.

And then repeat that 5 other times (once for each additional store).

I'm looking for a way to be "connected" either from access or MySQL running on my PC and be able to "see" the data on the other systems without all of this connect and disconnect stuff via RDP.

[RuralGuy]

I have one of my systems email me the data I want every day. I use CDO and it works a treat!

[quahtrader]

I have one of my systems email me the data I want every day. I use CDO and it works a treat!

Ah, that is something else on my list too - email "reports" daily. I'll look into that.

But on my original question, my needs are not always the same. Basically, I want to be able to query the database(s) on the other machine without having to go through Remote Desktop, as it is too time consuming - and I have to copy forms/queries/reports to each machine, etc.

Is there a way to connect to the data on the other machines?

[RuralGuy]

There are probaly several, all with security issues that would need to be addressed. I don't do it so I'm not much help at this time.

[NTC]

my opinion; to query a distant db is not feasible.....and so you send the entire BE file - and then query it locally....

you can automate the file sending.

if you want to consolidate all those sites into a single set of tables you can consider using the www.AccessTables.com service rather than doing it all yourself....

[carlmdobbs]

Hi,

I have a number of stores running an access based point of sale system. Currently when I want info from the database(s), I use RDP to connect to a store, use Access to query, etc. It's a pain to do this for 5 or 6 stores and I'm looking into trying to "centralize" this stuff.

I spent 15 years doing mainframe systems programming - so database stuff is not new to me - but PC stuff like this is. I've done a lot of searching but can't really figure out what the options are.

I'd like to be able to "connect" to these "remote" databases from my PC - using either access on my pc or MySQL. I'd prefer MySQL. I want to be able to "see" the remote databases as if I were actually on their system.

It seems so simple, yet I can see anything obvious for some reason.

I'm not looking for total hand-holding here, but some direction would be very appreciated!

Microsoft is now supplying the answer to your problem with Sharepoint. New in MS 2007 and 2010, this enables you to access information from a remote location and is a good answer to your situation.

I have worked with web networking before to simplify things. The North Carolina DNR had a lot of problems with speed of information transfer. I could help with that too if there is a problem with waiting 20 minutes for data to download from a remote location.

[quahtrader]

my opinion; to query a distant db is not feasible.....and so you send the entire BE file - and then query it locally....

you can automate the file sending.

if you want to consolidate all those sites into a single set of tables you can consider using the www.AccessTables.com service rather than doing it all yourself....

Really? I appreciate the input. Is there no way to use ODBC or something like it?

Not questioning at all - but it's hard to believe that there is no way to query remote data.

What if I were to install SQL Server Express on each machine - could I somehow connect to that and then have it "See" those access databases?

Sending the database to the local PC is possible, but it is rather large - and will limit me because the data is not "live".

Thanks for the replies!

[NTC]

Access is not designed for the BE & FE to be separate geographically across the generic internet - it will not work. The time outs will interfere and it will hang. ODBC is a standard for connecting to differing brand db maker products - and doesn't change the WAN situation for Access.

Going to a web architecture is one alternative. VPN to each Access individually is an alternative. Consolidating their tables is an alternative. Sharepoint can act as a consolidator - - all the Access dbs that are distance away from the SP machine will link to it using the online/offline capability (which has many of the same aspects of replication) but won't be able to work in real time connected to a distant SP hub. SP is a pretty big product itself - so one doesn't entire into it lightly and frankly I doubt anyone would just for this type scenario. Automating emailing of the BE file is probably your simpliest approach.

[RuralGuy]

You can also automate zipping up the BackEnd if that is the way you want to go.