If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Form, 3211 Error, could not lock table, already used in form.
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 |
#2
|
|||
|
|||
Form, 3211 Error, could not lock table, already used in form.
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" 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 |
#3
|
|||
|
|||
Form, 3211 Error, could not lock table, already used in form.
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." -- Andy "Klatuu" wrote: 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" 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 |
#4
|
|||
|
|||
Form, 3211 Error, could not lock table, already used in form.
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. "Andy" wrote: 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." -- Andy "Klatuu" wrote: 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" 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 |
#5
|
|||
|
|||
Form, 3211 Error, could not lock table, already used in form.
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, -- AndyP "Klatuu" wrote: 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. "Andy" wrote: 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." -- Andy "Klatuu" wrote: 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" 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 |
#6
|
|||
|
|||
Form, 3211 Error, could not lock table, already used in form.
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" wrote: 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, -- AndyP "Klatuu" wrote: 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. "Andy" wrote: 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." -- Andy "Klatuu" wrote: 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" 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 |
#7
|
|||
|
|||
Form, 3211 Error, could not lock table, already used in form.
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 -- AndyP "Klatuu" wrote: 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" wrote: 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, -- AndyP "Klatuu" wrote: 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. "Andy" wrote: 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." -- Andy "Klatuu" wrote: 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" 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 |
#8
|
|||
|
|||
Form, 3211 Error, could not lock table, already used in form.
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" wrote: 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 -- AndyP "Klatuu" wrote: 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" wrote: 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, -- AndyP "Klatuu" wrote: 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. "Andy" wrote: 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." -- Andy "Klatuu" wrote: 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" 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 |
#9
|
|||
|
|||
Form, 3211 Error, could not lock table, already used in form.
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 |
#10
|
|||
|
|||
Form, 3211 Error, could not lock table, already used in form.
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 |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Combo Box NotInList - How To Add Data To Underlying Table | 10SNUT | Using Forms | 19 | July 8th, 2005 09:12 PM |
Access combo box-show name, not ID, in table? | write on | New Users | 30 | April 30th, 2005 09:11 PM |
strategy for data entry in multiple tables | LAF | Using Forms | 18 | April 25th, 2005 04:04 AM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
dlookup | miaplacidus | Using Forms | 9 | August 5th, 2004 09:16 PM |