ADO problem when database is open

[duluter]

Hi everyone.

I was just wondering, is it a problem to try and establish a connection and grab a recordset via ADO to an Access database that someone else has open? Could you hurt the database if you try to do this?

The reason I ask is because I was writing a macro in Outlook this morning. My macro was connecting to a database on our server and everything seemed ok. A little while later I navigated to the database in Windows explorer, opened it up, and there was no data in the database--every record in every table was gone. This freaked me out because I had just been accessing that database this morning and everything was fine. I found out that another staff member had the database open because she was adding some data to it at about the same time. So either this is a big coincidence that the data got hammered on the same morning that I was fiddling with it via ADO, or the other staff member inadvertently caused the data crash somehow. Either way, scary. A tmp database was on the server that still had all the data in it, so crisis averted.

Note that in my ADO execute statement, I was only using a SELECT statement--I never asked the database to change any existing data.

Is it possible that my actions messed our database up? Or coincidence?


Duluter

 

[Banana]

1) Is the database split?

2) How are you opening the ADO connection?

 

[duluter]

1. The database is not split.

2. In VBA, using a connection string, like this:

Dim con As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim sConString As String
Dim sSQLString As String

Set con = New ADODB.Connection
Set rs1 = New ADODB.Recordset

sConString = "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=X:\FolderName\MyDatabase.mdb"
con.Open sConString

sSQLString = "SELECT Field1 FROM tblMyTable ORDER BY Field1"
'rs1.Open sSQLString, con, adOpenKeyset, adLockReadOnly
Set rs1 = con.Execute(sSQLString)


Duluter

 

[Banana]

If the database is to be used among several users, it simply must be split. There is no way around it; using an unsplit database merely mean it's a question of when it will get corrupted.

Split it then see if it works afterward.

Where is the code located? In the database or in the Outlook?

 

[duluter]

The code is in Outlook. The database is opened very infrequently, so I figured I could get away with it. I will split it.

Is this a known issue with ADO and Access? Is it documented somewhere that the database could get messed up in this type of situation?


Thanks,

Duluter

 

[Banana]

I don't know about this specific instance being documented, but I can tell you that as a rule, when there will be more than one user (or application or an automated script for that matter) using the same database, it has to be split. Frequency or number of concurrent users really doesn't matter.

 

[duluter]

Thanks, Banana. I am in complete agreement with you. I will split the db as soon as I can. But I still have an unsatisfied feeling because I don't really know if what I did caused the problem or not. It seems very strange to me that accessing an already open db via ADO would cause all of the data from every table to disappear. It seems ridiculous to me that this could happen. That's not to say that I don't believe it happened, but I think still think it's ridiculous. If this could happen, it seems like ADO could be a little smarter about connecting to open databases (i.e., let the program know somehow so it can be dealt with in the code). Or am I asking too much?


Duluter

 

[Banana]

I'm afraid I don't have any more specific answers, and do sympathize with you about how weird this is.

As for ADO being 'smart', I don't think ADO even knows or cares as the responsibility of managing database generally falls with the daemon*, not with the access technology. At best, ADO may be able to ask and report whether a record is locked but even so, it still has to rely on the daemon telling what state the record is currently in.

* Strictly speaking, Jet is more like a file server than a database engine, so there is no daemon administrating the database files, which is a big part of why Jet isn't as robust as any other RDBMS which has daemons performing the file operations and separating the task of requesting and modifying data from actual read/write to the hard drive. With Jet, when there are concurrent users using a file, it's akin to have several daemon working on the same file that's not split, and that's just bad mojo. Therefore, a possible explanation may been that when your staff member connected, the first thing Jet does is ask for all relevant pages in providing 'blueprint' of the database but the file was in use already and writer wrote where it shouldn't have, and you ended up with the same pages now empty. But that's just a speculation.

 

[datAdrenaline]

