Preventing database from being placed in a state that prevents it from being opened or locked?

**Vita** · 03-24-2023, 09:36 AM

I have been working on a database/tool to open tables from a sql server and modify them but I need it to be opened on more than 1 computer at a time. Several people will be using it at the same time.
I keep getting this error when I try to open up a second instance on a separate pc.
"The database has be placed in a state by user 'name' on machine 'name' that prevents it from being opened or locked."

I checked the options and open mode is set to shared and record locking is set to no locks.

Can anyone help me fix this? I am aware I might have just designed everything wrong from the start but I am hoping there is a fix.

**Gicu** · 03-24-2023, 10:21 AM

When you say

open tables from a sql server and modify them

do you actually mean opening the (linked) table or running DDL SQL pass-through queries to modify the tables (that is how I would do it). Here is an example how to change bit fields in SQL to not accept NULL and set the default to 0 (to avoid the write conflict error):

Code:

Public Function vcCheckBoxesSetDefaultZero()

Dim tdf As DAO.TableDef, db As DAO.Database, fld As Field
Dim sTable As String, sField As String


Set db = CurrentDb


For Each tdf In db.TableDefs


    If Left(tdf.Name, 4) <> "msys" Then
    
        'only do SQL Server tables
        If InStr(tdf.Connect, "SQL Server") > 0 Then
            For Each fld In tdf.Fields
    
                If fld.Type = dbBoolean Then
                sTable = tdf.Name
                sField = fld.Name
                'call update to replace nulls with zeroes
                Call vcAlterTable(tdf.Name, "UPDATE [" & sTable & "] SET [" & sField & "] = " & 0 & " WHERE [" & sField & "] IS NULL;", "tblAgents")
                'now lets alter the table
                Call vcAlterTable(tdf.Name, "ALTER TABLE [" & sTable & "] ALTER COLUMN [" & sField & "] BIT NOT NULL", "tblAgents")
                Call vcAlterTable(tdf.Name, "ALTER TABLE [" & sTable & "] ADD CONSTRAINT DF_" & sTable & "_" & sField & " DEFAULT 0 FOR " & sField & ";", "tblAgents")
                'Call vcAlterTable(tdf.Name, "ALTER TABLE [" & sTable & "] ALTER COLUMN [" & sField & "] BIT NOT NULL ;", "NAMES")
    
                End If
    
            Next fld
        End If
    End If


Next tdf


   MsgBox "DONE"




End Function
Public Function vcAlterTable(strSourceTable As String, sAlterSQL As String, sUseConnectionStringFrom As String)
Dim db As DAO.Database
Dim qdExtData As QueryDef
Dim strSQL As String


On Error Resume Next


Set db = CurrentDb
If ObjectExists("QUERY", "sqlALTER_TABLE") Then DoCmd.DeleteObject acQuery, "sqlALTER_TABLE"
strSQL = sAlterSQL
Set qdExtData = db.CreateQueryDef("sqlALTER_TABLE")


qdExtData.Connect = CurrentDb.TableDefs(sUseConnectionStringFrom).Connect
qdExtData.ReturnsRecords = False
qdExtData.SQL = strSQL


CurrentDb.QueryDefs("sqlALTER_TABLE").Execute


'lets refresh the link
CurrentDb.TableDefs(strSourceTable).RefreshLink


qdExtData.Close
db.Close
Set db = Nothing
'MsgBox "ALTER TABLE STATEMENT COMPLETED - PLEASE CHECK THE TABLE IN DESIGN VIEW"
End Function

Cheers,

**ArviLaanemets** · 03-24-2023, 10:44 AM

Are your users trying all to use the same Access front-end, or do they have their own copies of it?

**Vita** · 03-24-2023, 11:06 AM

Originally Posted by Gicu

When you say do you actually mean opening the (linked) table or running DDL SQL pass-through queries to modify the tables (that is how I would do it). Here is an example how to change bit fields in SQL to not accept NULL and set the default to 0 (to avoid the write conflict error):

Code:

Public Function vcCheckBoxesSetDefaultZero()

Dim tdf As DAO.TableDef, db As DAO.Database, fld As Field
Dim sTable As String, sField As String


Set db = CurrentDb


For Each tdf In db.TableDefs


    If Left(tdf.Name, 4) <> "msys" Then
    
        'only do SQL Server tables
        If InStr(tdf.Connect, "SQL Server") > 0 Then
            For Each fld In tdf.Fields
    
                If fld.Type = dbBoolean Then
                sTable = tdf.Name
                sField = fld.Name
                'call update to replace nulls with zeroes
                Call vcAlterTable(tdf.Name, "UPDATE [" & sTable & "] SET [" & sField & "] = " & 0 & " WHERE [" & sField & "] IS NULL;", "tblAgents")
                'now lets alter the table
                Call vcAlterTable(tdf.Name, "ALTER TABLE [" & sTable & "] ALTER COLUMN [" & sField & "] BIT NOT NULL", "tblAgents")
                Call vcAlterTable(tdf.Name, "ALTER TABLE [" & sTable & "] ADD CONSTRAINT DF_" & sTable & "_" & sField & " DEFAULT 0 FOR " & sField & ";", "tblAgents")
                'Call vcAlterTable(tdf.Name, "ALTER TABLE [" & sTable & "] ALTER COLUMN [" & sField & "] BIT NOT NULL ;", "NAMES")
    
                End If
    
            Next fld
        End If
    End If


