MS Access Connection Limit

[NJLDoc]

I have written a VB 6 SP6 application that uses Access 97 databases. There are four small databases on the local machine and 1 larger database that is shared often on a network server. One client I have runs XP Home and all databases reside on her local machine. Another client runs XP Pro with all databases residing on the local machine. All data connections are made identically using ado 2.8 yet only 4 of the 5 actually succeed. The client does have MS Office installed but does not have MS Access. None of the other 40+ clients I have running this application experience the same problem. Is there a limit to the number of active separate database connections that can run? I have run MDAC checkers finding no problems. I have run dll dependency comparisons without success.

 

[Trevil]

I'm not clear on the symptoms: You said "four small .. and 1 .. often shared on network", but when you say "XP Home and all reside local.." are you saying nothing on the network is being accessed?

Do you get an error message? If so, what is it.

Do the configurations of the "40+ others" match (XP, no MS Access, etc.)?

You don't mention what method you use to access the 5 databases.

"Hmmm, it worked when I tested it....

 

[NJLDoc]

The databses on the machines having trouble are all part of a network yet the database connections are all local to their machine. Most other users do not have access installed though some do. Some have administartive accounts such as the XP Home user and others not.

The connection method is dim cn as ADODB.Connection. Each databse I connect to uses exacly the same syntax and so the puzzleas to why it will not connect. If in code I use Kill Path_of_DB on the trouble database I get error 70 permission denied yet the database is not open.

 

[Trevil]

I believe you are running a VB6 application on an XP workstation, that tries to open multiple MS-Access 97 databases that are located on one or more servers?

Are you trying to open all 5 databases concurrently, or is only one open at a time?

Re "I use Kill Path_of_DB..." I assume you are trying to delete an Access 97 database? If so, and you get a permissions error, either the file is busy or the user does not have permissions to delete the file.

Can you change the order of connections so that the 5th one gets opened earlier? If that works but still fails on the 5th open, then it's most likely a resource problem. If it fails on a specific database, then it must be a permissions problem.

Please let me know.

"Hmmm, it worked when I tested it....

 

[NJLDoc]

As a test of the problem Iplaced a copy of the database on the local machine. Before the connection was attempted the Kill old was followed by File copy. The kill error was permission denied yet the databse is not in use. I copied the clients database and tested on my machines with no problem there the databse is not corrupted. The permission error is strange because the client is an administrative user not a restricted user. All 5 database connections can be loaded concurrently expcept on this machine. If its a resource issue how do I verify and correct?

 

[Trevil]

I think you first need to solve the cause of the 'Permission Denied' error.

I'll ramble....
1. Check the database to see if there is a 'Read-Only' attribute set.
2. If not 'Read-Only', then how are you copying from server to workstation? I assume explorer drag-and-drop or copy / paste.
3. If you are the one doing the copy, then are you logging into their system, or using the existing users account?
4. Try a different approach for deleting the file and see if that works:
Dim oFSO As FileSystemObject
Set oFSO = CreateObject("Scripting.FileSystemObject")
oFSO.DeleteFile ("C:\temp\YourDatabase.mdb"), True

Did you try switching the order of opening the databases to see if we can eliminate one cause?


"Hmmm, it worked when I tested it....

 

[NJLDoc]

I am waiting for the client to try a new build of the application affecting the order of connection. As far as the delete code is concerned, I do not need this process as I had added this to test for a corrupt database which was not the case. Assuming that the connection order does make a difference, I would expect the new ordered last database to fail.

Would this will clarify the problem as a resource issue? How would one trouble shoot this.

 

[Trevil]

If you change the database open order, and it still fails on the 5th database, then I think there is a resource issue and I would want to see the exact code that you are executing to open ALL database.

I had asked in my first reply if you get an error on the open, and if so what is it.


"Hmmm, it worked when I tested it....

 

[NJLDoc]

The error message received is 3704 however the connection state shows as 1.

 

[Trevil]

My guess is that you are using the ADO object for your connections?

In that case the 3704 error is for "Application-defined or object-defined error" and is not the true cause of the error. Probably more like: "Operation is not allowed when the object is closed. The Connection or Recordset has been closed. For example, some other routine might have closed a global object. You can prevent this error by checking the State property before you attempt an operation."

Multiple errors can be raised by the ADO object and you need to grab all of them. The following describes the error collection:

