Solved

Compacting dbase via unattended scheduled task - DELAYED WRITE FAILED

Posted on 2004-03-22
38
957 Views
Last Modified: 2012-08-14
I will explain what I am doing, then the error I keep recieiving.

I run a series of ACCESS 2000 macros at night and have scheduled them to run using a batch file.  The batch file uses the names of tasks that have been set up in windows task scheduler.

MY BATCH FILE:

::--Start script.cmd---
@echo off
setlocal
::Put your task names in order of execution here.
::Surround names with qoutes. Use space as delimiter.
set tasklist="ImportLVTCData" "Compact Sales" "Import Web Data" "Compact Webdata" "Import Website" "Compact Website" "Compact LVTC1_DATA"


::Main routine
for %%c in (%tasklist%) do (
schtasks /Run /TN %%c
call :loop %%c
)
endlocal
goto :eof


::Task status checker
:loop
:: Pause. If you have sleep.exe
:: or other pause utility, put it
:: here instead of ping command
ping -n 5 127.0.0.1 > nul 2>&1

for /f "tokens=1,3,4 delims=," %%i in ('schtasks /QUERY /FO CSV /NH') do (
    If %%i==%1 (
        set var1=%%~j
        set var2=%%~k))
If "%var1:~0,-1%"=="Runnin" (
    echo Task %1 is running. Waiting...
    goto loop)
If "%var2:~0,-1%"=="Runnin" (
    echo Task %1 is running. Waiting...
    goto loop)
echo Task %1 ended.
goto :eof

::--End script.cmd---

Sometimes it works with no problems, but more often than not, I am getting the following error during the task IMPORTLVTCDATA.  

DELAYED WRITE FAILED:
Unable to save all the data to db1.mdb.  All the data will be lost.  May be caused by a failure of hardware or network connection.

. . . when I try to open the dbase it tells me that I cannot open it because it is currently in use by someone else - even if I reboot - and it IS NOT being used by anyone else!!!

This particular task imports 8 *.dbf (dbase4) tables then compacts on close.  I believe that the error is occuring during the compact - this task used to ONLY import the tables followed by another task that would compact the dbase(via the /compact switch) & I used to always get that error during the compact task - now that I compact on close, the error seems to be happening after the importing of the tables.

ACCESS has added a table called: MSysCompactError - it has the following columns:

ErrorCode      ErrorDescription      ErrorRecid      ErrorTable
-1611      Could not find field 'Description'.            INVENTORY
-1611      Could not find field 'Description'.            MSysObjects

. . . under the 3rd column, it lists a row for each table & query in the dbase plus 6 rows with the "MSysObjects"????

The dbase that I am trying to compact is on a network drive (not local), but I am sure that nobody is using it at the time it is being compacted.  I am using XP pro - I recently switched from Win 98 & never had this problem.

Any thoughts on this would be greatly appreciated.

MAx
0
Comment
Question by:MaxwellTurner
  • 14
  • 10
  • 8
  • +1
38 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 10651389
Greetings MaxwellTurner!
  I think this is where you might want to post a 20pt question in the Windows OS TA's to get one of those crew in here.  This reaks to me of a WinXP deal, with a touch of disk write commits interferring in the compact operation.

  Are you specifying Exclusive DB use during these operations?

 Have you considered getting a copy of FMS Inc's  Total Access Agent??  (www.fmsinc.com)  This REALLLLY saves a buttload of effort managing and maintaining Dbs in a shared production environment.  (and it can back up data files WHILE there are users on line!!!!)

regards
jack
0
 
LVL 1

Author Comment

by:MaxwellTurner
ID: 10651471
jadedata,

I typed this in the DOS section, then cut & pasted it into here , then into XP then back here - I couldn't decide where the best place was to place it but I thought that the error probably was an ACCESS related error so I ended up here.

Good idea to post alink elsewhere though - I will do that.

Oh yeah, I should probably note that none of my tables, queries, or macros have or mention a field called "description"!

Max
0
 
LVL 32

Expert Comment

by:Luc Franken
ID: 10651516
Hi MaxwellTurner,

>>DELAYED WRITE FAILED<<
This normally indicates that your drive is attached with a 40-wire IDE cable instead of the 80-wire IDE cable which is used nowadays.
I suggest you to check this and replace it if neccesery.

Greetings,

LucF
0
 
LVL 57

Assisted Solution

by:Pete Long
Pete Long earned 75 total points
ID: 10651526
Delayed Write Failed" Error Message When You Manage Files in Windows XP
http://support.microsoft.com/default.aspx?scid=kb;en-us;330174
0
 
