Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MikeB
Constraint Violating Yak Guru
387 Posts |
 Posted - 2003-03-18 : 11:17:22
|
| I was told that it is bad practice to have a persistent connection to the mdb file in my application. My application is developed using C++, ADO, and Access .mdb. I went ahead and changed my application to open a connection when needed and close the connection when no longer needed. I also tried to create disconnected recordsets for information most commonly viewed. I have found that the application is much slower in some parts due to the opening and closing of the connection. I have also become a little worried about using disconnected recordsets because I don't know how it will use up the system resources! I know my design probably isn't the best but.....Anyway, what happens if I leave a persistent connection and just use disconnected recordsets? What are the dangers of using a persistent connection? Can it cause a corrupted mdb file?Any thoughts are welcome!Mike B |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-18 : 19:56:24
|
| The biggest problem is Access, not ADO. Access has a HORRIBLE locking scheme, and keeping persistent connections open on an Access database WILL corrupt data sooner or later. This is especially true in a multi-user environment where the Access DB is kept on a shared network drive. If people are opening the MDB file on the network directly, ooops, might want to stop that and use linked tables instead. Anything over 10 users who browse the database all day will cause problems. Memo fields especially; I strongly urge that you look at getting rid of any memo fields and converting them to regular text, especially if you have any irreplaceable info in them. Also, if your Access DB is over 100 MB, you're living on borrowed time and certainly poor performance.The problems are lessened with ADO vs. DAO, but you really can't eliminate them...Access is a very shaky foundation for a multi-user database app. As far as disconnected recordsets, they really don't have a lot of overhead unless you are opening 100,000+ row tables with them. Even then, it's probably better than leaving a connection open to the network file all day. You don't need to close the connection either, just set the recordset's ActiveConnection to Nothing. The larger problem is that Access can't really use connection pooling, so you'll have at least one connection per client computer. If you find that you need to repair the Access DB more than once or twice a month, time to get SQL Server, or anything else for that matter, but you're at the point where Access is gonna bomb permanently and you'll lose data.One thing to be aware of, even if it doesn't seem like a big problem, it is deceptive in it's scope creep. The repair utility in Access works well, but there are some levels of corruption that are cumulative and reach a level that cannot be repaired, ultimately you'll have a dead MDB file that even the JetComp utility won't fix, and you'll be stuck creating a brand new MDB file and importing everything into it. You'll also lose some data too, usually it's only a row or two, or sometimes only a single memo field on a single row, but it's gone for good. Sometimes though you can lose an entire table or a whole raft of forms and reports. The best thing you can do is, THE MINUTE you start seeing problems with Access, start looking at alternatives. Don't cross your fingers and put all your eggs in the repair database basket, it WILL break at some point. The sooner you can migrate away from Access the easier it will be, it will only get harder the longer you wait. |
 |
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2003-03-19 : 15:18:13
|
quote:
Access has a HORRIBLE locking scheme, and keeping persistent connections open on an Access database WILL corrupt data sooner or later.
quote:
You don't need to close the connection either, just set the recordset's ActiveConnection to Nothing
Kind of confused here? If I disconnect the recordset, and don't close the connection, can the database still become corrupt?In one sense you say I should close the connection, and in another sense you are saying I don't need to. So, if I have a connection at application startup. I create recordsets, disconnect them by setting their active connections to NULL, view all day, and disconnect the connection at application closing. This should not be a problem?My problem is this. The users are consistently adding/editing/and removing records from the database. If I establish a connection before an operation, execute the operation, and disconnect each time, this creates a laggy sense in the applcation. It takes a second to connect, a second to connect + to execute + a second to disconnect.The applcation would run much smoother if I can get away with just a single persistent connection / application instance! Mike B |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-19 : 20:18:30
|
Yeah, sorry about that. It's more of a difference in the way ADO works than DAO, which is what I'm familiar with in Access. DAO didn't allow for disconnected recordsets, so the connection had to be opened the entire time. Basically, when the connection is open and active, Access will lock rows as you browse and edit them. Where it f---s up completely is RELEASING those locks (in short: it almost never does). In ADO, with a disconnected recordset, you're not locking the row in Access, all of the editing and such is done on the client's machine, with their client-side disconnected recordset. It's also better at OBEYING the lock settings you specify, if you say pessimistic, it uses pessimistic (BTW, avoid pessimistic locking like the plague, go with batch optimistic as much as possible). Then, when you want to commit the changes, you reattach the connection to your recordset and then use UpdateBatch. ALL of the edits and stuff are done in one operation, and any collisions or conflicts are reported and resolved in one batch, instead of hundreds of single-row batches that never release their locks.As far as a single connection, it won't work unless you are using a server-side COM layer that abstracts the data access entirely, like a web app would (essentially forcing disconnected recordset use, and possibly open connection-grab data-close connection) You really can't do that kind of client-server programming with Access anyway, it's not that kind of architecture.If you haven't thought about it, you should consider looking at MSDE for your database. It's basically SQL Server Lite, and you can seamlessly migrate from it to a full-blown SQL Server. It definitely resolves a lot of problems that Access has and will work better with ADO. And it's FREE too.  |
|
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2003-03-20 : 16:28:31
|
quote:
As far as a single connection, it won't work unless you are using a server-side
Ok, well, I will tell you what I am doing, and if you can tell me, what risks do I have?Sequence of events:1) Start application2) Establish connection to mdb3) Create disconnected recordsets4) On command to add/edit/delete a record - Set disconnected recordsets connection
- aad/edit/delete recordset
- set recordsets connection to nothig
5) Repeat 4 until done with application6) close connection7) close applicationThis appears to work well and fast. If I constantly connect and disconnect the _ConnectionPtr object, the application will run really slow. PS. We currently run Novell network. Will MSDE work with this? I am trying to get Win2000 and SQL Server.Mike BEdited by - MikeB on 03/20/2003 16:30:11 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-20 : 20:24:25
|
| You're doing exactly what you should be doing to get the most out of Access with the least headache. But you must still be vigilant in keeping the MDB file compacted and repaired, if not every day, then at least once a week. As long as the connections will ultimately close and disconnect you won't see any major problems.Yes, SQL Server supports a Novell client library, but unless you've disabled TCP/IP you can also use it as well. MSDE will also be able to use either network protocol. On Win2000 machines though, you need to install the NWClient service in order for the Novell library to work. |
|
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2003-03-21 : 09:58:14
|
quote:
You're doing exactly what you should be doing to get the most out of Access with the least headache
That is what I like to hear!  Thanks for your clarifications!Mike B |
|
|
|
|
|
|
|
|
Dangers or persistent connection?