Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 650
  • Last Modified:

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

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
Bobby McKnight
Asked:
Bobby McKnight
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Bobby McKnightProject Controls SpecialistAuthor Commented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now