Results 1 to 4 of 4
  1. #1
    Bkper087 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    May 2014
    Posts
    81

    Change the names of columns in a table using VBA

    I am building version 5ish of a budget database that I have used through the years. I get budgets in an excel format from our accounting firm that I would like to be able to tie into my personal tracking database. This way I can compare their actual numbers with what I am tracking and find discrepancies, etc. The end goal is to be able to import a budget (excel sheet) and via some vba/ click of a button, change the column names in the newly imported table (22 in total).



    I've setup an import form frmBudgetImport. On that form I have two text boxes txtBudgetNumber, txtImportedTableName and a button, btnUpdateBudgetNumber.

    For example if the two text boxes have the following info in them:
    txtBudgetNumber = 800
    txtImportedTableName = tbl800

    I want the code to update the 22 column names in the newly imported table, tbl800 to a predetermined naming structure. Example of this naming structure below:

    B800_1
    B800_2
    B800_3
    etc.

    Thoughts? As always, thanks in advance!

  2. #2
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Hmm, not the way I would do it. Bring in the Excel spreadsheet into a Template used to just dump the data then append it to the live table matching up the columns that way. It also affords you the chance of the giving the data a once over before pushing to the live table.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I might be more inclined to transferspreadsheet into a table by appending. Then run append query to transfer from that to a "fixed" table so that you can avoid the usual issues of not getting the correct data types in a transfer from Excel. Then you'd be able to append from a field whose name you don't like to the name you do like in the 2nd table. Often the transfer gives you text for numbers, dates and such, but appending numbers that are really text to a field that has the correct data type is a common approach.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Link the Excel table into your Access database. Create an Access table for imported data.
    To import data into Access table, ran a query like:
    Code:
    INSERT INTO AccessTable (field1, field2, ...)
    SELECT Expression1, Expression2, ... FROM LinkedExcelTable
    NB! The order, field types and number of expressions in SELECT clause must match with field list!

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

Similar Threads

  1. Replies: 7
    Last Post: 03-21-2018, 04:58 AM
  2. Replies: 2
    Last Post: 02-03-2018, 06:54 PM
  3. Replies: 4
    Last Post: 11-29-2017, 10:01 AM
  4. Replies: 2
    Last Post: 07-03-2014, 08:28 PM
  5. Separate Names into Columns
    By bulbul4u in forum Access
    Replies: 2
    Last Post: 04-01-2011, 12:01 PM

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