+ Reply to Thread
Results 1 to 16 of 16

Updating multiple records in Access table using excel VBA

  1. #1
    Forum Contributor
    Join Date
    01-21-2012
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2010
    Posts
    102

    Updating multiple records in Access table using excel VBA

    Hoping someone can help me with this. I've have a script that I'm modifying. It's original purpose was to add new records to an access table, which it does with no problem. What I want it to do is go from adding new records to updating existing ones. The only change I've made is in bold. I changed the .AddNew to .Update. I thought is would work but apparently not. Can anyone help me see what I'm missing that would make this work?



    Application.ScreenUpdating = False ' Prevents screen refreshing.

    Dim cn As ADODB.Connection
    Dim DatabaseName As String
    Dim TableName As String
    Dim rs As ADODB.Recordset
    Dim StartRow As Long

    Set rs = New ADODB.Recordset

    DatabaseName = "DB_test1.mdb" ' Enter your database name here
    TableName = "tblPopulation" ' Enter your Table name here
    StartRow = 2 ' Enter row in sheet to start reading records


    Dim shtSheetToWork As Worksheet
    Set shtSheetToWork = ActiveWorkbook.Worksheets("Table download")
    '********

    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Admin\DB_test1.mdb;Persist Security Info=False;"


    rs.Open TableName, cn, adOpenKeyset, adLockOptimistic


    Do While Range("A" & StartRow).Value > 0

    ' repeat until first empty cell in column A
    With rs
    .Update ' create a new record
    ' add values to each field in the record
    .Fields("Country") = Range("B" & StartRow).Value 'The Country
    .Fields("Yr_1950") = Range("C" & StartRow).Value 'The Year 1950
    .Fields("Yr_2000") = Range("D" & StartRow).Value 'The Year 2000
    .Fields("Yr_2015") = Range("E" & StartRow).Value 'The Year 2015
    .Fields("Yr_2025") = Range("F" & StartRow).Value 'The Year 2025
    .Fields("Yr_2050") = Range("G" & StartRow).Value 'The Year 2025
    .Fields("Region") = Range("H" & StartRow).Value 'The Region
    .Fields("MyField") = Range("I" & StartRow).Value 'New Field

    ' add more fields if necessary...
    .Update ' stores the new record
    End With
    StartRow = StartRow + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    cn.Close
    MsgBox "Data has been updated!", vbInformation
    Set cn = Nothing

  2. #2
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Updating multiple records in Access table using excel VBA

    Please add code tags to your code.
    Good luck.

  3. #3
    Forum Contributor
    Join Date
    01-21-2012
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2010
    Posts
    102

    Re: Updating multiple records in Access table using excel VBA

    Sorry I didn't add the proper code tags to my post earlier. Here is my code for trying to update records in an Access table from excel.

    Please Login or Register  to view this content.
    Last edited by anthony1312002; 02-27-2012 at 11:50 AM.

  4. #4
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Updating multiple records in Access table using excel VBA

    Code tags use square brackets [ and ] rather than < and > - please update accordingly.

    (note: you need to add code to either filter your recordset, or find a specific record based on some key field).

  5. #5
    Forum Contributor
    Join Date
    01-21-2012
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2010
    Posts
    102

    Re: Updating multiple records in Access table using excel VBA

    I see what your saying. Along those lines found this code on this forum that updates a single record but I don't know what alterations to make so that it will update multiple ones. It uses the active cell as the targeted update. Any thoughts on how this could be altered to use a range instead of just updating the active cell?

    Please Login or Register  to view this content.

  6. #6
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Updating multiple records in Access table using excel VBA

    You haven't said what the key field is, but you want something like this based on your original code:
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    01-21-2012
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2010
    Posts
    102

    Re: Updating multiple records in Access table using excel VBA

    Thanks for being patient. I'm need to understand what you mean by key field. Are speaking of the primary key in the Access table?

  8. #8
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Updating multiple records in Access table using excel VBA

    I mean whichever field you are using to link the Excel data to the Access data. That may or may not be the primary key.

  9. #9
    Forum Contributor
    Join Date
    01-21-2012
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2010
    Posts
    102

    Re: Updating multiple records in Access table using excel VBA

    Okay, I understand now. Well the key field in the Access table is PopID. This code that updates a single record based on the PopID in the Access table uses the ActiveCell function highlighted in bold along with the sql statement. I've been trying to use a range of cells instead of just the active cell in the update process but with no success. Can that be done using this code or the one I posted earlier?

    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    01-21-2012
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2010
    Posts
    102

    Re: Updating multiple records in Access table using excel VBA

    Okay, it looks like I really gave some wrong info. I really apologize. Here is what I meant to say. The key field in my excel file is in column A. It is numerical and matches with the primary key filed in the Access table. That is how the excel file is connected to the Access table. How can I use this code to update multiple records in the Access table using the key field?

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim fld As ADODB.Field
    Dim MyConn
    Dim lngRow As Long
    Dim lngID As Long
    Dim j As Long
    Dim sSQL As String


    lngRow = Range("A1:A228").Select

    sSQL = "SELECT * FROM tblPopulation WHERE PopID = " & lngID



    Set cnn = New ADODB.Connection
    MyConn = "C:\Admin\DB_test1.mdb"

    With cnn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Open MyConn
    End With

    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseServer
    rst.Open Source:=sSQL, ActiveConnection:=cnn, _
    CursorType:=adOpenKeyset, LockType:=adLockOptimistic

    I know this will have to change, but to what I don't know
    'Update records in Access.

    For j = 2 To 7
    rst(Cells(1, j).Value) = Cells(lngRow, j).Value
    Next j
    rst.Update

  11. #11
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Updating multiple records in Access table using excel VBA

    Code tags again please. (always required when posting code)

  12. #12
    Forum Contributor
    Join Date
    01-21-2012
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2010
    Posts
    102

    Re: Updating multiple records in Access table using excel VBA

    Man, I keep forgetting about those tags. Thanks!

    Please Login or Register  to view this content.

  13. #13
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Updating multiple records in Access table using excel VBA

    We are back to what I posted before except your ID field is probably numeric so:
    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    01-21-2012
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2010
    Posts
    102

    Re: Updating multiple records in Access table using excel VBA

    It looks like we're close but I'm still missing something. Maybe if you had a copy of the excel file it would help. I've attached it. You will see that there are several scripts including one that creates a database and puts it in the directory C:\Admin.
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    01-21-2012
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2010
    Posts
    102

    Re: Updating multiple records in Access table using excel VBA

    Hey OnErrorGoto0, Sorry about the late response. I just tried the code but it seems to be be taking a long time to run. Did you run into that as well?

  16. #16
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Updating multiple records in Access table using excel VBA

    Sorry - I forgot a key line! Before the Loop line you need:
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1