Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316

    Unanswered: More ADO woes...

    Does anyone know why sometimes this query works, and sometimes it doesn't?

    Code:
    Dim cnn As New ADODB.Connection
    Dim rs1 As New ADODB.Recordset
    Dim sQRY As String
    
    cnn.Open CurrentProject.Connection
    Set rs1 = New ADODB.Recordset
    
    sQRY = "SELECT WorkRequestID " & _
    "FROM tblLeedsASWRIDs;"
    rs1.Open sQRY, cnn, adOpenStatic, adLockReadOnly
    Set rs1 = cnn.Execute(sQRY)
    tblLeedsASWIRDs has 4 records in it. It seems totally random whether the above query picks them up or not. Any help greatly appreciated.

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    Your last 2 lines of code do the same thing one is not necessary as you have already retreived the data in the previous line.

    So remove either this line

    Set rs1 = cnn.Execute(sQRY)

    or this line

    rs1.Open sQRY, cnn, adOpenStatic, adLockReadOnly
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Mar 2004
    Location
    Adelaide, Australia
    Posts
    32
    I suggest you change your code to this:

    Code:
    Dim cnn As ADODB.Connection
    Dim rs1 As New ADODB.Recordset
    Dim sQRY As String
    
    set cnn = CurrentProject.Connection
    Set rs1 = New ADODB.Recordset
    
    sQRY = "SELECT WorkRequestID " & _
    "FROM tblLeedsASWRIDs;"
    rs1.Open sQRY, cnn, adOpenStatic, adLockReadOnly
    that is how I open a connection and it works fine, everytime.

    HTH.
    Regards,
    Rod.

    Programmer response #5 - "Even though it doesn't work, how does it feel?"

  4. #4
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Thanks a lot chaps. Seems the following line in my code wouldn't always establish a connection:

    Code:
    cnn.Open CurrentProject.Connection

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •