ADODB error message in access 2000

[zandiT]

hello
I'm running XP and using access 2003
Every time i try to run my database i get the following error

Runtime error -2147467259 (80004005) The database has been placed in a state by Admin, that prevents the machine from being opened or locked

I can just ignore the message by clicking end, and it will work, but i'm worried it will give me problems later because it will be accessed by all the users in the company. sometimes i get a message saying i cannot save any the changes that I've made.

When the error pops up, if i click on debug it highlights the "ado.Open strConnection" . as shown below

Dim ado As ADODB.Connection

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" &                                 CurrentProject.Path & "\Table1.mdb;"

ado.Open strConnection

i've tried changing the references but that didn't help the situation and i havn't installed or uninstalled any software of late.

Thankyou for your help

[FishVal]

Hello.

That usually happens because somebody didn't save module having written the code in.
While db contains any unsaved design changes it is "placed in a state by ... on machine ... that prevents it from being opened or locked".

BTW, you should not create ADO connection, because you already have one available via CurrentProject. Connection property.

Regards,
Fish

[zandiT]

Hi FishVal
Thankyou for the quick reply. so if i don't use ADO then how else can i get the records in the table to my unbound form. i tried using DAO but that was a disater. should i remove the CurrentProject part and continue with ADO?

Okay here's the deal:
i have 2 tables and one form. all i want to do is to access the tables in the form so the users can manipulate the records whichever way they want and save the changes to the main table.
originally i had created the form using the wizard and bound the table(s) to the form and it worked fine with a lot of VBA. the only problem is i found out just recently that the database is going to be placed on the server and accessed by the whole company. different users with different levels of access etc. so i decided to "restructur e" it and now its a lot harder than i thought. i'm very new to this and a bit overwhelmed. As an expert, do you think i should leave it the way it was originally and just pile on more VBA code to further its functionality or is there another way out? your adice is much appreciated
ZandiT

[zandiT]

Hi FishVal
Thankyou for the quick reply. so if i don't use ADO then how else can i get the records in the table to my unbound form. i tried using DAO but that was a disater. should i remove the CurrentProject part and continue with ADO?

Okay here's the deal:
i have 2 tables and one form. all i want to do is to access the tables in the form so the users can manipulate the records whichever way they want and save the changes to the main table.
originally i had created the form using the wizard and bound the table(s) to the form and it worked fine with a lot of VBA. the only problem is i found out just recently that the database is going to be placed on the server and accessed by the whole company. different users with different levels of access etc. so i decided to "restructur e" it and now its a lot harder than i thought. i'm very new to this and a bit overwhelmed. As an expert, do you think i should leave it the way it was originally and just pile on more VBA code to further its functionality or is there another way out? your adice is much appreciated
ZandiT

[FishVal]

Hi FishVal
Thankyou for the quick reply. so if i don't use ADO then how else can i get the records in the table to my unbound form. i tried using DAO but that was a disater. should i remove the CurrentProject part and continue with ADO?

O_O

What did you mean by this?
I've just suggested you not to open a new ADO connection, because you already have one permanently accessible via CurrentProject. Connection property.

Okay here's the deal:
i have 2 tables and one form. all i want to do is to access the tables in the form so the users can manipulate the records whichever way they want and save the changes to the main table.
originally i had created the form using the wizard and bound the table(s) to the form and it worked fine with a lot of VBA. the only problem is i found out just recently that the database is going to be placed on the server and accessed by the whole company. different users with different levels of access etc. so i decided to "restructur e" it and now its a lot harder than i thought.
i'm very new to this and a bit overwhelmed. As an expert, do you think i should leave it the way it was originally and just pile on more VBA code to further its functionality or is there another way out? your adice is much appreciated
ZandiT

That all depends on what you actually want to enhance in your database. ;)
Could you specify what are your concerns with current configuration?

[zandiT]

its been made mainly from wizards and vba code here and there and i thought it wouldn't be best practice to implement a database with such a structure for a medium sized multi user environment, with concurrency and speed issues etc. thats why i started using Ado and made a big mess of things. i don't even know much about but i got some stuff from the net a a few books but it didn't help much.
i just want the database to function in a multi user environment and i was looking for the best way to develop it. using the wizards and vba or starting from fresh with ADO or DAO?

[FishVal]

its been made mainly from wizards and vba code here and there and i thought it wouldn't be best practice to implement a database with such a structure for a medium sized multi user environment, with concurrency and speed issues etc. thats why i started using Ado and made a big mess of things. i don't even know much about but i got some stuff from the net a a few books but it didn't help much.
i just want the database to function in a multi user environment and i was looking for the best way to develop it. using the wizards and vba or starting from fresh with ADO or DAO?

There are several ways to upsize Access database to multiuser enviroment.
The following depends on your network performance. So, check it first - simply by opening database from network drive.

• Access database could work pretty fine with somewhat about 3-7 users just as single file on shared drive.
• If it is not your case, then try to split your database to single back-end and multiple front-ends - according to specification Access supports up to 255 concurrent users.
• And at last you may think about upsizing to SQL server.

P.S. The only advantage you may get from creating unbound form is that you could perform a remote connection - connect to database only when user queries or updates data. And that is not a simple coding. ;)

P.P.S.

... database is going to be placed on the server ...

BTW, what does "server" state for?

[zandiT]

Thankyou so much for the advice
i'm going to leave it as it was before, when it was working, then split it and see where it goes from there. the network speed is pretty good and there will be around 30 people accessing the database. sorry i didn't understand your question about "server" state?? thanks a bunch

[FishVal]

Thankyou so much for the advice
i'm going to leave it as it was before, when it was working, then split it and see where it goes from there. the network speed is pretty good and there will be around 30 people accessing the database.

Well. I almost sure that with good network speed, 30 users and only 2 tables overall performance will be good enough.

sorry i didn't understand your question about "server" state?? thanks a bunch

Is it file server or just a shared folder?
Or, though I almost sure it is not, is it database server?

Regards,
Fish

[zandiT]

Its a file server, thats why i'm going to split it.i'll cross that bridge when i get there, right now i have to make it function. different levels of access and the rest of it. thanks again