[AccessD] Backend database corruption

Hi all

A variation of 1. that TradeShift uses for FTP upload:

0. Write a small "pilot file" once the upload is complete. Could be just an empty file with an appropriate file name to identify the file being uploaded. 
Don't process  an upload if its "pilot file" doesn't exist.

/gustav

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Jim Dettman
Sendt: 23. februar 2015 15:17
Til: 'Access Developers discussion and problem solving'
Emne: Re: [AccessD] Backend database corruption


 +1 on #1.

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Friday, February 20, 2015 06:34 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Backend database corruption

Some other methods.

1. Write a small "guard file" before you start the upload and delete it once the upload is complete.  Could be just an empty file with an appropriate file name to identify the file being uploaded. 
Don't process  an upload if its "guard file" exists

2. Try to open the file for exclusive access from you import routine.  
You shouldn't be able to while the file is still open and being written to.

3. Write the file to a temporary file name and rename it using a standard convention  once fully written.  Only process files with an appropriate file name.

--
Stuart

On 19 Feb 2015 at 21:34, McGillivray, Don wrote:

> And if you go the CSV route, be sure that your app on the server has a 
> means of determining when a CSV is complete and ready for import.
> Years ago I built a system that imported data into an Access BE from 
> fixed-width text files produced by automated mail inserting machines.
> Each input file would contain tens of thousands of records, and I had 
> to deal with the problem of a large file being "present" in the drop 
> zone but possibly still being written to when detected.  If I remember 
> correctly, I dealt with it by capturing each file's size on either 
> side of a brief interval.  Files that were the same size on both sides 
> of the pause were considered ready, and the others were left for the 
> next import cycle.  I had to experiment a bit to find the appropriate 
> pause interval.  There are probably smarter ways to do this, but it 
> worked . . .
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W.
> Colby Sent: Thursday, February 19, 2015 1:06 PM To: Access Developers 
> discussion and problem solving Subject: Re: [AccessD] Backend database 
> corruption
> 
> Loss of connection while writing to an Access DB is a known issue, 
> never fixed, and probably unfixable.
> 
> Don't use Wifi / WAN with an Access BE.
> 
> The best option is to move the BE to a SQL Server BE.  That will 
> absolutely solve this issue.  If you must continue to use Access as 
> the BE, then write CSVs to a directory on the server and have an 
> Access app RUNNING ON THE SERVER watch for these CSVs and import them 
> into the table.  At least if the write to the CSV file is interrupted, 
> it does not corrupt the BE.
> 
> John W. Colby