Results 1 to 7 of 7
  1. #1
    Join Date
    May 2003
    Posts
    70

    Unanswered: Opening recordset

    Hi,

    If I open my recordset in the followin manner:
    rs.open "TableName", CN, adOpenKeyset, adLockOptimistic

    Will it load the content of the whole table into memory?

    should I be doing this instead?
    rs.open "Select * From TableName Where AutoNumberKey = -1"

    ???

    Thanks,

  2. #2
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    here is some sample code that my help - remember any other manipulatin using the rs.open may cause you to lose the connection so look at this as a partial solution

    Dim con As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset
    Dim stQuery as String

    'myTable = whatever your table is called.
    stQuery = "SELECT * FROM myTable ORDER BY name"

    With cmd
    Set .ActiveConnection = con
    .CommandType = adCmdText
    .CommandText = stQuery
    End With

    With rs
    .LockType = adLockPessimistic
    .CursorType = adOpenKeyset
    .Open cmd
    End With

    If rs.RecordCount > 0 Then
    Set Adodc1.Recordset = rs
    Adodc1.Refresh
    rs.Close
    Else
    rs.Close
    End If

  3. #3
    Join Date
    May 2003
    Posts
    70
    This isn't what I'm asking.
    I want to know if I open a recordset using the table name, will it load the content of the whole table into memory??

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    well now, i can't directly answer your question, but if you absolutely do want the entire recordset loaded into memory you need to take special action.

    e.g. if you want to be sure you get the right answer from rex.recordcount you should first do a rex.movelast to get everything loaded.

    if you only want a subset of records, then logically you should use the WHERE

    what access does by default is somewhere between one and all records, but i can't give you the definition.

    izy

  5. #5
    Join Date
    May 2003
    Posts
    70
    What I want to do is the followin.

    I want to open the recordset before using the .AddNew method.

    I want the most efficient way that's why I'm asking if using the table name will load the whole table into memory or not.

    Thanks,

  6. #6
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    the answer is yes the recordset will load into memory provided there are no memory constraints to prohibit the load.

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ahA!

    if your goal is minimum memory use, and your only intent behind opening the recordset is .addnew then construct your WHERE with as much stuff from your wannabe new record as possible... if you are lucky your recordset will be empty. it's not all bad to do this anyway, since .recordcount > 0 shows duplicate records which might be helpful.

    your WHERE could look like
    fldLastName = boxNewLast.Value and fldFirstName = boxNewFirst.value
    etc... in an attempt to not select any of the existing records

    izy

Posting Permissions

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