locking application during update

[getrighteous]

I have created an networked Access application that allow users to view data coming from another Access database via linked tables. The users do not add any data to either database in the process, all data is read only. The data in the "data" database (where the data files are stored) gets updated on a monthly basis by executing a macro that runs the following code:

Public Function GetFiles()
    MyPath = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name)))
    MyName = Dir(MyPath, vbDirectory)
    Do While MyName <> &quot;&quot;
        If Right(MyName, 4) = &quot;.txt&quot; Then
            strTblName = Left(MyName, Len(MyName) - 4)
            strSpec = LCase(strTblName) & &quot; Import Specification&quot;
            If Not CurrentDb.OpenRecordset(&quot;SELECT * FROM MSysObjects WHERE name = '&quot; & strTblName & &quot;'&quot;).EOF Then
                DoCmd.DeleteObject acTable, strTblName
            End If
            strFile = MyPath & MyName
            DoCmd.TransferText acImportDelim, strSpec, strTblName, strFile
        End If
    MyName = Dir
    Loop
End Function

This function loops through a directory and for each text file, checks for the presence of the appropriate table, deletes it, and imports the text file.

This all works just fine -- unless someone else is currently accessing the database. The tables can not be deleted, and the code hangs.

Does anyone know how I can change the code to free up the database at the time the import macro is run, ensuring that no one can access the application unless the code finishes running?

 

[MichaelRed]

There are several (to numerous) threads in Tek-Tips elaborating this theme. Do a search. I THINK key word(s) include:

Kick
Logged
Users
Maintenance


see thread705-79802 specifically, but there are many others -some of which include specific code / tables /structures.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[vbajock]

problem might be
SELECT * FROM MSysObjects

Try this. if the table doesn't exist it will drop it into the error handler, which will force a resume next:

Public Function GetFiles()
on error goto err_h

MyPath = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name)))
MyName = Dir(MyPath, vbDirectory)
Do While MyName <> &quot;&quot;
If Right(MyName, 4) = &quot;.txt&quot; Then
strTblName = Left(MyName, Len(MyName) - 4)
strSpec = LCase(strTblName) & &quot; Import Specification&quot;

Currentdb.execute &quot;DROP TABLE &quot;+strTblName

strFile = MyPath & MyName
DoCmd.TransferText acImportDelim, strSpec, strTblName, strFile
End If
MyName = Dir
Loop

exit function

'*******
err_h:
'*******

resume next





End Function