Solved

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

Posted on 2014-01-20
2
628 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now