Solved

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

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

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
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:bmck0426
Comment Utility
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

743 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

17 Experts available now in Live!

Get 1:1 Help Now