I do not beleive that your connection via ADO caused the issue you have experienced. If you were still able to open the database AFTER all this occurred, then that gives credibility to the conclusion that your ADO connection and SQL statements were probably not the culprit. Splitting the database is a good practice so as to isolate the data and prevent corruption caused by two people modifying the DESIGN of the database (either tables or the UI). Which brings up the possibility that another user was changing the design of something whilst you were connected, which can be a bad thing. As far as capability goes, an mdb/accdb file can maintain (by specification) is 255 simultaneous connections, so ... it is highly unlikely that your connection did anything because of connection overload! The "practical" limit is about 20 connections to a datafile.

However, that being said, Access (and other db's/file formats that offer multiple connections and a sharing scheme similar to Access's) have that undesired failure mode of corruption every so often. I have seen mdb's lose all the table information and become completly useless simply because a PC went into sleep mode while a db was opened.

Corruption is, often times, and inexplainable thing ... but .. typically surrounded with ".. it was probably because of <blah> <blah> ...", but that doesn't mean the same steps will cause the corruption again! There are, however, common circumstances that are associated with corruption ... wireless networks, encryption (especially over wireless networks), powering off PC's while the db is opened, multiple connections while designing objects, the AutoCorrect feature.... the list goes on ....good times eh?

 

[duluter]

datAdrenaline:

Good info. Thank you for posting. I so much wish I could migrate our tables to SQLServer and keep all our front ends in Access. I work for a small company, so this is probably not a reality in the near future. Still, splitting the database and backing up regularly should do the trick for now.


Thanks,

Duluter

