Updating tables on web database

[psbsmms]

I have a database that we are using on our lan for orders and a separate DB for the web store. What i would like to be able to do is "Snyc" the item table so i only have to enter items once. i have copied the entire web DB to our lan and automated filling the item table initially and then copy back to web, however this will not work once ordering starts because I will overwrite order tables on the copy. i tried to import from lan to web, but i don't get an option to append to a table, it just create a new table. Any ideas?

 

[HitechUser]

Create a linked table for each of your tables. A simple FE/BE (Frontend / Backend) setup!

Example ... (after copy to LAN)

Delete tblItem table from web MDB then create a linked table (called tblItem) linking it to the LAN MDB. The applications will view this as a local table.

I hope this helps.

 

[psbsmms]

I had tried it the other way, linking it frrom the web to the LAN and I couldn't get a good connection string, I just tried it the other way link from LAN to web and I get the same problem, it was a good try though. In my research I'm not sure there is a way to do this with Access, I might have to go with SQL or higher db that allows for site mirroring. Thanks for the response, and if I do figure it out I will post here. Most of what i found was to use ASP and remake the front end.

 

[HitechUser]

Your respones puzzles me. If both databases are MDB's then you should be able to link either way. I have done this. Is there something else in this configuration you failed to mention??

 

[psbsmms]

I am tring to link between our lan and a web site hosted on the WWW. And although it will let me link the tables it cannot follow the link either way. I have successfully linked tables across our LAN many times, but for some reason, possibly permission at the web server it doesn't allow for communication between the DBs.

 

[Bugget]

So the web database is actually at a host somewhere (or colo) and you are copying it via ftp I am guessing? If this is the case there are only a couple of options for you if you have to (financially) stick with access and none of them are really elegant.

1) You can program a form which includes an ActiveX Web Browser object in the form. You can then program the ActiveX Web Browser to open the ftp site, log in, and copy a file there.

I don't like this because there are a lot of place to error and if the copying of the database goes down then your website falls flat.

2) You could try a tunneling program such as Stunnel:

http://www.ucisa.ac.uk/help/admin/db_stunnel

If you can, I would try to get a copy of SQL Server as it is able to do exactly what you want. You can keep a copy of the DB on your computer and set it up as a publish then set up a copy on your website backend as a subscriber and set the items table there to update from your server every couple of hours. SQL Server also has much more robust transaction role backs so that all of this automated transfering won't destroy the website if it fails.

Good luck!

- Tom

 

[psbsmms]

Thanks, that is what I've been discovering. Wanted to start out in SQL, but cost was prohibitive on this spec venture, where are the 90's when you need them. Everyone overpayed, over bought, and went bust.