LVL 32

Expert Comment

by:Luc Franken
ID: 10651537
Some further reading:

"Delayed Write Failed" Error Message When You Manage Files in Windows XP
http://support.microsoft.com/?kbid=330174

0
 
LVL 32

Expert Comment

by:Luc Franken
ID: 10651549
Hmm, sorry Pete.
0
 
LVL 32

Assisted Solution

by:jadedata
jadedata earned 75 total points
ID: 10651600
I think I should get a little credit for pegging this as a WinXP disk write issue on sheer intuition!!!  :) :)
I'm happy to split with these hardware geeks anyday!

Nice matching Links there boys!!
0
 
LVL 32

Expert Comment

by:Luc Franken
ID: 10651609
:))
0
 
LVL 1

Author Comment

by:MaxwellTurner
ID: 10651950
jadedata - EVERYONE GETS POINTS IF I CAN RESOLVE THIS!!!

QUESTION:  The database that I am trying to compact is on a directory on my companies server, not on my local machine.  I assume that the 80 wire cable that I would want to check is on the server not my local machine - not sure though because I think the db1.mdb is trying to be written to my "My Documents" folder.  Also, if I am using a 40 wire cable, does that means that the hard drive is also 40 wire and will have to be upgraded - I have a brand new IBM workstation, so I doubt that it is 40 wire on my machine?  My "Enable write caching on the disk" is turned on.

Max
0
 
LVL 32

Accepted Solution

by:
Luc Franken earned 150 total points
ID: 10651991
In that case, take a look here:
"Delayed Write Failed" Error Message When You Write a File to a Server
http://support.microsoft.com/?kbid=321733
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10652247
What would be the issues surrounding bringing the data file to be compacted over to the local drive for processing, and then transporting and overwriting at the server after the operation certified correct completion?
0
 
LVL 1

Author Comment

by:MaxwellTurner
ID: 10652259
Okay,

The dbase that I am compacting is on F: - our company server - but the error message indicated that the db1.mdb was being written to "C:\Documents and Settings\Maxwell Turner\My Documents" - on my local machine.  

I assume that I should be checking my local machine because that is where the write error is occuring - is that correct?

I just checked & my local machine does have an 80 wire cable - I physically counted 20 & it was about 1/4 the way across so its definitely not 40 pin.

Also, my "Enable write caching on the disk" is ENABLED.  What are the consequenses of turning this off?  Will it really hurt my computers performance?

LucF:

Your link described my probelm exactly - it is for Win 2000 though, I noticed.  Do you figure it will work for XP as well?

Maxwell
0
 
LVL 32

Expert Comment

by:Luc Franken
ID: 10652301
Could be that your version of the page is different than mine (I get a dutch version) and it clearly explains it's for both win2k and winXP to me. But as I've seen several times it could be different, let me know and I'll try to find another one for you.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10652307
This is making more sense in light of the way Access will write a temporary copy of a database to a location pending successful completion of the compact.  I don't think this would change even if you brought the file over to the local drive for processing,...
now that I think of it...
0
 
LVL 1

Author Comment

by:MaxwellTurner
ID: 10652402
LucF,

Sorry - you were right - I didn't look hard enough.  Sounds like I will have to contact Microsoft for the fix.

BTW - I attempted to change my registry as the workaround describes, but it was already the way it was supposed to be.

Max
0
 
LVL 32

Expert Comment

by:Luc Franken
ID: 10652533
This is just an assumption, but it could be this problem will be fixed in SP2 for winXP, you might want to check if it's allready available for you.
0
 
LVL 1

Author Comment

by:MaxwellTurner
ID: 10652743
I have called IBM, who in turn told me to call Microsoft & they actually emailed me the HOT FIX described in the article that LucF recommended (http://support.microsoft.com/?kbid=321733) - Its a zip with 2 Application files - I don't know which one to open so I am currently on-hold with MS for installation instructions.

I am pretty sure that this will work - I will keep you posted.

Max
0
 
LVL 32

Expert Comment

by:Luc Franken
ID: 10652766
Ok, good luck ;-)
0
 
LVL 1

Author Comment

by:MaxwellTurner
ID: 10653153
Still on hold . . . . . . .

Max
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:MaxwellTurner
ID: 10654035
I recieved and applied the HOT FIX so I guess when my tasks run tonite, I will know if it was fixed.

Thanks everybody for such prompt replies!!

Max
0
 
LVL 32

Expert Comment

by:Luc Franken
ID: 10655593
Glad to help and let us know the outcome.

