Results 1 to 4 of 4
  1. #1
    rscott1989 is offline Novice
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    9

    Update existing table in Access from Excel using VBA

    Hello everyone,



    I have a form with a button. When this button is pressed I want it to update a table in my database. I don't want to use the wizard to accomplish this, but only VBA. I have it now to where it will add a new table when the button is clicked. I just need it to update an already existing table with the data from the Excel document.

    So for the button i have right now is this

    Private Sub Command0_Click()
    Dim filepath As String
    Dim User As String


    User = Environ("username")
    filepath = "C:\Users\" & User & "\Downloads\Borrower.xlsx"
    DoCmd.TransferSpreadsheet acImport, , "Borrower_Import", filepath, True
    End Sub

    The table I am trying to update is "Borrower"

    Any information you can provide is greatly appreciated!

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    You can certainly use VBA to UPDATE records without using any SQL. However, I suggest creating some query objects as a way to build a model. In other words, accomplish what you want via one or more query objects and then determine how you can leverage VBA. Perhaps you can start by linking to a copy of your Excel Spreadsheet.

  3. #3
    rscott1989 is offline Novice
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    9
    Thanks for the response!
    I created an append query from the button I had already created. So now I still have the same code but when the button is clicked the data is pulled from the imported table to my already existing Borrowers table. Then the temporary imported table is deleted.

    What if I want to select only a certain table in the excel document? I have three tables total I am going to be updating with three seperate buttons. Is it possible to pull all of this from the same excel spreadsheet. I dont see a place in the code i used to place the name of the sheet I want to use. I have Borrower, Movie and MovieInfo for sheet namee. Is it possible to call the specific sheet?

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    When I import a specific worksheet, I use VBA to automate Excel. I think you can use the Wizard to link to a specific worksheet. However, linking to Excel will make the file Read Only. You might be able to use the Wizard to create, name, and save an import procedure that goes after a specific Worksheet. I do not recall if the Import Wizard asks about Worksheets. If it does, you can save the procedure the Wizard steps you through and then call the saved Import Procedure via VBA.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-25-2015, 01:44 PM
  2. Replies: 1
    Last Post: 04-25-2015, 01:41 PM
  3. Replies: 7
    Last Post: 03-22-2015, 02:29 AM
  4. Update Existing Access Tables with Data from Excel
    By ChelseaC in forum Import/Export Data
    Replies: 7
    Last Post: 08-10-2014, 04:28 PM
  5. Import Excel data into existing Access Table
    By octsim in forum Import/Export Data
    Replies: 4
    Last Post: 10-24-2013, 07:21 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums