Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    I'm relatively new in using ADODB recordsets. I'm attempting to update or add records to an ADODB recordset based on data in another table with the code below, but can't find a solution to the rowset scrolling error I get at the .Find statement. Reading the help on recordset open parameters, I think I have the correct CursorType and LockType, but I've clearly overlooked something. Recordset rstAssoc is successfully opened and I can view the first record in the debug window.

    Set rstAssoc = New ADODB.Recordset
    rstAssoc.Open _
    "A_tblBalances", _
    cnRst, _
    adOpenDynamic, _
    adLockOptimistic, _
    adCmdTable
    Do While rst.EOF = False
    With rstAssoc
    .Find "[SYear]=" & rst!SYear
    If _
    Not .EOF _
    Then
    !SYear = rstAssoc!SYear
    !CurrentAssessment = rstAssoc!CurrentAssessment
    .Update 'This is an optional explicit save
    Else
    .AddNew
    !SYear = rstAssoc!SYear
    !CurrentAssessment = rstAssoc!CurrentAssessment
    !AID = pAID
    .Update
    End If
    End With
    rstAssoc.MoveNext
    Loop

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Is A_tblBalances a table in the current database, or a linked table? If the latter, is the source table in another Access database, or in (for example) a SQL Server database?
    BTW you'd have to move to the first record in rstAlloc within the loop.

    (I always prefer to use DAO, it's much easier than ADO)

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    There must be more code involved than this, because I cannot see the logic behind this.
    It appears that it probably needs a loop for the outer recordset rst (that is not defined).
    And another loop for the inner recordset.

    You also need to explain how many times the years are in each table.

    There appears nothing wrong with the adodb connections, although the adCmdTable
    is probably not essential.

    But, the looping logic looks incorrect to me

    Attaching a zipped and stripped down copy of the database might help
    Andrew

  4. #4
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    Hans,

    rst is a DAO recordset based on a local table in the FE which contains the new values and A_tblBalances is a BE table which is not linked.

    There is considerable time overhead to a full linking of one the BE db's. The client would like to be able make some simple on-the-fly updates to this table. I thought with ADODB I could connect to the table and make the changes without linking to the FE. I didn't think DAO provided me a way to do this. I've used DAO in the past for similar functions, but I would link it in with an alias name which requires additional housekeeping to manage the alias which I was trying to avoid.

    Marty

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    With DAO you can also open a recordset on a table in an external database without linking it - either use OpenDatabase to create a Database object, then OpenRecordset to open a recordset in that database, or specify the external database in the SQL for the recordset.

    By the way, I tested your code both with a local table and a linked table, and didn't get an error message, so I don't understand why you get one.

  6. #6
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    [quote name='AndrewKKWalker' post='799729' date='24-Oct-2009 13:49']There must be more code involved than this, because I cannot see the logic behind this.
    It appears that it probably needs a loop for the outer recordset rst (that is not defined).
    And another loop for the inner recordset.

    You also need to explain how many times the years are in each table.

    There appears nothing wrong with the adodb connections, although the adCmdTable
    is probably not essential.

    But, the looping logic looks incorrect to me

    Attaching a zipped and stripped down copy of the database might help[/quote]


    Andrew,

    Thanks for your input. You've already spoted my next logic error should I get past the .find problem. The statement rstAssoc.MoveNext should really be rst.MoveNext. In both recordsets the year is a unique value and cann only appear once. I failed to mention in the original post the ADODB connection is to a BE table which is not linked to the FE.

    Marty

  7. #7
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='mcowen' post='799740' date='25-Oct-2009 00:11']Andrew,

    Thanks for your input. You've already spoted my next logic error should I get past the .find problem. The statement rstAssoc.MoveNext should really be rst.MoveNext. In both recordsets the year is a unique value and cann only appear once. I failed to mention in the original post the ADODB connection is to a BE table which is not linked to the FE.

    Marty[/quote]

    I am assuming that the BE has a table in it and the FE a similar table.
    IF your intention is to Update the FE with data from the FE
    By Adding Records where the year don't match
    and Updating Records where the years do match,
    then something like the function below would do.

    This assume a 1 to 1 relationship based on SYEAR.
    It also assume the BE is another Access Database

    In this case DAO works well.

    If the BE is SQL Server or MYSQL etc then ADODB might be better,
    although Microsoft have swung back to favour DAO in Office 2007


    You will need to modify table names and locations
    Also it is written as a Function, but you can change it back to a sub by just taking the code section,
    and changing any End Function to End Sub

    also I have no idea what pAID is




    '================================================
    Option Compare Database
    Option Explicit

    Function UpdateBE()

    Dim dBB As DAO.Database, dBL As DAO.Database
    Dim rstB As DAO.Recordset, rstL As DAO.Recordset
    Dim lngUpdated As Long, lngAdded As Long


    On Error GoTo NoUpdateError

    Set dBL = CurrentDb
    Set dBB = DBEngine(0).OpenDatabase("C:\DATA\db2.mdb") 'Put Your Path Here

    Set rstL = dBL.OpenRecordset("A_tblBalancesLO", dbOpenTable) 'Back End

    If Not rstL.EOF Then rstL.MoveFirst

    Do While Not rstL.EOF
    'Open Recordset to back end relating to Local Year
    Set rstB = dBB.OpenRecordset("Select * From A_tblBalancesBE Where SYear=" & rstL!SYEAR, dbOpenDynaset) 'FE
    If Not rstB.EOF Then 'If Records Then
    rstB.MoveFirst
    'Update
    rstB.Edit
    rstB!SYEAR = rstL!SYEAR 'If you are changing an existing record with Key Year you don't need this do you?
    rstB!CurrentAssessment = rstL!CurrentAssessment
    rstB.Update
    lngUpdated = lngUpdated + 1
    Else
    'Append
    rstB.AddNew
    rstB!SYEAR = rstL!SYEAR
    rstB!CurrentAssessment = rstL!CurrentAssessment
    'rstB!AID = pAID ' I have No Idea what this is but I am sure you do
    rstB.Update
    lngAdded = lngAdded + 1
    End If
    rstL.MoveNext 'Next Local Record
    Loop

    rstB.Close
    rstL.Close
    dBB.Close
    dBL.Close


    Set rstB = Nothing
    Set rstL = Nothing
    Set dBB = Nothing
    Set dBL = Nothing

    'Comment These out when no longer Testing
    MsgBox "Data Updated Into Back End Table" & vbLf & lngUpdated & " changed " & vbLf & lngAdded & " Added", vbExclamation

    Exit Function


    NoUpdateError:

    Set rstB = Nothing
    Set rstL = Nothing
    Set dBB = Nothing
    Set dBL = Nothing

    MsgBox "Could Not Complete Update of BE table" & vbLf & Err.Description, vbExclamation
    Exit Function



    End Function
    '================================================= ==========


    Anyway, it might help.
    Andrew

  8. #8
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    Andrew & Hans.

    Thanks so much for the great assistance, I've got things to work as hoped.

    Marty

Posting Permissions

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