The database has been placed in a state .....

BittenApple · Post by **BittenApple** » 13 Jul 2017, 04:26

Hello Hans,

When I work with my database and open the Microsoft inside Access as an control object, I get this error:
The database has been placed in a state by user "admin" on Machine "...." that prevents if from being opened or locked.

When I googled this error, one of the causes was that the database was not split. I am still testing it and after it is finalized, I will split.

I want to make sure this error is caused by not splitting database not something else. If it is something else, I can fix while I am building it.

Regards,
Bita

Post by **HansV** » 13 Jul 2017, 05:36

I don't understand "open the Microsoft inside Access as an control object". What do you mean by that?

BittenApple · Post by **BittenApple** » 14 Jul 2017, 16:19

Hello Hans,
I was not able to explain properly.

This is how it went.
Since I couldn't create charts I wanted I went to Excel
I created a table with two fields, auto number and a field with OLE Data type
I created an Excel workbook, I made a connection between Excel and Access (Get External Data/ From Microsoft Query/ followed the rest of steps), I open a query from Microsoft Access into Excel
I opened Microsoft Access database and open table containing OLE field. I dragged the Microsoft Excel containing Access query into the field whose data type was OLE
I started a blank form and added the field with OLE Datatype/ I place the data source for the form the table containing OLE field.
I ran the form; the chart in the Microsoft Excel was shown on the form. I right clicked on the form and selected worksheet object and open, I was able to see the worksheet on the form.
I faced so many problems and each time I got a different error messages which "The database has been placed in a state...." was one of them.

To go around this:
First question:
I created a form and inserted hyperlinks to that excel sheet so end user can click on the link and the links take them the Excel sheet and they can see the chart.
Now my concern is that if I split the database and end users each have a copy of front end, does this link work?
Second question:
The database has been placed in a state .. is generated because the database has not been split yet.
Much more thanks!!!
Bita,

Post by **HansV** » 14 Jul 2017, 16:31

The problems you were running into are because you created a link from Access to Excel and also a link from Excel to Access.

If the Excel workbook is in a fixed folder (for example the folder containing the backend), the hyperlinks should work for everyone.

BittenApple · Post by **BittenApple** » 14 Jul 2017, 19:17

Hans,
omg, how interesting the link works for everybody.
What suggests that I have created a link from Access to Excel and vice a versa?

I have opened a query belonging to Excel and then I have inserted a chart for that query in Excel. Then I have opened that Excel through OLE in Access (We can rename or delete that Excel workbook later)
Do you think the problems I am running into is coming form this?

I am so grateful to you!!!!
Bita

Post by **HansV** » 14 Jul 2017, 19:36

Your Excel chart refers to a query in Access. This creates a link from Excel to Access, "locking" the database.

BittenApple · Post by **BittenApple** » 19 Jul 2017, 03:26

Hello Hans,
Re: The database has been placed in a state .....

Your Excel chart refers to a query in Access. This creates a link from Excel to Access, "locking" the database.
Again, I get this message: Re: The database has been placed in a state .....
when I hit ok/ I get this message: The query did not run, or the database table could not be opened.
Check the database server or contact your database administrator. Make sure the external database is available and has not been moved or reorganized, then try the operation again.

I googled this message and read some about this.
I had nulls on the fields that I have grouped on them; I think that caused issues and I test it several time and I couldn't see any error. Can null fields be causing these errors?
Re: The database has been placed in a state .....; Can this be cause by nulls in query? Or is it caused by not split database?

Please advise me.
Regards,
Bita

Post by **HansV** » 19 Jul 2017, 03:34

No, I don't think that Nulls have anything to do with it. I think it is because you have a link from Excel to Access.

BittenApple · Post by **BittenApple** » 19 Jul 2017, 04:09

Hans,
I added some record to the table. I clicked on the link on the form and I opened Microsoft Excel. it worked only first time; on the task bar is showing a message: "waiting for the query to run"; on the second test, I saw the same message.

Yes, you are right; nulls have nothing to do with it. How can I solve this issue if I want to open the queries in Database Access from Excel and display charts in Excel?
Is splitting the database will take care of this issue?

What is your idea?

=========================
Also, when I click on connection properties, I can see the connection file points to previous location of database, because I moved the database and placed it on a drive that everybody has access to it. Previously, it was located on my own person drive. Could this be a cause?

Regards,
Bita

Post by **HansV** » 19 Jul 2017, 04:24

I don't think splitting the database will help.
If the connection points to the original location of the database, it's best to create it anew to point to the new location.

BittenApple · Post by **BittenApple** » 19 Jul 2017, 05:01

Hans,
I will create a new connection and make it to point to the current drive not the one in which I originally created the database.
Can this be causing the error message?
Please advise me.
Bita

Post by **HansV** » 19 Jul 2017, 05:30

BittenApple · Post by **BittenApple** » 19 Jul 2017, 05:44

Hans,

From Hans: "No, I don't think that Nulls have anything to do with it. I think it is because you have a link from Excel to Access."
Is there a work around to avoid the link from Excel to Access?

Regards,

Bita

Post by **HansV** » 19 Jul 2017, 05:54

If possible, create the chart in Access itself, as discussed in this thread. This imposes some limitations on the chart, but it'll avoid the problems you're running into.

BittenApple · Post by **BittenApple** » 19 Jul 2017, 06:03

Hans,

Yes, I have to switch to Access and discuss it with stockholders.
I will work on it though and I will update you.
Thank you so much for all your support.

Bita,