Form, 3211 Error, could not lock table, already used in form.

[Andy]

Help, I have a "3211 Error", could not lock table, because it is already
used in form.
How to refresh a table shown on a form, when it is altered by a VBA
procedure called from the same form?

VBA code follow

Thanks
Andy
-------------------------------------------

FORM Subroutines

Private Sub Form_Load()
'Subroutine that shows 3211 Error, because table is shown in form?
Call Mod_TableFields
End Sub
-------------------------------------------
Private Sub TableName_Click()
Response = MsgBox("Marry: " & Me.TableName & " ? ", vbYesNo, "Marry a File")

If Response = vbYes Then
'Call MARRIED(Me.TableName)
' Subroutine that shows 3211 Error, because table is shown in form?
Call Mod_TableFields
Refresh
End If

End Sub
-------------------------------------------
MODULE 1 SUBROUTINES

Sub Mod_TableFields()

On Error GoTo Error_Handler

Dim ThisDB As DAO.Database
Dim RS As DAO.Recordset
Set ThisDB = CurrentDb

' Drop field table, so it can be rebuilt
ThisDB.Execute "DROP TABLE T_FIELDS;"

STEP1_BYPASS:

'Create clean empty field table
ThisDB.Execute "CREATE TABLE T_FIELDS (Table_name TEXT, Field_name TEXT,
CNTRecs double );"

' Open that clean empty table
Set RS = ThisDB.OpenRecordset("T_FIELDS")

' Go through each of the Access table definitions
For Each tdf In ThisDB.TableDefs

' Go through each of the Access field definitions
For Each fld In tdf.Fields
RS.AddNew
RS!Table_name = tdf.Name
RS!CNTRecs = tdf.RecordCount
RS!FIELD_NAME = fld.Name
RS.Update
Next fld
Next tdf

'Close the new field table
RS.Close

'Empty the reference
Set RS = Nothing

' Delete the table file, to rebuild it fresh and clean
' 3211 Error shown because table is shown in form?
ThisDB.Execute "DROP TABLE T_TABLES"

' Error handler skips this step when table not dropped because of 3211
error.
' Query & Group field information
ThisDB.Execute "SELECT Table_name, CNTRecs, Count(Table_name) AS CNTFields
INTO T_TABLES " & _
" FROM T_FIELDS GROUP BY Table_name, CNTRecs ORDER BY Table_name; "

STEP2_BYPASS:

' Leave subroutine
Exit Sub

'ERROR HANDLER
Error_Handler:
If Err <> 0 Then
MsgBox Err.Number & " " & Err.Source & "->" & Err.Description, ,
"Error"

If Err = 3376 Then
Resume STEP1_BYPASS
End If
If Err = 0 Then
Resume STEP2_BYPASS
End If
End If

End Sub

 

[Klatuu]

That is not what the error says, Andy. It says it is in use by another
person or process. That means another user has it open, or some piece of
code, or a macro is executing and using the table.
I think you need to change some parameters in your database. From your menu
bar:
Tools->Options->Advances tab
Default Open Mode = Shared
Default Record Locking = No Locks
Open database using record-level locking = Checked

That should help.

 

[Andy]

Klatuu,
That is what I have my settings set to. So that did not fix the error.
The person or process that the error refers to is me and my form. This is a
standalone Workstation with only one Access app running with one form and no
other tables open.
I think what I need is to do:
1) release the table before I process it
2) ReOpen the table once it has been processed
How would this be done in VBA for a Access form?
Also, which event is best for calling a VBA procedure to build a table that
will be displayed on a form, I would like to keep the processing of this
table all within one form.
I apologize for not posting the entire error message which is:
"Error 3211 DAO.Database->The database engine could not lock table
'T_TABLES' because it is already in use by another person or process."

 

[Klatuu]

Andy,
Thanks for the info. Looking at your code, I don't guess I understanding
what you are trying to do. Can you give me a narrative of what you are
attempting to accomplish? Creating and Dropping tables programmatically is
not a common event, although there are some valid reasons to do it. I am
thinking there may be another way to go about it.
What I do suspect; however, is that the table you are erroring on is the
record source for your form and this would cause that problem.

 

[AndyP]

Klatuu,
(I changed my name to AndyP, since Andy already exists in this forum.)
Narrative of what I am trying to do:
Using a minimalist approach I want to see in one form within Access:
1) what Access tables exist in the current DB.
2) their field(column) count.
3) their record(row) count.
4) to be able to click on a table name to run a VBA procedure using a that
table.
5) refresh the formy to show the same table information with any new tables
and any changes that have occurred.
The form and VBA procedure do all of this except that Access thinks the
forms displayed table is locked.
Thanks,

 

[Klatuu]

