Solved

How to import an excel file and add new fields if they don't currently exist?

Posted on 2014-01-20
2
634 Views
Last Modified: 2014-01-21
Okay, here's the situation.  I have a database application that will eventually be copied and distributed to end users to run a set of reports.  The database requires 2 files to be imported on a regular basis.  One of which is from an excel file that will be managed by one person and imported by that one person since it will be maintain in a secure folder on the network.  I setup some VBA code in the excel sheet to copy and format the relevant data into an export tab in the excel file.  Then I built a form with a button to import the excel sheet buy browsing to the file (file paths could change without my knowledge, so I can't use any static paths in the programming) and importing it using the TransferSpreadsheet function.  This works great, however, I run into a potential problem when the spreadsheet  manger needs to add a new column for a new project. The user will receive run-time error 2391 if someone doesn't go in and manually add the new column to the table.  I don't want anyone to have to manually add anything to the tables.  I need access to create a new field for any fields that don't already exists when the file is imported dynamically.  I've read a couple different methods for doing this, including creating a temp table to import the file to and then appending it to the existing table, but I haven't been able to get that to work.  I would appreciate any help.

Thanks
0
Comment
Question by:Bobby McKnight
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39795833
First: It's generally a bad idea to add fields to a live, in-use database. You'd be much better off importing the table as-is, and then reworking your downstream processes to manage the new fields.

As you've found in your research, in general it's best to import to a "staging" table before moving data in to your live table. The flow is this:

1) User selects Excel file to import
2) Import is performed, data is inserted into your staging table (tblStaging, perhaps)
3) Your code then validates the data in tblStaging, and moves it into the live tables.

If you must add new fields, then you can do so using several methods. DAO is a good choice, and is fairly easy to work with.

Dim dbs As DAO.Database
Dim tdfSource As DAO.TableDef
Dim tdfDest as DAO.TableDef
Dim fldSource As DAO.Field
Dim fldDest as DAO.Field

Set dbs = CurrentDB
Set tdfSource = Currentdb.TableDefs("YourStagingTable")
Set tdfDest = Currentdb.TableDefs("YourLiveTable")

For each fldSource in tdfSource.Fields
  On Error Resume Next
  Set fldDest = tdfDest.Fields(fldSource.Name)

  If Err.Number <> 0 Then
    '/ could not find the field, so it needs to be added:    
  End If

  Err.Clear  
Next

Set fldSource = Nothing
Set fldDesc = Nothing
Set tdfSource = Nothing
Set tdfDes = Nothing
Set dbs = Nothing

But - again - it would be better to review the process and insure you have no other avenues. It's rare in the extreme that you must add fields to an live database.
0
 

Author Closing Comment

by:Bobby McKnight
ID: 39798385
Thanks for the comments.  I think I will take your advice and use a solution that will prevent me from having to add new fields to the table.  From discussions with my co-workers, we should only have to add a field 1-3 times a year.  So, I plan on building the table with a preset number of fields and generic field names.  That way we can add a project to an existing field and inactive projects can be removed from the excel file but the field will remain.  This tool is used simply as quick way to produce reports with current data and not housing historical data so inactive projects are irrelevant in this case.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question