Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Unanswered: ADODB connection syntax/structure (!?) problem

    hm. the following code works as far as letting me debug.print 1 recordset:

    Code:
        Dim rstTrans As ADODB.Recordset
        Set rstTrans = New ADODB.Recordset
        rstTrans.ActiveConnection = CurrentProject.Connection
        rstTrans.CursorType = adOpenDynamic
        rstTrans.LockType = adLockPessimistic
        rstTrans.Open ("select * from tbl_trans)
    
        'Check, print contents:
        Debug.Print rstTrans.GetString
    
        Dim rstH1 As ADODB.Recordset
        Set rstH1 = New ADODB.Recordset
        rstH1.ActiveConnection = CurrentProject.Connection
        rstH1.CursorType = adOpenDynamic
        rstH1.LockType = adLockReadOnly
        rstH1.Open ("select * from tbl_H1)
    
        Debug.Print rstH1.GetString
        Stop
    
        lAmount = rstTrans!amount
    BUT: - I need 2 rst's on the same connection, and when I change to the following:

    Code:
    'TESTING 2 rst on 1 cnn:
    PrepHoldingsRecordset:
        Dim cnn As ADODB.Connection
        Dim rstTrans As ADODB.Recordset
        Dim rstH1 As ADODB.Recordset
    
        Set cnn = New ADODB.Connection
        Set rstTrans = New ADODB.Recordset
        Set rstH1 = New ADODB.Recordset
    
        cnn.ConnectionString = CurrentProject.Connection
        cnn.Open
    
        rstTrans.ActiveConnection = cnn
        rstTrans.CursorType = adOpenDynamic
        rstTrans.LockType = adLockPessimistic
        rstTrans.Open ("select * from tbl_trans")
    
        rstH1.ActiveConnection = cnn
        rstH1.CursorType = adOpenDynamic
        rstH1.LockType = adLockReadOnly
        rstH1.Open ("select * from tbl_h1")
    
        'Check, print contents:
        Debug.Print rstTrans.GetString
        Debug.Print rstH1.GetString
        Stop
    
        lAmount = rstTrans!amount
        Stop
    
    '''ENDOFTEST_REPLACE_WITH_ADJUSTED_ORG_CODE
    It stops at
    cnn.Open
    and says:

    Run-time error '3709':
    "The connection cannot be used to perform this operation. It is either closed or invalid in this context."

    Is there something wrong with the structure here?

    Daniel.
    Last edited by kedaniel; 04-05-04 at 13:04.

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465

    Re: ADODB connection syntax/structure (!?) problem

    Originally posted by kedaniel
    hm. the following code works as far as letting me debug.print the (1) recordset:

    Code:
        Dim rstTrans As ADODB.Recordset
        Set rstTrans = New ADODB.Recordset
        rstTrans.ActiveConnection = CurrentProject.Connection
        rstTrans.CursorType = adOpenDynamic
        rstTrans.LockType = adLockPessimistic
        rstTrans.Open ("select * from tbl_trans)
    
        'Check, print contents:
        Debug.Print rstTrans.GetString
    
        Dim rstH1 As ADODB.Recordset
        Set rstH1 = New ADODB.Recordset
        rstH1.ActiveConnection = CurrentProject.Connection
        rstH1.CursorType = adOpenDynamic
        rstH1.LockType = adLockReadOnly
        rstH1.Open ("select * from tbl_H1)
    
        Debug.Print rstH1.GetString
        Stop
    
        lAmount = rstTrans!amount
    BUT: - I need 2 connedtions, and when I change to the following:

    Code:
    'TESTING 2 rst on 1 cnn:
    PrepHoldingsRecordset:
        Dim cnn As ADODB.Connection
        Dim rstTrans As ADODB.Recordset
        Dim rstH1 As ADODB.Recordset
    
        Set cnn = New ADODB.Connection
        Set rstTrans = New ADODB.Recordset
        Set rstH1 = New ADODB.Recordset
    
        cnn.ConnectionString = CurrentProject.Connection
        cnn.Open
    
        rstTrans.ActiveConnection = cnn
        rstTrans.CursorType = adOpenDynamic
        rstTrans.LockType = adLockPessimistic
        rstTrans.Open ("select * from tbl_trans")
    
        rstH1.ActiveConnection = cnn
        rstH1.CursorType = adOpenDynamic
        rstH1.LockType = adLockReadOnly
        rstH1.Open ("select * from tbl_h1")
    
        'Check, print contents:
        Debug.Print rstTrans.GetString
        Debug.Print rstH1.GetString
        Stop
    
        lAmount = rstTrans!amount
        Stop
    
    '''ENDOFTEST_REPLACE_WITH_ADJUSTED_ORG_CODE
    It stops at
    cnn.Open
    and says:

    Run-time error '3709':
    "The connection cannot be used to perform this operation. It is either closed or invalid in this context."

    Is there something wrong with the structure here?

    Daniel.
    This is an opinion because i use Access 97 then with local data base but i thing that the problem is with cnn because is empty you must set the variable control conection some thing like this:
    Dim conEditores As Conection
    Set conEditores = wrkODBC.OpenConnection("conexión1", _
    dbDriverNoPrompt, , _
    "ODBC;DATABASE=eds;UID=sa;PWD=;DSN=Editores")

    Copyright (c) 1996 Microsoft Corporation

    you must set the cnn control with the currentProject .......
    Saludos
    Norberto

  3. #3
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    I send an DAO sample conection
    Saludos
    Norberto

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    I hope this help you
    Attached Files Attached Files
    Saludos
    Norberto

  5. #5
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    Re: ADODB connection syntax/structure (!?) problem

    Since it is an object you must use Set as follows:

    Set cnn = CurrentProject.Connection
    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!

  6. #6
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Re: ADODB connection syntax/structure (!?) problem

    Originally posted by SCIROCCO
    Set cnn = CurrentProject.Connection
    OK, that works :-)

    Hm. I do not know very much about working with ADO recordsets. It now seems that the "cursor" leaves the record after a GetString test, or is placed on it which causes a new request to look forward only and miss it?

    If I do not manually enter
    rstH1.MoveFirst
    before trying to access this 1-record rst, then I will get the following error:
    "Run-time error '3021':
    Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record."

    Has this something to do with cursor type? In this test the Trans is set to
    rstTrans.CursorType = adOpenDynamic
    , which I thought should be ok. That one is not like a forward-only cursor, which is the default when not specifying one, as far as I know.

    I get that error message even when using debug.print, AND when simply looking up a value in one of the fields in the 1-record rst... (not moving forward?)
    ( lAmount = rstTrans!amount )

    This does not make sense to me. Please advice.
    (I assume that I do not have to include MoveFirst after every operation on a 1-record rst, in order to have the next operation work on the full recordset?)

    D.

  7. #7
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    Re: ADODB connection syntax/structure (!?) problem

    When you open a new recordset the pointer should be on the first record (if one exists) otherwise either Bof or Eof will be true. Are you sure you are not doing anything to reach the last record or something? Please post your code to investigate.

    In any case you should always test when opening new recordsets for that condition:

    If not (rs.Bof and rs.Eof) then

    'Do something

    Else

    'There are no records found

    End If
    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!

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: ADODB connection syntax/structure (!?) problem

    Originally posted by SCIROCCO
    When you open a new recordset the pointer should be on the first record (if one exists) otherwise either Bof or Eof will be true. Are you sure you are not doing anything to reach the last record or something? Please post your code to investigate.

    In any case you should always test when opening new recordsets for that condition:

    If not (rs.Bof and rs.Eof) then

    'Do something

    Else

    'There are no records found

    End If
    Not true ... With ADO recordsets you are not on the 1st record. You are before the 1st record ... The BOF/EOF flags are set if no matches are found (empty recordset). So to get on the 1st record you must do a MoveFirst ... Your testing of BOF/EOF is correct ... Although you need test for only 1 initially ...

  9. #9
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    It is true you are not in first record if you use ADO, your are in the first record if you use DAO
    Saludos
    Norberto

  10. #10
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: ADODB connection syntax/structure (!?) problem

    Originally posted by M Owen
    Not true ... With ADO recordsets you are not on the 1st record. You are before the 1st record ... The BOF/EOF flags are set if no matches are found (empty recordset). So to get on the 1st record you must do a MoveFirst ... Your testing of BOF/EOF is correct ... Although you need test for only 1 initially ...
    If that were true, then the following code would return a true for BOF and Error 3021 would be the result when I tried to look at EventID.

    Dim rs As ADODB.Recordset, strSQL As String
    Set rs = New ADODB.Recordset

    strSQL = "Select EventID from tblEvents"
    rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    MsgBox rs.BOF
    MsgBox rs.Fields("EventID")

    However, as it turns out, I am on the first record and don't get an error but instead, the value that the first record contains.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  11. #11
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    OK, I re-edited a first offline draft for my second comment after finding that the movefirst worked, but unfortunately a sentence too much was eliminated:

    It works for the first debug.print getstring command for both recordsets, the correct data is displayed in the immediate window for each of the two rst's.

    THEN, if I do the same debug.print getstring for either one of the rst's, I get the EOF/BOF message..!

    In this scenario I will only work with rst's that contain one record at the time, and there will be a record each time.

    What is the behaviour of the cursor/move direction when I use
    rstTrans.CursorType = adOpenDynamic ?

    When a lookup in a certain field is performed, I guess the focus is at that field. Do one need to movefirst before each subsequent action one want to perform on the whole rst?

    Daniel

  12. #12
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: ADODB connection syntax/structure (!?) problem

    Originally posted by basicmek
    If that were true, then the following code would return a true for BOF and Error 3021 would be the result when I tried to look at EventID.

    Dim rs As ADODB.Recordset, strSQL As String
    Set rs = New ADODB.Recordset

    strSQL = "Select EventID from tblEvents"
    rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    MsgBox rs.BOF
    MsgBox rs.Fields("EventID")

    However, as it turns out, I am on the first record and don't get an error but instead, the value that the first record contains.
    Hmmmmm ... Well I go by the docs on ADO recordsets ... No matter.

  13. #13
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Re: ADODB connection syntax/structure (!?) problem

    Originally posted by basicmek
    If that were true, then the following code would return a true for BOF and Error 3021 would be the result when I tried to look at EventID.

    Dim rs As ADODB.Recordset, strSQL As String
    Set rs = New ADODB.Recordset

    strSQL = "Select EventID from tblEvents"
    rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    MsgBox rs.BOF
    MsgBox rs.Fields("EventID")

    However, as it turns out, I am on the first record and don't get an error but instead, the value that the first record contains.
    Hm. I am going in circles here.
    I am trying both the above, adjustet to my tbl names etc, and the following:

    Code:
        Dim cnn As ADODB.Connection
        Dim rstH1 As ADODB.Recordset
        Dim rstTrans As ADODB.Recordset
        Set cnn = New ADODB.Connection
        Set rstH1 = New ADODB.Recordset
        Set rstTrans = New ADODB.Recordset
    
        Dim strUsr9 As String
        Dim strPw As String
        strUsr9 = "x" 'the user I am currently logged in with
        strPw = "y" ' my valid pw
    
        Set cnn.ConnectionString = CurrentProject.Connection
        cnn.Open , strUsr9, strPw
    I do it both with and without the login, and without, using only cnn.open
    I am also trying to use cnn = currentProject.Connection¨
    , ie. without using "set" , but is either asked for a user/pw, (this db uses a mdw with logins/pw, but not db pw) or given the message about the cnn being wrong. using set, I get passed it, but then it requires a login. What is the circumstances where one can use simply cnn.open?

    I would like to connect using the current logged in user and the current connection, not specifying any user/pw.


    D.

  14. #14
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Re: ADODB connection syntax/structure (!?) problem

    Originally posted by kedaniel
    Hm. I am going in circles here.
    I am trying both the above, adjustet to my tbl names etc, and the following:

    Code:
        Dim cnn As ADODB.Connection
        Dim rstH1 As ADODB.Recordset
        Dim rstTrans As ADODB.Recordset
        Set cnn = New ADODB.Connection
        Set rstH1 = New ADODB.Recordset
        Set rstTrans = New ADODB.Recordset
    
        Dim strUsr9 As String
        Dim strPw As String
        strUsr9 = "x" 'the user I am currently logged in with
        strPw = "y" ' my valid pw
    
        Set cnn.ConnectionString = CurrentProject.Connection
        cnn.Open , strUsr9, strPw
    I do it both with and without the login, and without, using only cnn.open
    I am also trying to use cnn = currentProject.Connection¨
    , ie. without using "set" , but is either asked for a user/pw, (this db uses a mdw with logins/pw, but not db pw) or given the message about the cnn being wrong. using set, I get passed it, but then it requires a login. What is the circumstances where one can use simply cnn.open?

    I would like to connect using the current logged in user and the current connection, not specifying any user/pw.


    D.
    It's hard to tell where you are with this thing at this point but it does appear that the GetString method moves the cursor to the EOF position. So, yes, I guess you would have to use MoveFirst to get back to the first record.

    As far as the login is concerned, I have always used the open method of the recordset and specified CurrentProject.Connection for the ActiveConnection argument. If your goal is to create a recordset and not to use the other functions of a connection object then this would be the way to go.

    With cnn.Open: You can use this but you have to have set the connectionstring argument prior to opening the connection. Seems like you did that originally.


    For your original code, I think this should work:

    PrepHoldingsRecordset:
    Dim rstTrans As ADODB.Recordset
    Dim rstH1 As ADODB.Recordset

    Set rstTrans = New ADODB.Recordset
    Set rstH1 = New ADODB.Recordset


    rstTrans.ActiveConnection = CurrentProject.Connection
    rstTrans.CursorType = adOpenDynamic
    rstTrans.LockType = adLockPessimistic
    rstTrans.Open ("select * from tbl_trans")

    rstH1.ActiveConnection = CurrentProject.Connection
    rstH1.CursorType = adOpenDynamic
    rstH1.LockType = adLockReadOnly
    rstH1.Open ("select * from tbl_h1")

    'Check, print contents:
    Debug.Print rstTrans.GetString
    Debug.Print rstH1.GetString
    Stop

    lAmount = rstTrans!amount
    Stop

    You have to watch your cursor types though. They each have different characteristics.

    Hope I didn't confuse things.

    Good luck.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

Posting Permissions

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