Here is a basic routine that does what you want.
Sub ListTableNames()
Dim dbf As Database
Dim rst As Recordset
Dim tdfs As TableDefs
Dim tdf As TableDef
Dim lngRecs As Long
Set dbf = CurrentDb
Set tdfs = dbf.TableDefs
Debug.Print "Database Contains " & tdfs.Count & " Tables"
For Each tdf In tdfs
Set rst = dbf.OpenRecordset(tdf.Name, dbOpenSnapshot)
If rst.RecordCount = 0 Then
lngRecs = 0
Else
rst.MoveLast
rst.MoveFirst
lngRecs = rst.RecordCount
End If
rst.Close
Set rst = Nothing
Debug.Print "Table " & tdf.Name & " Contains " & tdf.Fields.Count &
" Fields And " _
& lngRecs & " Records"
Next tdf
Set tdf = Nothing
Set tdfs = Nothing
Set dbf = Nothing
End Sub

What you are currently doing is overkill.

 

[AndyP]

Klatuu,

Sorry, No that does not do what I want.

1st I get a error "Run-time error '13':Type mismatch" on the line
Set rst = dbf.OpenRecordset(tdf.Name, dbOpenSnapshot)

2nd your routine does not provide the key essential feature to select and
process a file that is on the list. And then to rebuild the list. I am not
that experienced to flip into VBA evertime to run a process, easier from a
form for now.

If I find a solution I will try to post.

Thanks

 

[Klatuu]

It was only an example of how to get the info you said you want. Don't know
why you got the error, it ran for me.
Reconsider the approach you are taking. There is absolutely no need to be
creating and deleting tables the way you are doing it.

 

[AndyP]

Yeah, I found the solution!

The forms recordsource had to be changed before that table could be
manipulated.

' before calling any vba procedure that needs exclusive use of the displayed
table
' in this example table "T_TABLES"
' Clear the record source
Form_F_Tables.RecordSource = ""
' then call your procedure that requires exclusive use of the table
Call Mod_TableFields
' then restablish the connection to the table
Form_F_Tables.RecordSource = "T_TABLES"

Pat on the back to myself for 4+ hours of investigation and many more of
noodling.

 

[jam-the-learner]

I am having a similar problem to you, albeit at a more basic level as I'm not
that experienced at VBA (as you can see from the code).

I am trying to update a database via a command button on the main form, that
uses tabbed sub forms.

The database gets its data from paradox data tables copied our company's
third-party software. To allow users to use the system and others to copy
data from the system I use a bat file to update the base data files before
using linked tables and make queries during the login process to update the
database in my system.

When I try to run the code below I get the same error about the table being
locked by a user or process. As you can see I have tried adding a pause
incase the files are still being copied but this does not seem to be the
problem. I have also tried the solution you suggested, but I get an error
that an object is required.

Can anyone suggest anything else.

code:
------------------------------------------------------------

Dim response
Dim stDocName As String
Dim stLinkCriteria As String


response = MsgBox("Are you sure that you want to update xxx with Customer
data from xxx?", vbYesNo, "Perform Update")
If response = vbYes Then

‘pause software to let any pending work to be completed
Sleep (5000)

‘close active form
DoCmd.Close

‘close all active forms
Do While Forms.Count > 0
DoCmd.Close acForm, Forms(0).Name
Loop

‘pause software to let any pending work to be completed
Sleep (30000)

‘delete existing file
Kill "c:\folder\file\ DATA.DB"

‘replace with new file
FileCopy "J:\Folder\new_DATA.DB", " c:\folder\file\ DATA.DB "

‘pause software to let any pending work to be completed
Sleep (40000)

DoCmd.SetWarnings False


stDocName = "Make_DATA"
DoCmd.OpenQuery stDocName, acNormal, acEdit

‘pause software to let any pending work to be completed
Sleep (35000)

stDocName = "Make_DATA_SUMMARY"
DoCmd.OpenQuery stDocName, acNormal, acEdit

‘pause software to let any pending work to be completed
Sleep (35000)

stDocName = "qry_Update_ Status"
DoCmd.OpenQuery stDocName, acNormal, acEdit

‘pause software to let any pending work to be completed
Sleep (35000)

‘open up main form when finsihed
stDocName = "main-form"
DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.SetWarnings True


Else
End If

 

[AndyP]

Try using two quotes together with no space.

When I use a space I get the error:
Run-time error '2580':
The record source ' ' specified on this form or report does not exist.

Doesn't work(has space): Form_F_Tables.RecordSource = " "
Works! (no space): Form_F_Tables.RecordSource = ""

 

[jam-the-learner]

I'm using no space between the quote marks as well.

To be on the safe side I have tested to make sure that the file being copied
has completed before moving on by comparing the size of the files in the
original location and the copied file via a message box and this indicates
that the copy process has completed. But then I get eth same error straight
after clicking okay.

Have you got any more suggestions?