+ Reply to Thread
Results 1 to 11 of 11

Export Excel data into Access table - overwrite table data

  1. #1
    Registered User
    Join Date
    04-21-2009
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2007
    Posts
    42

    Export Excel data into Access table - overwrite table data

    Hi,

    I'm looking for some vba code that will allow me to export some data in a worksheet into an access database (.mdb) and overwrite the data in a given table (Main_Data_Table). The worksheet will be in exactly the same format as the access table so the data should just be able to copy over the information. There won't be any new entries added in this time either.

    I'm sure this should be a relatively simple operation I just haven't got the vba skills to pull something like this off, any help you could offer would be gratefully received as always.

    Thanks in advance

    Jon

  2. #2
    Registered User
    Join Date
    04-21-2009
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Export Excel data into Access table - overwrite table data

    If it helps, I already have the following vba code which appends a new record to the table in question, hopefully it shouldn't be too hard to update so it just overwrites the whole table:

    Please Login or Register  to view this content.
    Last edited by Jonsocks; 02-09-2012 at 12:25 PM. Reason: Pasted the wrong code!

  3. #3
    Forum Contributor
    Join Date
    12-26-2010
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    147

    Re: Export Excel data into Access table - overwrite table data

    Having problem posting code

    PART 1

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    12-26-2010
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    147

    Re: Export Excel data into Access table - overwrite table data

    Part 2
    Having problem posting code

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    12-26-2010
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    147

    Re: Export Excel data into Access table - overwrite table data

    Part 3
    Having problem posting code

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    12-26-2010
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    147

    Re: Export Excel data into Access table - overwrite table data

    PART 4

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    12-26-2010
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    147

    Re: Export Excel data into Access table - overwrite table data

    PART 5

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    12-26-2010
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    147

    Re: Export Excel data into Access table - overwrite table data

    PART 6

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    12-26-2010
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    147

    Re: Export Excel data into Access table - overwrite table data

    PART 7

    HI

    ==============
    Instructions:
    ==============
    (1) Create a new directory.
    (2) Create a new xlsm Excel file and put it in the new directory
    (3) Create a WS called WSData
    (4) (3) In the first row put these column titles [(3) columns] LastName FirstName Myref
    (5) Put 3 rows of data into the 3 columns - TEXT ONLY
    (6) Put the code below in a module in the new xlsm file
    (7) Create a New Blank Access ACCDB file
    (8) Create a new table - any name - and add some dummy data.
    (9) (8) This table will NOT be used.
    (10) Open and Close the ACCDB FILE to make sure it opens ok

    ==============
    The code
    ==============
    (1) 1 main Subroutine called - Public Sub Excel_To_Access_SelectInto()
    (2) The code is linear meaning - everything - is in the this 1 sub.
    (3) Once you get the hang of it - you can break the sub into smaller subs/functions.
    (4) There is a small function called ISFILE that verifies the Excel and Access files
    exits

    ==============
    How the code THINKS (1)
    ==============
    (1) Confirm files exist
    (2) Connect to Access and see if a variable named ms_ACCDB_DropTable that contains
    the table we want to write to exists. If it does KILL IT. If not - proceed.
    (3) Kill connection to Access

    ==============
    How the code THINKS (2)
    ==============
    (1) Strangely - we will connect to Excel to write to the Access table.
    (2) We will use the SQL thingie ** SELECT INTO ** to take the data on WSData
    and insert it into the Access table
    (3) BONUS - depending on the value of a boolean variable RATHER than creating a
    new table and writing records to it - a DIFFERENT SQL variable will **APPEND**
    records to the table - if it exists.

    ==============
    The code
    ==============
    (1) Has been tested under several conditions and works.

    ==============
    FLIES IN THE OINTMENT
    ==============
    I have NOT NOT NOT tested any of this but suspect ////
    ==============
    (1) Only after getting it to work per the instructions above - do the following.

    (a) Add 1 new column to the Excel ws called WSDATA - name the field MYInteger
    (b) Put some integer values in. It should work for the SELECT INTO sql.

    (c) Now it gets **HARDER** Add a date filed and rinse and repeat instructions above
    If it fails - not a code problem - rather (guessing) access has determined the field
    is a STRING field and NOT a DATE field. You will have to fiddle with e.g #01/01/2012#
    as the data format in the Excel WS. wHICH means you will have to create a function
    that converts your orginating data FROM:01/01/2012 TO #01/01/2012#

    (d) In one of your original text fields put a SINGLE APOSTROPHE within the letters.
    Guessing - this should BLOW UP the SELECT INTO - but not sure. If it does you need
    a function that replaces all text values that contain a SINGLE APOSTROPE with ???? (I forget)

    ==============
    No support
    ==============
    I did not want to leave you hanging so wrote this code. But I really cannot support
    further questions.

    ==============
    If it works
    ==============
    You can SLOWLY change the directory locations of the files which means you have to
    modify certain variables in the routine. This ISFILE function will always TRAP
    SITUATIONS WHERE THE eXCEL OR aCCESS FILE IS NOT FOUND

    HTH
    regards
    John

  10. #10
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Export Excel data into Access table - overwrite table data

    Is there any reason that you have to export from Excel to Access. It may be easier to import into Access from Excel. As to overwriting the tables, in your code, simply delete the data in the tables so that there is only a skeleton of the table to populate. Attached is an example of how I have done this in the past. To make this work, you will need to place the two excel files (this example brings in two files) on the desktop of your machine. This was written in AC 2002 and used on a XP Operating system, so if you are newer, you may have to change some of the paths.

    Alan
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  11. #11
    Forum Contributor
    Join Date
    12-26-2010
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    147

    Re: Export Excel data into Access table - overwrite table data

    Your second point first:

    <<so it just overwrites the whole table>>

    I interpreted this (TO MEAN) make a new table.

    However, I also included this (in the same routine):

    SQL_DELETE = "DELETE "
    SQL_DELETE = SQL_DELETE & "FROM "
    SQL_DELETE = SQL_DELETE & "[" & ms_ACCDB_AppendTable & "]"

    So he could do (1) Delete and (2) Append - his choice -
    if he wanted to go that route.

    At the end of the day I don't think anything is stationary - not in my experience - so should
    his "fixed" Excel WS grow from 5 columns to 10 columns to N columns - the current code wil
    deal with it - EXCEPT FOR - any quirky data thingies.

    With respect to the Export vs Import - I think it more natural to Import - but I struggled with
    that for 5 minutes - so went the other way.

    I will look at your code to see how you did it.

  12. #12
    Forum Contributor
    Join Date
    12-26-2010
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    147

    Re: Export Excel data into Access table - overwrite table data

    I looked at your code.

    You are basically suggesting that the poster use:

    (1) DoCmd.TransferSpreadsheet acImport

    and if he wants to

    (2) DoCmd.TransferSpreadsheet acExport

    from within an Access code module. And thats __Great__.

    The poster __should__ certainly investigate your solution.

    From what I read in the _flavor_ of his post - he wanted to hump ADO and VBA _and_ do so
    within Excel.

    Yes - maybe I read too much into it.

    But, in any event, that guided my solution.

    One niff-naff.

    <<As to overwriting the tables, in your code, simply delete the data in the tables so that there is only a skeleton of the table to populate. >>

    Private Sub Command2_Click()
    DoCmd.DeleteObject acTable, "tbl_GEM"
    DoCmd.DeleteObject acTable, "tbl_CAP"
    End Sub

    Behind that cake mix is a SQL Drop statement.

  13. #13
    Registered User
    Join Date
    04-21-2009
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Export Excel data into Access table - overwrite table data

    Hi all,

    Sorry it's taken me so long to come back and thank you all for your help - been a hectic few weeks!

    I ended up using a different method to get what I needed, which overwrites a single row when it is selected:

    Please Login or Register  to view this content.
    Thanks again for your help

    Jon

+ 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