http://docs.sun.com/source/817-2514-10/Ch11_ADO47.html

The code you would need to use in your error trap would be something like the following:

AdoError:

Dim errorCollection As Variant
Dim errLoop As Error
Dim strError As String
Dim iCounter As Integer

' In case our adoConnection is not set or
' there were other initialization problems
On Error Resume Next

iCounter = 1

' Enumerate Errors collection and display properties of
' each Error object.
strError = ""
Set errorCollection = adoConnection.Errors
For Each errLoop In errorCollection
With errLoop
strError = "Error #" & iCounter & vbCrLf
strError = strError & " ADO Error #" & .Number & vbCrLf
strError = strError & " Description " & .Description & vbCrLf
strError = strError & " Source " & .Source & vbCrLf
Debug.Print strError
iCounter = iCounter + 1
End With
Next




"Hmmm, it worked when I tested it....

 

[NJLDoc]

Yes I am using ADO for the conection object. I cannot understand why the same build of the application would work on one machine and not another if the issue is a closed connection or recordset. The connection state shows = 1 but when the code hits the .AddNew the 3704 is triggered but no ADO errors from the code loop provided.

Scenario:
Database-A has records being read for use in for use in Database-B. Database-B is the database that I cannot connect to. After the 3704 error is thrown Database-A no longer has data yet it has not been programmatically purged.

 

[Trevil]

Re 'I cannot understand why the same build of the application would work on one machine and not another...' you're at the mercy of whatever version of DLL's, MDAC, JET, ADO etc.

Hmmm, as I connect two other brain cells.... We did have an issue a month ago where our new version of an application would get an error on SOME XP workstations, but run OK on all Win-2K workstations. Eventual solution was to open the code, delete ALL references, save everything (can't remember if we closed database), then add ALL required references back. There is a documented problem about references being corrupted, and this fixed our problem. That may deserve a shot.

Am I to assume that 'Database B' was previously the 5th one opened, but is now the 2nd one opened?



"Hmmm, it worked when I tested it....

 

[NJLDoc]

Your assumption is correct re databse order. I will take a look at the references as an option though this will take some time to effect I think.

Was there a report you can refernce re corrupted project references?

 

[Trevil]

I'm struggling trying to find the website that mentioned this. I did find my email that was sent when we had a clue that references were a problem, but since it was sent to a user, I didn't include where I found it.

"Hmmm, it worked when I tested it....

 

[NJLDoc]

If you find a reference please let me know. Once I find the solution I will post. I do appreciate the considerable effort you have put forth. Thank you.

 

[chiph]

One client I have runs XP Home and all databases reside on her local machine

The non-server versions of XP have a limit on the number of incoming network connections. I think it's five, but I'm not 100% sure.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[NJLDoc]

The incoming network connections I believe would be a non issue as the databases are on the local machine. Also, I have an XP Pro machine that runs the application in question just fine. Are there any trouble shooting suggestions anyone has?

 

[Trevil]

I did manage to find one reference for 'corrupted references', but it's not the one I remeember. See:

http://www.utteraccess.com/forums/access/access80005.html

Now, you said "the code hits the .AddNew the 3704 is triggered but no ADO errors from the code loop provided." You do realize this sample code would NOT display a message to you -- it does a 'Debug.Print'. Are you CERTAIN there was no other errors because I think there would have been.

I also believe you said you changed the DB open order and now get the problem on the second open? Can you post the code that is executed from the point of the first open through the point that it fails. I'm not clear on whether you are:
1. Opening only connections, or
2. Opening tables based on connections, or
3. Opening recordsets (that may contain many joined tables).


"Hmmm, it worked when I tested it....

 

[NJLDoc]

Thanks for the link. I will take a look.

Yes, the debug.print line does not show the error at runtime but I had replaced with a message box to be able to identify raised errors.

 

[NJLDoc]

Trevil I wish to thank you for all your efforts. The problem I have resolved and was an unbelievable oversight on my part. I had been sourcing the problematic database path by comparing a registry value of another database and replacing the name of that database with the problematic one in order to set the ADO connection string. The database name Replace() function was the cause of the error as the default was vbBinaryCompare and as the string I was passing in was different by case the Replace() function failed and a second connection was created to the registry referenced database. This explains why the connection state was open and why the addnew failed.

I apologize for the wild goose chase but do appreciate all your efforts.