ADO won't connect to DB


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: ADO won't connect to DB

  1. #1
    James Guest

    ADO won't connect to DB


    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


  2. #2
    Russ Guest

    Re: ADO won't connect to DB


    "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.




  3. #3
    James Guest

    Re: ADO won't connect to DB


    "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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center