ADO won't connect to DB

[James]

Hi all.

I have a rather interesting problem that I hope someone can help me with.
My program controlls a 6-axis CNC milling machine. Users send co-ordinates
into an Access 2000 database, my program reads the co-ordinates, and sends
the co-ordinates to the mill. At random, I cannot connect to the database.
I reuse the same function to get the next co-ordinate, so I know that the
function works. Once the function cannot connect to the database, all other
functions will not connect to the database. Below is the code clippit for
retrieving the next X co-ordinate:

Public Function NextX() As Single
Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
Dim DBPath As String

Set CN = New ADODB.Connection
Set RS = New ADODB.Recordset

DBPath = GetSetting("GrisGun", "Settings", "DBLoc")
CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = " & DBPath & ";" & _
"Persist Security Info=False"

CN.Open

RS.Open "CurrentGun", CN, adOpenStatic, adLockOptimistic, adCmdTable
RS.AbsolutePosition = CurrentGunPos
NextX = RS!Distance

RS.Close
CN.Close

Set RS = Nothing
Set CN = Nothing

End Function

I've tried it with:
Dim CN as New ADODB.Connection

and with:
Dim CN as ADODB.Connection

Set CN = New ADODB.Connection

the result is the same.

Sometimes this will fail on the third connection to the database, sometimes
on the 60th, or anywhere inbetween. I can't find any reason as to why it
won't connect. And more so, why other functions connecting to the database
won't connect once the error occurs.

Thanks in advance for your help.

James

[Russ]

"James" <davidsonj@tni.ca> wrote:
>
>Hi all.
>
>I have a rather interesting problem that I hope someone can help me with.
>My program controlls a 6-axis CNC milling machine. Users send co-ordinates
>into an Access 2000 database, my program reads the co-ordinates, and sends
>the co-ordinates to the mill. At random, I cannot connect to the database.
>I reuse the same function to get the next co-ordinate, so I know that the
>function works. Once the function cannot connect to the database, all other
>functions will not connect to the database. Below is the code clippit for
>retrieving the next X co-ordinate:
>
>Public Function NextX() As Single
>Dim CN As ADODB.Connection
>Dim RS As ADODB.Recordset
>Dim DBPath As String
>
>Set CN = New ADODB.Connection
>Set RS = New ADODB.Recordset
>
>DBPath = GetSetting("GrisGun", "Settings", "DBLoc")
>CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source = " & DBPath & ";" & _
> "Persist Security Info=False"
>
>CN.Open
>
>RS.Open "CurrentGun", CN, adOpenStatic, adLockOptimistic, adCmdTable
>RS.AbsolutePosition = CurrentGunPos
>NextX = RS!Distance
>
>RS.Close
>CN.Close
>
>Set RS = Nothing
>Set CN = Nothing
>
>End Function
>
>I've tried it with:
>Dim CN as New ADODB.Connection
>
>and with:
>Dim CN as ADODB.Connection
>
>Set CN = New ADODB.Connection
>
>the result is the same.
>
>Sometimes this will fail on the third connection to the database, sometimes
>on the 60th, or anywhere inbetween. I can't find any reason as to why it
>won't connect. And more so, why other functions connecting to the database
>won't connect once the error occurs.
>
>Thanks in advance for your help.
>
>James
>

James,
You might be running into a timing issue if you are opening and closing the
connection very quickly. You might try declaring CN at the module level,
open it once and leave it open until the last call to NextX() is finished.
Not sure if this will fix the problem, just a thought.

-Russ.

[James]

"Russ" <russell.thompson@adlink.com> wrote:
>
>"James" <davidsonj@tni.ca> wrote:
>>
>>Hi all.
>>
>>I have a rather interesting problem that I hope someone can help me with.
>>My program controlls a 6-axis CNC milling machine. Users send co-ordinates
>>into an Access 2000 database, my program reads the co-ordinates, and sends
>>the co-ordinates to the mill. At random, I cannot connect to the database.
>>I reuse the same function to get the next co-ordinate, so I know that the
>>function works. Once the function cannot connect to the database, all other
>>functions will not connect to the database. Below is the code clippit for
>>retrieving the next X co-ordinate:
>>
>>Public Function NextX() As Single
>>Dim CN As ADODB.Connection
>>Dim RS As ADODB.Recordset
>>Dim DBPath As String
>>
>>Set CN = New ADODB.Connection
>>Set RS = New ADODB.Recordset
>>
>>DBPath = GetSetting("GrisGun", "Settings", "DBLoc")
>>CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>> "Data Source = " & DBPath & ";" & _
>> "Persist Security Info=False"
>>
>>CN.Open
>>
>>RS.Open "CurrentGun", CN, adOpenStatic, adLockOptimistic, adCmdTable
>>RS.AbsolutePosition = CurrentGunPos
>>NextX = RS!Distance
>>
>>RS.Close
>>CN.Close
>>
>>Set RS = Nothing
>>Set CN = Nothing
>>
>>End Function
>>
>>I've tried it with:
>>Dim CN as New ADODB.Connection
>>
>>and with:
>>Dim CN as ADODB.Connection
>>
>>Set CN = New ADODB.Connection
>>
>>the result is the same.
>>
>>Sometimes this will fail on the third connection to the database, sometimes
>>on the 60th, or anywhere inbetween. I can't find any reason as to why it
>>won't connect. And more so, why other functions connecting to the database
>>won't connect once the error occurs.
>>
>>Thanks in advance for your help.
>>
>>James
>>
>
>James,
>You might be running into a timing issue if you are opening and closing
the
>connection very quickly. You might try declaring CN at the module level,
>open it once and leave it open until the last call to NextX() is finished.
> Not sure if this will fix the problem, just a thought.
>
>-Russ.
>
>
>
Thanks Russ, I was kinda thinking along those lines. I still think its a
little funny that an earlier version of the program worked great. I never
had a DB problem. Then the client asked for some additional features, and
now I have DB problems. These additional features don't interact with the
DB, so I'm quite confident that this is not the source of my headaches.

I'm also going to try going from ADO 2.5 to ADO 2.6 as a different release,
and see if that helps as well.

Now for the fun part... to wade through 2Megs of raw code to comment out
the new connection part in each subroutine and function.

Thanks

James