Hi can anyone shed some light on this error?

The database has been placed in a state by user 'admin' on machine 'BHD020'
that prevents it from being opened or locked.

I have 20 pcs each running a different copy of an excel sheet that does a
Microsoft Query refresh once every 15 minutes. They do this 7 days per week
and 24 hours per day. Occasionaly we would receive this error and have to
manualy click "Cancel" or "OK". If you select OK it then does an ODBC login.

We are using office 2003, in Windows XP. The access database is located on a
network server. There is no pattern to the error. It happens all times of the
day and night and on different machines, "BHD020" is just one example.

Is this because two pcs are performing an ODBC login at the same time? Can I
solve the error by changing the way they log in? For example: Is it possible
to change them all to read only? Would this let multiple pcs in at the same
time without conflicting with each other.

Another possible theory is that after the refresh they are not relinquishing
control of the database:

Only one of the PC's needs to write to the Access database.

I write a file called "Access On Hold.xls" to the network and before
refreshing the Microsoft Query I check for this file. When the various
clients see this file they wait. I've actualy got a 6 minute delay waiting
for all pc to complete their refresh before the Master PC begins it's update.
Next the Master PC clears all the records in the access file then copies a
worksheet from Excel (loaded with math and additional queries to pull info
from our business systems) into access. Erases the "Access On Hold.xls" file
then closes the table involved.

I'm actualy Opening the table, deleting all records, then paste appending in
new ones. Then I close the table.

After I introduced the "Close" of the table, the Master PC stopped showing
up in these errors.

However, the rest of the clients are performing a refresh of a Microsoft
Query. I need a way to close their connection after they refresh. I think
this will solve the problem.

I'm wondering if it wouldn't be better to use a macro to open the database
and retrieve the data rather than using a query. the same SQL commands can
be used and you can loop in the macro until the data is retrieved. You can
use an OnTime Event to run the macro every 15 minutes. I can't guarantee
this method will not have the same problem.

"Keith" wrote:

Hi can anyone shed some light on this error?

The database has been placed in a state by user 'admin' on machine 'BHD020'
that prevents it from being opened or locked.

I have 20 pcs each running a different copy of an excel sheet that does a
Microsoft Query refresh once every 15 minutes. They do this 7 days per week
and 24 hours per day. Occasionaly we would receive this error and have to
manualy click "Cancel" or "OK". If you select OK it then does an ODBC login.

We are using office 2003, in Windows XP. The access database is located on a
network server. There is no pattern to the error. It happens all times of the
day and night and on different machines, "BHD020" is just one example.

Is this because two pcs are performing an ODBC login at the same time? Can I
solve the error by changing the way they log in? For example: Is it possible
to change them all to read only? Would this let multiple pcs in at the same
time without conflicting with each other.

Another possible theory is that after the refresh they are not relinquishing
control of the database:

Only one of the PC's needs to write to the Access database.

I write a file called "Access On Hold.xls" to the network and before
refreshing the Microsoft Query I check for this file. When the various
clients see this file they wait. I've actualy got a 6 minute delay waiting
for all pc to complete their refresh before the Master PC begins it's update.
Next the Master PC clears all the records in the access file then copies a
worksheet from Excel (loaded with math and additional queries to pull info
from our business systems) into access. Erases the "Access On Hold.xls" file
then closes the table involved.

I'm actualy Opening the table, deleting all records, then paste appending in
new ones. Then I close the table.

After I introduced the "Close" of the table, the Master PC stopped showing
up in these errors.

However, the rest of the clients are performing a refresh of a Microsoft
Query. I need a way to close their connection after they refresh. I think
this will solve the problem.

Joel,
Thanks for you suggestion. It gave me several ideas of how to solve this.
I can actually do the same thing with my Automation Anywhere software. It's
capable of performing the SQL statement as well.

So, I'll either do it as you've suggested or within the Automation as well.

Thanks again,

Keith


"joel" wrote:

I'm wondering if it wouldn't be better to use a macro to open the database
and retrieve the data rather than using a query. the same SQL commands can
be used and you can loop in the macro until the data is retrieved. You can
use an OnTime Event to run the macro every 15 minutes. I can't guarantee
this method will not have the same problem.

"Keith" wrote:

Hi can anyone shed some light on this error?

The database has been placed in a state by user 'admin' on machine 'BHD020'
that prevents it from being opened or locked.

I have 20 pcs each running a different copy of an excel sheet that does a
Microsoft Query refresh once every 15 minutes. They do this 7 days per week
and 24 hours per day. Occasionaly we would receive this error and have to
manualy click "Cancel" or "OK". If you select OK it then does an ODBC login.

We are using office 2003, in Windows XP. The access database is located on a
network server. There is no pattern to the error. It happens all times of the
day and night and on different machines, "BHD020" is just one example.

Is this because two pcs are performing an ODBC login at the same time? Can I
solve the error by changing the way they log in? For example: Is it possible
to change them all to read only? Would this let multiple pcs in at the same
time without conflicting with each other.

Another possible theory is that after the refresh they are not relinquishing
control of the database:

Only one of the PC's needs to write to the Access database.

I write a file called "Access On Hold.xls" to the network and before
refreshing the Microsoft Query I check for this file. When the various
clients see this file they wait. I've actualy got a 6 minute delay waiting
for all pc to complete their refresh before the Master PC begins it's update.
Next the Master PC clears all the records in the access file then copies a
worksheet from Excel (loaded with math and additional queries to pull info
from our business systems) into access. Erases the "Access On Hold.xls" file
then closes the table involved.

I'm actualy Opening the table, deleting all records, then paste appending in
new ones. Then I close the table.

After I introduced the "Close" of the table, the Master PC stopped showing
up in these errors.

However, the rest of the clients are performing a refresh of a Microsoft
Query. I need a way to close their connection after they refresh. I think
this will solve the problem.