Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > Database and Reporting > Access Database Issues


Reply
 
Thread Tools Display Modes
  #1  
Old 04-25-2012, 06:29 AM
SiriusProjects SiriusProjects is offline
Regular
 
Join Date: May 2006
Posts: 88
Default Access Database Issues


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.
Reply With Quote
  #2  
Old 04-25-2012, 09:58 AM
Gruff's Avatar
Gruff Gruff is offline
Bald Mountain Survivor

Super Moderator
* Expert *
 
Join Date: Aug 2003
Location: Oregon, USA
Posts: 6,352
Default

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.
__________________
Burn the land and boil the sea
You can't take the sky from me


~T
Reply With Quote
  #3  
Old 04-26-2012, 05:30 AM
SiriusProjects SiriusProjects is offline
Regular
 
Join Date: May 2006
Posts: 88
Default

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.
Reply With Quote
  #4  
Old 04-26-2012, 09:02 AM
Gruff's Avatar
Gruff Gruff is offline
Bald Mountain Survivor

Super Moderator
* Expert *
 
Join Date: Aug 2003
Location: Oregon, USA
Posts: 6,352
Default

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
__________________
Burn the land and boil the sea
You can't take the sky from me


~T

Last edited by Gruff; 04-26-2012 at 09:15 AM.
Reply With Quote
  #5  
Old 05-03-2012, 02:03 PM
loquin's Avatar
loquin loquin is offline
Google Hound

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,402
Default

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.
__________________
Lou
"I have my standards. They may be low, but I have them!" ~ Bette Middler
"It's a book about a Spanish guy called Manual. You should read it." ~ Dilbert
"To understand recursion, you must first understand recursion." ~ unknown

Last edited by loquin; 05-03-2012 at 02:25 PM.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->