Front End being locked

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
 
D

Dirk Goldgar

BruceS said:
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?


I'm not sure exactly what is going on, but I wonder if it is necessary for
you to create a new workspace, and whether that is somehow implicated. Why
not use the current, default workspace?

Set db = DBengine.OpenDatabase(txtDatabase, , True)
 
B

BruceS

Dirk,

Been a while! Thanks for replying.

Creating the workspace was a long shot, a guess. The app has had some
strange behavior. It was telling me that no records existed in a table when
I could display it and see them, so I thought it may be confusing the local
and remote databases. I have since removed that part with no change in
results.

Anyway, the logic is working fine, creating a valid text file as it should,
but it still locks it from any changes after a single run. I still can't
save changes made to the local tables via the form, and it still warns me
that I won't be able to save any changes when I go into design mode. It
thinks that another Admin is accessing the front end even though only one
instance of it is running. Have to close it and restart it after every run.

I've tried the normal things: copying into a blank database, checking the
references, removing the "auto correct" settings, but it still does this.

As a note, I installed the 2007 runtime a couple of weeks ago when I was
testing a install routine. Uninstalled it when I starting having this
problem, but it did not change the result. That's the only software change
that should affect anything for this front end.

Any thoughts?

Thanks,
Bruce

Dirk Goldgar said:
BruceS said:
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?


I'm not sure exactly what is going on, but I wonder if it is necessary for
you to create a new workspace, and whether that is somehow implicated. Why
not use the current, default workspace?

Set db = DBengine.OpenDatabase(txtDatabase, , True)


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
 
D

Dirk Goldgar

BruceS said:
Dirk,

Been a while! Thanks for replying.

Creating the workspace was a long shot, a guess. The app has had some
strange behavior. It was telling me that no records existed in a table
when
I could display it and see them, so I thought it may be confusing the
local
and remote databases. I have since removed that part with no change in
results.

Anyway, the logic is working fine, creating a valid text file as it
should,
but it still locks it from any changes after a single run. I still can't
save changes made to the local tables via the form, and it still warns me
that I won't be able to save any changes when I go into design mode. It
thinks that another Admin is accessing the front end even though only one
instance of it is running. Have to close it and restart it after every
run.

I've tried the normal things: copying into a blank database, checking the
references, removing the "auto correct" settings, but it still does this.

As a note, I installed the 2007 runtime a couple of weeks ago when I was
testing a install routine. Uninstalled it when I starting having this
problem, but it did not change the result. That's the only software
change
that should affect anything for this front end.

Any thoughts?


I don't see anythig obvious, but I'm sure it's not just random. Is there
any code that is executed between the sections of code you posted -- I saw
some ellipses in there? Is this database (and its associated remote
database) one that you could send me to examine?
 
B

BruceS

Sure! Email me at bruce at windingcreekdesign dot com with where to send them.

Thanks!!

Bruce
 
D

Dirk Goldgar

BruceS said:
It thinks that another Admin is accessing the front end even though only
one instance of it is running. Have to close it and restart it after
every run.


I'm not sure why this is necessary, but working with the copy you sent me,
I've found that explicitly identifying the (existing) workspace when calling
OpenDatabase does the trick. Instead of:
Set db = OpenDatabase(txtDatabase, , True)

or even this that I recommended before:
Set db = DBEngine.OpenDatabase(txtDatabase, , True)

Try this wherever you open the database:

Set db = DBEngine.Workspaces(0).OpenDatabase(txtDatabase, , True)

For me, that eliminates the problem.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top