Next tdf


   MsgBox "DONE"




End Function
Public Function vcAlterTable(strSourceTable As String, sAlterSQL As String, sUseConnectionStringFrom As String)
Dim db As DAO.Database
Dim qdExtData As QueryDef
Dim strSQL As String


On Error Resume Next


Set db = CurrentDb
If ObjectExists("QUERY", "sqlALTER_TABLE") Then DoCmd.DeleteObject acQuery, "sqlALTER_TABLE"
strSQL = sAlterSQL
Set qdExtData = db.CreateQueryDef("sqlALTER_TABLE")


qdExtData.Connect = CurrentDb.TableDefs(sUseConnectionStringFrom).Connect
qdExtData.ReturnsRecords = False
qdExtData.SQL = strSQL


CurrentDb.QueryDefs("sqlALTER_TABLE").Execute


'lets refresh the link
CurrentDb.TableDefs(strSourceTable).RefreshLink


qdExtData.Close
db.Close
Set db = Nothing
'MsgBox "ALTER TABLE STATEMENT COMPLETED - PLEASE CHECK THE TABLE IN DESIGN VIEW"
End Function

Cheers,

It pulls data from linked tables and then updates data using passthrough queries.

Originally Posted by ArviLaanemets

Are your users trying all to use the same Access front-end, or do they have their own copies of it?

They are all going to be trying to use the same front-end. The DB is not split. I want to avoid having to make a version for each person incase I need to update it in the future.

**Welshgasman** · 03-24-2023, 11:10 AM

They are all going to be trying to use the same front-end. The DB is not split. I want to avoid having to make a version for each person incase I need to update it in the future.

Big, BIg, BIG mistake.

However if you are using SQL server, it must be split surely?

but they still need their own copy of the FE.

**Vita** · 03-24-2023, 11:40 AM

Originally Posted by Welshgasman

Big, BIg, BIG mistake.

However if you are using SQL server, it must be split surely?

but they still need their own copy of the FE.

the SQL server is on a separate computer so I suppose this could be considered a frontend?
Its a simple dropdown and textbox that searches for a record using 3 primary keys and updates some fields and creates some records using a passthrough.
I was hoping I could just have this one frontend and have everyone use a shortcut to it so I can avoid having to update several different frontends incase they request additional features.

**Welshgasman** · 03-24-2023, 12:23 PM

No, definitely not the way to do it.

**Vita** · 03-24-2023, 12:56 PM

Originally Posted by Welshgasman

No, definitely not the way to do it.

How should I have done it?? Why isn't this the way? Is there a way to do it as I am describing?

**madpiet** · 03-24-2023, 01:02 PM

Originally Posted by Vita

How should I have done it?? Why isn't this the way? Is there a way to do it as I am describing?

Having all the users share the same front end is a recipe for disaster.
I supposed you could use group policy or whatever to push the new front end out to everyone, or put it in a shared folder where users can download it

**Vita** · 03-24-2023, 01:42 PM

Originally Posted by madpiet

Having all the users share the same front end is a recipe for disaster.
I supposed you could use group policy or whatever to push the new front end out to everyone, or put it in a shared folder where users can download it

The alternative I am trying to avoid is just make a copy of the front end for everyone and put them on the network drive and give everyone shortcuts to their respective frontend (which is what we currently do with a separate database).

**Welshgasman** · 03-24-2023, 01:42 PM

The easiest for you atm, is to give everyone a shortcut which copies the latest version to each users computer each time and opens that db.
A more sophisticated method, is for the db to check its version and update automatically, but little steps first.

**Vita** · 03-24-2023, 02:21 PM

Originally Posted by Welshgasman

The easiest for you atm, is to give everyone a shortcut which copies the latest version to each users computer each time and opens that db.
A more sophisticated method, is for the db to check its version and update automatically, but little steps first.

How should I go about doing this? I like this! I wanna jump straight to the sophisticated version tbh.

**Welshgasman** · 03-24-2023, 02:48 PM

Well I used Bob Larsen's Auto Update code
You will need to google for that

But he even mentions another coder
http://www.autofeupdater.com/

Vlad (@Gicu) also has one I seem to recall
http://forestbyte.com/ms-access-util...G3a5bsYECzLvNI

**isladogs** · 03-24-2023, 02:57 PM

Web Version Checker (isladogs.co.uk)

**Welshgasman** · 03-24-2023, 03:28 PM

That is another nice method.

Preventing database from being placed in a state that prevents it from being opened or locked?

Thread Tools

Preventing database from being placed in a state that prevents it from being opened or locked?

Similar Threads

PC Admin Account Places Database in a State Preventing an Attempted Action

Database placed in state preventing it to be opened

The database has been placed in a state by user XYZ that prevents it from being opened or locked

the database has been placed in a state by user admin on machine that prevents it from being opened

Help! Database has been placed in a state by user _ on machine _ that prevents

Posting Permissions