DB opens as read only and won't let me write

[GHawk]

Hi, I'm using an Access DB from the server and I'm communicating with it via Vb Net.

I have 4 PCs that uses the program communicates with the DB. And they work kind of ok, but at some point the DB "protects" and becomes a Read Only DB.

I'm using ADO connections. Like:

Dim CMDa As New OleDb.OleDbCommand("SELECT xxxx FROM xxxx WHERE xxxx)", frm_kit.CNEff)
Dim DR As OleDb.OleDbDataReader
frm_kit.CNEff.Open()
DR = CMDa.ExecuteReader
If DR.Read Then
MsgBox("ERRORR")


frm_kit.CNEff.Close()
Exit Sub
End If
frm_kit.CNEff.Close()

What I want to know is if there is a way to "restore" the DB if ever gets to Read Only, or to check the state of the DB?..

Regards!

[ranman256]

It could be leaving a lock file even tho nobody is in it. Delete the lock file. *.laccdb
(if it wont delete, then someone IS in it.)

[Bob Fitz]

Is the db split, with one back end (data) file, and each user having a copy of the front end on their own machine?

[ssanfu]

Hi, I'm using an Access DB from the server and I'm communicating with it via Vb Net.

I have 4 PCs that uses the program communicates with the DB. And they work kind of ok, but at some point the DB "protects" and becomes a Read Only DB.

When you say "using Access from the server.... 4 PCs" does that mean that the dB is split and EACH user (PC) has a copy of the dB on their computer and the BE is on the server? Or everyone opens the same dB on the server?

[GHawk]

Hi, Bob Fitz and ssanfu.

Well I don't know anything about split DB. :-\

What I have is 1 DB and all and every PC opens the same DB. (Maybe I'm doing something wrong here that I didn't know).

Is that a problem?.. how can I fix that?... I just know one thing, I don't want to have 4 DBs to work with but at the same time I may be missing some way to do a better job on handling the DB.

[Bob Fitz]

Hi, Bob Fitz and ssanfu.

Well I don't know anything about split DB. :-\

What I have is 1 DB and all and every PC opens the same DB. (Maybe I'm doing something wrong here that I didn't know).

Is that a problem?.. how can I fix that?... I just know one thing, I don't want to have 4 DBs to work with but at the same time I may be missing some way to do a better job on handling the DB.

If you have a db with multiple users, it must be split into a backend file which holds just the tables and a front end which has everything else (forms, reports, queries, modules). Then put a copy the front end on each users machine and link each front end to the back end file. Search google. There are thousands of pages on this subject. Access even has a wizard that will split the db for you.
We can't say that this is definitely the cause of your current problem but sooner or later it will cause you problems (corruption) if you carry on using it as it is. If you wear a blindfold every time you cross a road you can be sure that it will eventually end in disaster. How soon that will happen, or how bad it will be is anyones guess.

[ssanfu]

See:
http://www.techrepublic.com/blog/10-...cess-database/
http://allenbrowne.com/ser-01.html
http://www.hitechcoach.com/index.php...split-database

[GHawk]

Bob Fitz,ssanfu

Not sure if I got it right about Front End and Back End. (Or even about split DB)

This is what I have:

a) VB Net program on each PC.
b) Access DB on server.
c) Each program connects to the only DB to write/read information.
d) I need the DB to run reports from all the information gathered by all PCs.

After reading the information from your replies I still have a couple questions: (not trying to bother you but trying to understand what you are trying to tell me)
1) Do I have a front end-back end project?..
2) Should I need to split the DB?
3) Does access allow multiple user to write to the same table at the same time?... (Or this could be the reason why the DB gets into Read Only mode)

Regards!

[June7]

This is split db.

The backend is the Access file with data.

The frontend is VB.Net project file. Why VB.Net and not Access?

Each user needs to run their own copy of the VB.Net project file which you seem to have done.

Multiple users can write to the same table at the same time (or this can be prevented with table level locking), however, they cannot write to the same record.

[GHawk]

June7 , thanks for the clarification.

Now. knowing that I may have everything "correct". What possible can be causing the DB to a "read only state"?...
One of the things I found is that the DB says it has been open and I need to wait for the user to finish the process. But all programs are off and the PCs has been rertarted, still I can't open the DB to write.

With this in mind I have a couple questions:
a) How can I know who has the DB open?... (Assuming I don't have access to the server.)
b) Is there a way to close the connection, if there is one open?.. Which type of connection can do this?... What would be the best way to close unknown connections?
c) It is possible that a server job can create this problem?

[June7]

I've never used VB.Net as a frontend.

a. I have a Users table in the backend. When user opens their frontend, code saves value into their user record for the network ComputerName. This way I know which computers are running the app. When they close the app, the ComputerID is removed from the record. I know who the user is by grabbing their network UserName.

Environ("USERNAME")
Environ("COMPUTERNAME")

b. Closing the db on another computer would be possible but not a nice thing to do. AFAIK, can only kill the Access program so if user has multiple Access db files open, they would all shut down.

c. sorry, no idea

[GHawk]

Hi,

I made some changes on the SW. And it becomes more stable. still there is a sporadic error that I need to catch.

Thanks for the help, I learn more things despite of being unable to fix the issue.