Connect to Access back end from Outside VPN

[speedman_2001]

So here's my problem, I have an access db (split in two) that is currently used in house. All network connections are in house so using linked tables isn't a problem. The problem I'm trying to find a solution to is that I need to give the front to an outside source that will have no way of getting "inside" our network (via VPN). Any way that I can accomplish this?

 

[spikepl]

Remote Desktop, TeamViewer, gotoAssist -all to run the frontend on machine on the LAN from an outside machine

 

[speedman_2001]

Not something I thought about but won't work very well. That would require me to give them credentials to log in via RDC. TeamViewer and GoToAssist are more troubleshooting than access and along the same lines as RDC. I'm trying to give them as little access as possible by only giving them the front end of the db. DB is used to update "points" for an employee reward program. The outside person receiving the front end is in charge of letting our employees use their points to purchase merchandise.

 

[spikepl]

I was outlining available options. If you cannot provide VPN access (which would not work very well anyway, because Access sucks over WAN) then surely you cannpt let them on the network in any other way.

And TeamViewr and GotAssist allow standa alone login for the external party, so in lack of anything better ....

The precise meaning of "in charge of letting ..." is not clear. IS this something that could be done using some emails/forms, that could be sent from - and digested by - Access?

Finally - two backends , and resync changes once in a while, or use Excel to export/import data to/from the external party.

 

[speedman_2001]

Thanks for the replies, by no means am I saying anything is wrong with your replies, just not a road that I want to go down, if avoidable.

The way the db is set up is that we (in company) create employees and add points to their file. They (outside company) needs the ability to see how many points each employee has and then make record changes if an employee wants to redeem those points.

All record changes are currently handled through forms/buttons/VBA/etc...

I thought along the lines of manually importing their (outside) changes to the db, but I'm trying to automate as much as possible.

Thanks for the help and ideas spike... it's much appreciated.

 

[spikepl]

I have an application where orders for some ppl in the db are mailed in a spreadsheet, the supplier modifies two columns and mails back the spreadsheet which then is imported into the db.

 

[mdlueck]

Are you looking for one way only replication, or both ways?

If one was is adequet, have an automated process compact the DB, make a copy, and from there you can do any one of a number of ways to send the DB to the remote site.

If both ways, then you could arrive at the same sort of replication that Lotus Notes has, and implement some sort of "change conflict" scheme where both sides edited the record since the last sync. This is MUCH more involved, requiring DB schema changes.

Further, one side still must be the master for replication. So it ends up being "HUB and spoke" replication if multiple sites are involved.

The DB schema changes are to add two TimeStamp columns to each table to be replicated. One column stores the last write timestamp. On slave servers, the other timestamp column is used to keep track of the timestamp of the record as-was on the HUB server. If that does not match the timestamp of the record on the HUB at the next replicate, then a HUB update of the record is available. If on the slave the timestamp also does not match, then both sides edited the record since the last replication and thus invoke the replication conflict logic for that record. (So on the HUB server, BOTH timestamp columns are always updated. On slave servers only the "last save" timestamp column, not the "last remembered HUB" timestamp column.)

TEDIOUS! But doable.

 

[speedman_2001]

Looking for 2 way replication as much as possible but it's looking quite problematic. Simple solution at this point is looking like using spike's idea and manually importing data. Pardon any of my ignorance in ideas I come up with as I'm not a VB programmer by trade but more or less got thrown into it... is it possible to make an ODBC connection to an access db? I do have ways that I can make the db holding the data available to the outside if need be.

 

[spikepl]

speedman2001 - Access is two things: a dumb data file (not server, the data just sit there for the Access JET-engine to grab them) and a user interface.The db engine runs where the user interface is. So it would have to drag all the stuff across the WAN to be able to do something with it and that is not advisable.

There is perhaps another way: making a small "interface db", agreeing who works on it when, and then stuffing it into an online service like Dropbox.

 

[speedman_2001]

Thanks spike, I didn't have my hopes up on that one don't worry. We had talked along the lines of DropBox. Either that or manually importing may be the way to go. Thanks for the help guys.

 

[speedman_2001]

Hopefully you guys are hanging around still as I have another genius idea.

I have a MySQL db that I can use but can only make a PHP connection to it. Is this possible? Basically want to have linked tables through PHP. I apologize if I'm trying to use/reference PHP in a way that is not possible as I'm not very familiar with PHP.

 

[mdlueck]

PHP is a programming language, usually used in web programming... aka right on a web server.

MySQL could also be running right on that same webserver and only accept connections from the server itself.

You would not be able to connect Access to MySQL in that case as MySQL is not accepting connections from other machines on the network.