Forum Moderators: open
The work will take a few months, and in the short term, we'll be converting part of the site to ASP + Access (their office system currently uses Access). They'll be updating their web database every day simply by FTPing a copy of the office database to a remote web server. However, we're all slightly concerned about errors in the update process as the databases are quite big.
Is there a simple way to avoid errors in these updates? (Please don't say best not to use Access ... I know!) Is there a way to automatically synchronise the on and offline databases so we don't need to use FTP? Any other thoughts or ideas?
I have a good couple of years experience devloping with ASP / Access / SQL Server, but I've never had to worry about temporary solutions like this .....
or write a routine to extract that days data from the Master.db and update that way
DaveN
the biggest problem is that sometimes if the site is busy, when i upload the new database then the access lock file causes an error - which prevents all calls to the db.
(sorry am not technical) this is easily resolved but requires the application (?) session to be restarted
- obviously this requires some kind of admin access to the server
ps if theres a way of synchronising on and offline access db's i'd love to know too.
If you are using the database as read only, then upload the new one with a slightly different name, database2.whatever, and then when the upload is complete, rename the original to something else, databaseOLD.whatever, and rename the new to the correct name. Then, there is less oikely hood of problems, and if there are problems, you can quickly switch back to the old one.
If you are writing to the database however, (off the top of my head) you could
1) upload the new one, again under a different name, and then have a routine that checks for differences between the two and updates the old on accordingly, or
2) have a routine that checks the local database aginst the online on and updates accordingly.
Onya
Woz
>>>upload the new one with a slightly different name,
yes do that already, it cuts down the problem a lot, but it occassionally occurs
>>>have a routine that checks the local database aginst the online on and updates accordingly.
this sounds cool, i'll check this out.
So for access, something like this?
objConn.open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=http://www.myDomain.com/secure/myDatabase.mdb;")
If a network drive connection exists between the web server and office file server, you could "link" the officeDB inside the webDB. Create an Access macro to drop the existing tables and insert from the linked tables. The AccessDB has a command line switch to execute a specific macro upon startup, so this batch file could included as part of a scheduled task.
You can setup DTS package on your shared SQL server to transfer the data to the Access. The advantage of DTS is that you can transfer one table at the time, plus DTS can be set as scheduled job in SQL Server.
I am going to run into a similar problem soon and have been thinking about this but have not as yet implemented anything so this is pure conjecture on my part.
What I am thinking is to have a script on my local machine that would extract a row from a database and then pass that to a page at the domain that would do the checking/updating. That script would then redirect back to the local machine, and so on untill all updates are complete.
This would require the local machine to be using a local IP such as 192.168.0.1 and of course the site script would be at www.yourdomain.com/admin/script or some such similar. Success would hinge on the website script redirecting back to 192.168.0.1 correctly to get the next row from the local database, but as you would be running this from the local machine I think it should work.
Untested at this stage, hope it helps.
Onya
Woz