(Note: It's highly unlikely that anyone was modifying the design of the database when I was accessing it via ADO. The user that would have been accessing it is not database savvy and would have had no reason to modify the design.)

 

[Banana]

Just for your information, if you do have a legitimate need for a full-on RDBMS solution, you don't necessarily have to pay a pretty pop to get one. On one hand, there's express edition of SQL Server, which is free to use but capped in performance and scope. Good for quick upgrade without gob of cash. On other hand, there's open source that are distributed for free such as MySQL, Firebird and PostgreSQL. While not from Microsoft, this actually matter very little and they do a great job at what they do.

 

[duluter]

Banana:

Yes, lots of options. Unfortunately, I am a remote employee with limited administrator rights on the network, and we do not have a full-time IT person, so setting up and administering an RDBMS becomes problematic. Perhaps in the future one of these solutions will be possible for us.


datAdrenaline:

You mentioned that the practical limit on the number of connections to an Access database is about 20. I'm not sure how to phrase this, but does that limit assume that the connections are remote, as opposed to actually having the physical file open in Access? In other words, if one person has the file open, can approximately 19 others still access the database via ADO? Does actually having the file itself "open" in Access affect the database's capability to accept other connections? Does what I'm asking make sense?


Duluter

 

[datAdrenaline]

>> I'm not sure how to phrase this, but does that limit assume that the connections are remote <<

One thing to realize ... Access is a user interface to a JET database file ... so "Access" is really NOT a database .. its a database manipulation application, and the database Engine defaults to JET (or ACE in the case of Access 2007). So ... the connection limit of 255 is a JET file format property, what that means is that if you open the datafile.mdb in Access, thats One connection. If you open it using ADO, that's 2. Note that a single Access application can EASILY have Two connections claimed on the phyical limit of 255. But its also important to know that in the Access UI, when you open two or more tables from the database window, a new connection is NOT established with each open, so ... you chould have 50 tables opened in datasheet view mode, and all that data retrieval occurs through ONE connection, the one created by the Access UI when you opened the db. However, if you have an app, that creates 50 completely different connection objects in the same procedure, that is 50 connections.

The practical limit of 20 concurrent connections is really a guidline. It seems that the speed of data interaction really takes a turn for the worse in the 20 to 30 connections range, but many things can be done to help increase the practical limit, but 255 is definately the true limit.

 

[duluter]

Thanks, Brent.

I'm getting closer to understanding how all these parts work.

Correct me if any of this is wrong:

1. When you "open" an Access file, Access creates a connection to the data through JET.

2. The connection mentioned above is not significantly different from an ADO connection to the same data (from the perspective of "they are both just individual connections to the actual data").

3. One reason to split the database (aside from all the other reasons such as maintainability, etc.) is because there's trouble if two users try to open the Access file at the same time. However, in a non-split database, one user can have the file open and other users can access the data via ADO at the same time just fine.

4. I will probably never definitively know what caused my problem.


Thanks again,

Duluter

 

[datAdrenaline]

My thoughts/clarification ...

>> 1. When you "open" an Access file, Access creates a connection to the data through JET. <<

Expanding and Clarifying: JET has 255 "connections" to hand out. So if you connect to the data via an ADO connection in Outlook, JET gives one connection spot to the ADO Connection Object defined in Outlook. If you subsequently "Open" the datafile using Access (ie: File->Open), JET will give a connection spot to the Access instance you used to Open the datafile .... now 2 connections have been spoken for, and JET still has 253 connections waiting to be grabbed. If another user creates an Access db, and then creates a Linked TableDef (File -> Get External Data -> Linked Table) that points to the datafile, a 3rd connection is given out by JET when the linked tabledef object retrieves the data stored in the 'raw' table that it is pointing to.

>> 2. The connection mentioned above is not significantly different from an ADO connection to the same data (from the perspective of "they are both just individual connections to the actual data"). <<

True ... the "connections" the JET manages, are basically communication channels that are used to manipulate data stored in the JET database. The "connections" JET manages are really not concerned with the method the requestor is using to communicate with the data (ie: An ADO/OLEDB Connection Object; ODBC Connection; or an Access direct connection).

>> 3. One reason to split the database (aside from all the other reasons such as maintainability, etc.) is because there's trouble if two users try to open the Access file at the same time. However, in a non-split database, one user can have the file open and other users can access the data via ADO at the same time just fine. <<

Nope ... there is no "trouble" per se opening a .MDB file by more than one user (provide it is not defined as an "Exclusive" .mdb file). But ... I am unsure as to what you mean when you say "..try to open the Access file at the same time..". Do you mean an "application" file (Forms, Reports, Queries, Macros, Modules, LINKED Table Defs) or the "datafile" (Tables Only) {which describes a "Split" configuration}. In either case, the file itself can be opened by more than one person and JET (the manager of the .MDB files) gives users connection spots for each of the MDB's being opened. Do take note that the "application" file is still an MDB file and JET is utilized to store information regarding your Forms and other Access objects. So ... if you have a SINGLE file that holds the Application Info, as well as the 'raw' data (ie: Local Tables) an Outlook user can easily use ADO or ODBC and grab a JET connection to the .MDB file and begin manipulating data in the tables ... typically with out issue. However, if a user opens up the Single File solution with MS Access and thus has the ability to effect the Design of the objects defined in the file (ie: Forms or Tables), that is often where trouble starts to creep in. You see, many times a user will ... umm ... filter a form, then SAVE that form with a Ctrl-S ... KAPOW! ... a seemingly innocent event, but the Design of that form has changed. Over the years Access has gotten better about trying to gaurd against corruption occurring with this mechanism, but ultimately it is the best practice to "Split" the data (The 'Back End') from the application info (The 'Front End'), and distribute the Front End to all of your users. In your situation of using Outlook, your Outlook VBA is really considered just another 'Frond End' that gets its data from an .MDB JET does not know whether the .MDB is a 'Front End' or a 'Back End' or both. Outlook (or the ADO Connection Object) is merely talking to JET and JET is giving back the answers Outlook is requesting through the connection.

>> 4. I will probably never definitively know what caused my problem. <<

Yep ... that is a true statment! ..

.....

Hope this helps!

 

[duluter]

Banana and Brent, you have both given me some really great info in this thread. I can't thank you enough. I now have a much better understanding of the mechanics of these files and connections.


Duluter

 

[datAdrenaline]

You're welcome! ... Always glad to help out!