SiriusProjects

Hi All,

I have a number of Access Databases that are sitting on a network server. We occasionally get "file already in use errors" when someone tries to open the database. I have read about ensuring that the folder permissions, etc are set correctly, however, these errors sometimes occur when there is a server issue. For instance, sometimes the server will suddenly go offline (users will get a working offline message). This results in the database connection being cut off prematurely without properly closing down. Is there anything I can do that would allow the database to shutdown correctly when this happens or perhaps a different way of opening/closing the database in the applications?

Thanks.

Gruff

Typically when someone new to database programming enters the arena they do not understand that Access is a low end "file based" database solution and cannot normally handle multiple users well.

Usually they write their apps so that their database is bound to components. This mean that as long as the program is running the connection is open and others may not connect with the database.

Another scenario is that a global unbound database connection is opened for the duration of the program life. This has the same effect as above.

The best way IMHO is to open the database connect for the shortest time possible and close it immediately when your process is complete. If you need to write large amounts of data then I would use batch mode to blast the content into the database in one shot instead of dribbling it in from program loops.

---

If you want to handle the network issue I would sincerly consider moving your Access databases into a true Network mounted SQL Server database. There is a free express version that can hold multiple databases much larger than Access.

As a service the worst that can happen is you lose a session. The SQL Server will remain running unless when you say "Offline" you Network Server shuts down. The service also can report errors if the SQL Sever is not found or not available for some reason.

Multiple users can log in at the same time.

Long story short though I'd fix the server no matter what you decide to do as that is a nasty thing to have to deal with for any solution.

SiriusProjects

Hi Gruff,

Thanks for the reply.

I totally agree with your point regarding only opening and closing the database for the shortest time possible. I have often wondered about the pros and cons of this.

When I started writing apps that connect to databases, I did indeed bind VB controls to the database as you mentioned. I then moved on to using connection strings and SQL statements. My first applications would open the database when the program was loaded and keep it open until the program was closed. I then started writing apps that would only open the database when needed within the application. I now use the latter option all the time when writing a database app, with few exceptions.

As for your second point, whilst I would certainly like to move to a network mounted SQL Server database, I'm afraid I am not in control of making that decision in our company. Our IT Manager has talked about it on a number of occasions, but it never seems to get to the top of the priority list and there is also the matter of convincing our Accounts Exec/IT Director to give us the budget and resources to make these changes. So for now, I'm stuck with having to make the best of what we do have.

Gruff

No purchasing budget needed.

You realize that there is a Free Microsoft 2008 SQL Server Express.

You download it, Install it on your network server, and start using it. No Legal restrictions that I am aware of. You can have multiple database where each DB has a limit of up to 10 Gig.

You can also install it locally on your desktop to play with it and re-create your Access DB content. It is possible to connect from anywhere on your network from your desktop but your machine would need to be running all the time. (I'd recommend moving it to your server when you feel comfortable doing so.)


You would also need the Free
SQL Server Management Studio Express tool to view and edit the content.

The Microsoft SQL Server Management Studio Express (SSMSE) provides a graphical management tool for SQL Servers

loquin

I agree that SQL Server express would probably be a good solution to this issue.

Another solution, (that is probably just as much work,) would be to use disconnected recordsets.

Set the cursorlocation to client, static cursor type, and batch update lock type.

Open your recordset, then, set the recordset's active connection to nothing.

The data remains in the recordset for your use, but you can close the connection altogether. It's not needed again until it's time to update any changes made locally, back to the database.

Ref this thread, and specifically, the book mentioned there.

*****

The advantage that a database server has, is that IT controls access to the underlying data, and the underlying data will always be consistent. With access, as you've seen, if the file access/update process is interrupted, your database can be corrupted. But, by limiting the exposure of the open connection (as per above,) you can limit the opportunity for corruption as well. You can't eliminate it, like you could with a server, so you'll still need to be prepared for repairing corrupted databases, but you can reduce the number of incidents.

Meanwhile, if you haven't been keeping track of the downtime associated with corrupt data files, you should. As well as track the time you've spent in fixing the files. This can give you a baseline for the true cost of continuing to use the Access data files, and justification for moving forward to a database server-based approach


As Gruff mentioned, "Free" is a good way get started with a database server. In addition to SQL Server express, Oracle, and IBM (DB2) offer limited (normally database size) server-based solutions for free. In addition, PostgreSQL is a very powerful, open source enterprise database server that is 100 percent free of charge, and can handle terrabyte-scale database applications. MySQL can as well, but, it isn't as safe with your data as PostgreSQL is, and licensing costs in commercial applications can be an issue.