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

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 = ""
--
AndyP


"jam-the-learner" wrote:

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" wrote:

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.
--
AndyP


"Andy" wrote:

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
--
end of question and code examples
Andy

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?

"AndyP" wrote:

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 = ""
--
AndyP


"jam-the-learner" wrote:

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" wrote:

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.
--
AndyP


"Andy" wrote:

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
--
end of question and code examples
Andy