dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 5 of 5

Thread: ADODB Connection error

  1. #1
    Join Date
    Apr 2002
    Location
    Los Angeles, Ca
    Posts
    238

    ADODB Connection error

    Hi everyone ...
    I am creating an ADODB connection and recorset using code in a .BAS module. The procedure accepts a SQL string that is used to open the recordset.
    I call it from form load and it works great, but the when I want to do some other database function, I get the error. I KNOW it has records. The error is on the .MoveLast line in the module.
    Here is the code in the module and another procedure.
    I am open to ANY and ALL suggestions.
    Code:
    'MODULE CODE
    Option Explicit
    
    Public cnMyCon As ADODB.Connection 'Connection to ADODB
    Public MyRS As ADODB.Recordset 'Holds records
    Public Sub ConnectToDBase(strSQL As String)
    Dim lngRecordCount As Long
    
         If Not cnMyCon Is Nothing Then  'added to try to overcome error
              DisconnectDBase
         End If
         
         
         Set cnMyCon = New ADODB.Connection
         Set MyRS = New ADODB.Recordset
         
        MyRS.CursorLocation = adUseClient
        
       cnMyCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Administrator\Desktop\test.mdb;Persist Security Info=False"
       cnMyCon.CursorLocation = adUseClient
       MyRS.Open strSQL, cnMyCon, adOpenStatic, adLockOptimistic
       
       MyRS.MoveLast 'without these the recordcount
       MyRS.MoveFirst 'ends up -1
       lngRecordCount = MyRS.RecordCount
       Debug.Print "From Mod " & lngRecordCount 'visual proof that it has something
    End Sub
    Public Sub DisconnectDBase()
         MyRS.Close
         cnMyCon.Close
         Set MyRS = Nothing
         Set cnMyCon = Nothing
    End Sub
    
    'PROCEDURE CODE
    Private Sub Option1_Click(Index As Integer)
    
    Dim strCoName As String
    
         strCoName = Option1(Index).Caption
         Dim strSQL As String
         strSQL = "Select * From Suppliers where CompanyName = """ & strCoName & """"
         
         modTestConnect.ConnectToDBase (strSQL)
         MyRS.Fields.Item("Dropped").Value = True
         MyRS.Update
         
         UnloadOptions
         LoadControls
    End Sub
    Thanks.

  2. #2
    Join Date
    Aug 2000
    Location
    Essex, Uk
    Posts
    1,214
    What is the error message that you are getting?

    Also you should be more active with 'defencive' coding.

    You should wrap the move last statement etc in an

    if not (MyRs.bof and MyRs.eof) then.......

    Statement.
    If you find my answers helpful, dont forget to rate me

  3. #3
    Join Date
    Nov 2001
    Location
    Little Rock AR
    Posts
    255
    I know that some people say DAO is on its way out the door but I like it because it is faster than ADO. If you want to try DAO I can post some code.

    One thing that I would do is test the RS to see if there are any records in the rst. The move last will fail if there are no records.



    after the open I always test the rst like this:

    rstCnt = rst.recordcount

    if rstCnt = 0 then
    'close connection and give message or what ever
    end if

    rst.movelast
    rstCnt = rst.recordcount
    rst.movefirst

    One other test:

    Use a debug.print to capture the SQL. Open Access and a query and past the SQL there to see the results.

    Hope this helps
    LJ
    Hope is the feeling that you get that the feeling that you have will not last very long.

  4. #4
    Join Date
    Aug 2000
    Location
    Essex, Uk
    Posts
    1,214
    littlejohnny,

    The reason the guy is doing a movelast in the first place is because when the cursor is clientside in an ADO recordset, the recordcount property is always 0 even when records are present, therefore the first test that you propose of setting an integer will always be 0 since even if BOF and EOF where true (No records), the recordcount would still be 0. It's why I suggested wrapping in a 'not bof and eof' statement.

    However I agree with you that if he's using ACCESS and has no real intention of switching Databases at anypoint, then it wouldn't hurt to use DAO, but after being in this game for sooo long, you can gaurentee if the boss says 'no way EVER are we going to change' you can bet your last dollar that when the project's finished an upgrade to a bigger more powerful database will be required which will need ADO.
    If you find my answers helpful, dont forget to rate me

  5. #5
    Join Date
    Aug 2003
    Location
    London
    Posts
    515
    Originally posted by Bill Crawley
    However I agree with you that if he's using ACCESS and has no real intention of switching Databases at anypoint, then it wouldn't hurt to use DAO, but after being in this game for sooo long, you can gaurentee if the boss says 'no way EVER are we going to change' you can bet your last dollar that when the project's finished an upgrade to a bigger more powerful database will be required which will need ADO.
    I sort of went along with this for a while, using DAO strictly for Access apps and ADO for any others. But I found I was duplicating code & generally having to approach similar problems in different ways.

    I now use a class module I've written that can create any ADO connection & has a number of standard methods (GetRecordset, ExecuteProcedure etc), I just drop it into any project and that's all my data connections sorted. Syntax is the same in all my development & just seems to save me time.

    My 2 cents worth...!

Posting Permissions

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


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)