Solved

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

Posted on 2014-01-20
2
630 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
2 Comments
 
LVL 84

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

821 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