access db locking

  • Hi all

    I have a wizz bang dts that "attempts" to process around 50 access databases in a single directory (contents are all the same). The dts dynamically alters the connection, transforms data into the appropriate table, and moves the file before looping for the next file. Unfortunatly, move is not working. When the transformation is performed, an access ldb (lock file) is created, but seems to hang around until the entire dts package is complete, therefore, my ends up in an endless loop.

    For the life of me, after the successful transformation, I cant remove the lock on the access db.

    Ideas??

    Cheers

    Ck


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • One would be to just do the move at the end of instead of during the loop. The other would be to make sure you're closing the connection and setting to nothing - that should cause the lock file to clean up..I think!

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • Hi

    Ok, cool, i initially ripped an example from sqldts.com for looping but its a tad restrictive with access databases. In the end, I adapted this code to generate a string such as:

    [file1.mdb][file2.mdb][file3.mdb]<etc>

    Now, I simply loop around using some instr and mid functions to cut out the file names and process them accordingly.

    Works well and like most dts stuff, its very fast.

    Cheers

    Ck


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply