Locked table / timing issue with macros

[Mike]

I use a form with 2 buttons. Button 1 runs a module that lists files
into a table. If all files are imported, I have a "OK" message box
telling me that all files are imported.
After this, I have button 2 which runs a macro. The macro actually
runs a make table query.
Everything works fine if I use the 2 buttons. Now I want to have this
automated with one button press. As soon as I put everything in one
macro (or vba), Access tells me that it couldn't lock the table. I
think I have a timing issue.

Anyone knows what I could do?

 

[John W. Vinson]

Anyone knows what I could do?

You could post the code so that we can see it and help you debug it, for
starters!

John W. Vinson [MVP]

 

[Mike]

You could post the code so that we can see it and help you debug it, for
starters!

John W. Vinson [MVP]

Hi John,
Sorry I didn't come back earlier, but I had trouble accessing the
google groups. If I post everything, It's going to be a little longer,
so that's the reason I didn't post everything earlier. But here it is:

I have combined the two buttons I was talking about earlier into one.
The button runs the following event on click:

Private Sub Command2_Click()
LocateFile ("*.*")
DoCmd.RunMacro "Update documents tbl"
End Sub

The LocateFile module imports all filenames (including paths) into a
table. I had a hard time finding this code in the groups, so I want to
share it again as others might find it useful, too.

'imports contents (filename incl. filepaths) of DRAFT, ACTIVE and
ARCHIVE folder
'inserts all found files into the Import_Filenames_tbl

Function LocateFile(strFileName As String)

DoCmd.Hourglass (hourglasson)
Dim vItem As Variant
Dim db As DAO.Database
Dim strsourcefilepath As String

'strsourcefilepath is looked up in table settings under column
source
strsourcefilepath = DLookup("[source]", "Settings")
Set db = CurrentDb
With Application.FileSearch
.Filename = strFileName
.LookIn = strsourcefilepath
.SearchSubFolders = True
.Execute

'deletes current Import_filenames_tbl before inserting the
"reread" files
CurrentDb.Execute "DELETE * from Import_Filenames_tbl",
dbFailOnError

For Each vItem In .FoundFiles
db.Execute _
"INSERT INTO Import_Filenames_tbl (Filename) " & _
"VALUES(" & Chr(34) & vItem & Chr(34) & ")", _
dbFailOnError
Next vItem
End With

'simple messagebox so that user can see that filename import is
finished
'MsgBox "All filenames imported", vbInformation
'Set db = Nothing
DoCmd.Hourglass (hourglassoff)

End Function

Ok, now I have all filenames in my table and now want to extract all
data from the filename to create a documents table. I use a make-table-
query and call it with a my macro "Update documents tbl":

set warnings = no
open query: create documents table
set warnings = yes
close form: get files (this is the form where my button is located)
open form: show documents (form in continuous view to display the
created documents table)

Ok, so this is all I do. It works like I just described it.

Now I want to locate my button on the last opened form (which gets all
data from the documents table).
I've changed the macro so that it first closes the form and then does
all the rest posted above.
Unfortunately, everytime I do that, I get a "locked table" message
after all file names are imported and the macro stops with an error in
the vba line where I call the macro.

Do you have any ideas how I can solve this. I have also tried to put a
wait() in my button-code, but the error still occurs.

Thanks, Mike

 

[Mike]

You could post the code so that we can see it and help you debug it, for
starters!

John W. Vinson [MVP]

Hi John,
Sorry I didn't come back earlier, but I had trouble accessing the
google groups. If I post everything, It's going to be a little longer,
so that's the reason I didn't post everything earlier. But here it is:

I have combined the two buttons I was talking about earlier into one.
The button runs the following event on click:

Private Sub Command2_Click()
LocateFile ("*.*")
DoCmd.RunMacro "Update documents tbl"
End Sub

The LocateFile module imports all filenames (including paths) into atable. I had a hard time finding this code in the groups, so I want to
share it again as others might find it useful, too.

'imports contents (filename incl. filepaths) of DRAFT, ACTIVE and
ARCHIVE folder
'inserts all found files into the Import_Filenames_tbl

Function LocateFile(strFileName As String)

DoCmd.Hourglass (hourglasson)
Dim vItem As Variant
Dim db As DAO.Database
Dim strsourcefilepath As String

'strsourcefilepath is looked up intablesettings under column
source
strsourcefilepath = DLookup("[source]", "Settings")
Set db = CurrentDb
With Application.FileSearch
.Filename = strFileName
.LookIn = strsourcefilepath
.SearchSubFolders = True
.Execute

'deletes current Import_filenames_tbl before inserting the
"reread" files
CurrentDb.Execute "DELETE * from Import_Filenames_tbl",
dbFailOnError

For Each vItem In .FoundFiles
db.Execute _
"INSERT INTO Import_Filenames_tbl (Filename) " & _
"VALUES(" & Chr(34) & vItem & Chr(34) & ")", _
dbFailOnError
Next vItem
End With

'simple messagebox so that user can see that filename import is
finished
'MsgBox "All filenames imported", vbInformation
'Set db = Nothing
DoCmd.Hourglass (hourglassoff)

End Function

Ok, now I have all filenames in mytableand now want to extract all
data from the filename to create a documentstable. I use a make-table-
query and call it with a my macro "Update documents tbl":

set warnings = no
open query: create documentstable
set warnings = yes
close form: get files (this is the form where my button is located)
open form: show documents (form in continuous view to display the
created documentstable)

Ok, so this is all I do. It works like I just described it.

Now I want to locate my button on the last opened form (which gets all
data from the documentstable).
I've changed the macro so that it first closes the form and then does
all the rest posted above.
Unfortunately, everytime I do that, I get a "lockedtable" message
after all file names are imported and the macro stops with an error in
the vba line where I call the macro.

Do you have any ideas how I can solve this. I have also tried to put a
wait() in my button-code, but the error still occurs.

Thanks, Mike

I just tried to get everything in vba and have put the following in:

Private Sub Command87_Click() --> this would be my button on the main
form
LocateFile ("*.*")
DoEvents
DoCmd.Close acForm, "show_documents_frm"
DoCmd.Close acQuery, "Show_documents_qry"
DoCmd.Close acTable, "Documents_tbl"
DoCmd.OpenQuery "Create_documents_tbl_qry" --> this is where the code
stops because it can not lock the documents table.
DoCmd.OpenForm "show_documents_frm"

End Sub

What is the difference in pressing a button and the closing the form
and first closing the form, switching to another form (totally
independent) with a button and pressing it there?