(you'll have to delete your pointer in the OS-TA now as it has no use anymore)

Take care,

LucF
0
 
LVL 1

Author Comment

by:MaxwellTurner
ID: 10659384
Well,

I didn't work - same error on my machine this morning.

My local machine has a 80 wire cable, but the "Enable write caching on the disk" is ENABLED - I am abit leary to disable this - I don't really want to sacrifice performance even though I have a pretty quick computer (P4 2.8).  I am going to call Microsoft again to see if they have any other ideas before I resort to that.

I have the option also of moving my macros off to another Win 98 box which previously never had these problems.

Max

0
 
LVL 32

Expert Comment

by:Luc Franken
ID: 10659411
Bummer...

In the second link I gave, check the servers registry for that setting, try the workaround there. (maybe you didn't read carefully enough and missed that the workaround had to be performed on the server)
0
 
LVL 1

Author Comment

by:MaxwellTurner
ID: 10659417
I have a quick question:

When it is said:

"Ensure that the UDMA hard disk controller is using an 80-wire"

this is refering to the actual cable used to connect the hard drive? Correct?

Max
0
 
LVL 32

Expert Comment

by:Luc Franken
ID: 10659497
Yep, old IDE cables had 40 pins and 40 wires, newer cables have 40 pins and 80 wires where every second wire is only used for electro magnetic shielding because of the high frequencies of signals used through the cable.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10659518
Just as a lark,... try setting the Access ODBC time out default higher to allow more time to be able to retrieve data over the network.  I will state that there is no reason this should work, but I would try it were I in your shoes.

or,...
  I use this process for compact/repair/backups
(pseuda
  copy productiondatafile to tempdatafile  (in your case to local drive)
  rename productiondatafile to bakdatafile(appnameyymmddhhnn.mdbbak)
  compact tempdatafile to newproddatafile
  validate bakdatafile (open it and look around a bit)
  validate newproddatafile (open it and look around a bit)
  kill tempdatafile
 

I believe that this is fight between XP wanting to cache to disk immediately and having to wait for data from the network over the lan
am I missing some piece?

0
 
LVL 32

Expert Comment

by:Luc Franken
ID: 10659578
Good point jadedata ;-)

If I'm right, disabling "Enable write caching on the disk" would stop this fight at the cost of some performance.
If yours works it's the best way I guess.

Maybe we make a great team together ;-)

LucF
0
 
LVL 1

Author Comment

by:MaxwellTurner
ID: 10662607
LucF,

You are indeed correct - I just ran all my tasks 3x and not a single errer after I disabled "write caching on the disk".  Did not notice any decrease in performance either - if there is any it is very minimal.  Perhaps my computer is fast enuf that I just don't notice it.

Anyways, thanks guys (I am assuming yer guys - take no offence if yer not)!  Its been an adventure!

Max
0
 
LVL 32

Expert Comment

by:Luc Franken
ID: 10662619
You're very welcome Max ;-)

Take care,

LucF
0
 
LVL 57

Expert Comment

by:Pete Long
ID: 10727789
jadedata

"Hardware" Geek!!!!!  Im a network Engineer :p
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10727819
Pete:  Ok a Web Geek!
0
 
LVL 57

Expert Comment

by:Pete Long
ID: 10727876
Im aghast I come through to re-read my old comments and find a slur on my otherwise spotless character im so upset Im going to bed g'nite Jack ;)
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10728238
Your reputation is only appears spotless because it is so evenly covered in blemishes...
nitey nite!
0
 
LVL 57

Expert Comment

by:Pete Long
ID: 10732907
ROFL - you appear to know me too well, there aint no blemishes however on my boyish good looks ;)
http://petenetlive.hopto.org/about.htm thats not blemishes, thats a windswept and interesting aura!
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10732954
OH COOL!!! I love the Stinkclaire!!!!  I have to get a picture of my Tandy MC10 with a killer 4K of RAM!!  oooo and let's not forget that mind-blowing 16K ram expander!!  Love those little chicklet keys!!
0
 
LVL 57

Expert Comment

by:Pete Long
ID: 10732967
:)
0
 
LVL 32

Expert Comment

by:Luc Franken
ID: 10733428
And I tought I had some old computers still running :(
I have both and MSX-1&2 the last one with a whopping 128kb of video memmory and a Single Side Double Density diskdrive (3.5")
0
 
LVL 57

Expert Comment

by:Pete Long
ID: 10733619
Thats most strange, my Inbox shows a post by ee_ai_construct wonder how that could have happened on a question thats been quiet for over a month? "pete ponders smirkily"
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now