B
BruceS
Hi, all!
Have a front-end in 2003 that reads table structures in another .mdb. The
single form is bound to a table in the front end that keeps the last settings
(paths, etc.) The front end also holds a table where I store the extracted
structures.
When the front end loads, it connects to and re-reads the list of table defs
in the remote database. That works fine, and nothing in the front end is
locked.
When a button is clicked, it reads the structure of the selected table and
loads it into a table in the front end. This routine is causing a lock that
will not allow me to save the data in the form, make changes to the form or
alter anything else in the front end. I have to exit Access and restart it
to be able to change anything. For instance, when I try to switch back to
design mode on the form it tells me that I do not have exclusive access so
any changes will be lost.
I've never run into this before, so I tried setting up a separate workspace
for the remote database. Same problem. Don't know where to go from here.
Here are the related variables from the form header:
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim ws As Workspace
Dim rst As DAO.Recordset
Dim f As DAO.Field
Dim t As DAO.TableDef
Dim idx As DAO.Index
Dim idxfld As DAO.Field
This sub runs successfully when the form is opened and, afterward, the front
end is NOT locked:
....
Set ws = CreateWorkspace("", "Admin", "", dbUseJet)
Set db = ws.OpenDatabase(txtDatabase[the remote db], , True)
For Each td In db.TableDefs
If Left(td.Name, 4) <> "MSys" And Left(td.Name, 1) <> "~" Then
myStr = myStr & ";" & Chr(34) & td.Name & Chr(34)
End If
Next
db.Close
Build_Exit:
Set db = Nothing
Set ws = Nothing
....
When button is clicked this code executes. The lock seems to be occuring
when the "idx" loop runs.
....
'Open database & table.
Set ws = CreateWorkspace("", "Admin", "", dbUseJet)
Set db = ws.OpenDatabase(txtDatabase, , True)
Set t = db.TableDefs(Me.cboTables)
tblName = "`" & t.Name & "Test`"
numFields = t.Fields.Count
numIndexes = 0
For Each idx In t.Indexes
If Not idx.Foreign Then numIndexes = numIndexes + 1
Next idx
DBEngine.Idle [see below]
'Load field list.
myStr = "DELETE * FROM NewFields;"
DBEngine(0)(0).Execute myStr, dbFailOnError
' DoCmd.RunSQL myStr
DBEngine.Idle
....
SOMETIMES it gives me a run-time error 3734 - The database has been placed
in a state by user 'Admin' on machine '[my computer id]' that prevents it
from being opened or locked. - when it hits the first DBEngine.Idle command.
If I comment out that command, it will SOMETIMES give me the same error when
it runs the DELETE SQL.
I'm buffaloed on this one. Can anyone help?
Thanks,
Bruce
Have a front-end in 2003 that reads table structures in another .mdb. The
single form is bound to a table in the front end that keeps the last settings
(paths, etc.) The front end also holds a table where I store the extracted
structures.
When the front end loads, it connects to and re-reads the list of table defs
in the remote database. That works fine, and nothing in the front end is
locked.
When a button is clicked, it reads the structure of the selected table and
loads it into a table in the front end. This routine is causing a lock that
will not allow me to save the data in the form, make changes to the form or
alter anything else in the front end. I have to exit Access and restart it
to be able to change anything. For instance, when I try to switch back to
design mode on the form it tells me that I do not have exclusive access so
any changes will be lost.
I've never run into this before, so I tried setting up a separate workspace
for the remote database. Same problem. Don't know where to go from here.
Here are the related variables from the form header:
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim ws As Workspace
Dim rst As DAO.Recordset
Dim f As DAO.Field
Dim t As DAO.TableDef
Dim idx As DAO.Index
Dim idxfld As DAO.Field
This sub runs successfully when the form is opened and, afterward, the front
end is NOT locked:
....
Set ws = CreateWorkspace("", "Admin", "", dbUseJet)
Set db = ws.OpenDatabase(txtDatabase[the remote db], , True)
For Each td In db.TableDefs
If Left(td.Name, 4) <> "MSys" And Left(td.Name, 1) <> "~" Then
myStr = myStr & ";" & Chr(34) & td.Name & Chr(34)
End If
Next
db.Close
Build_Exit:
Set db = Nothing
Set ws = Nothing
....
When button is clicked this code executes. The lock seems to be occuring
when the "idx" loop runs.
....
'Open database & table.
Set ws = CreateWorkspace("", "Admin", "", dbUseJet)
Set db = ws.OpenDatabase(txtDatabase, , True)
Set t = db.TableDefs(Me.cboTables)
tblName = "`" & t.Name & "Test`"
numFields = t.Fields.Count
numIndexes = 0
For Each idx In t.Indexes
If Not idx.Foreign Then numIndexes = numIndexes + 1
Next idx
DBEngine.Idle [see below]
'Load field list.
myStr = "DELETE * FROM NewFields;"
DBEngine(0)(0).Execute myStr, dbFailOnError
' DoCmd.RunSQL myStr
DBEngine.Idle
....
SOMETIMES it gives me a run-time error 3734 - The database has been placed
in a state by user 'Admin' on machine '[my computer id]' that prevents it
from being opened or locked. - when it hits the first DBEngine.Idle command.
If I comment out that command, it will SOMETIMES give me the same error when
it runs the DELETE SQL.
I'm buffaloed on this one. Can anyone help?
Thanks,
Bruce