Access Form Dialog - Import Excel Working - Insert to Normalized Tables? 1

[misscrf]

I have a spreadsheet, which has been set up so that our users can go on site and be able to track what they are doing offline. When they come back to our “world”, we need to be able to import that spreadsheet into our database application. The application is 2010 Access, with linked SQL tables. The spreadsheet has 29 fields, which are a template. They will always be the same, whether there is info to fill out for every record. I did the following, in an attempt to set up a structure for handling this:

I created a table for importing all the records from any of these spreadsheets, and I upsized it to SQL. In the SQL table, I added some foreign key fields, as this will be a sort of temp table for the data to come in (and allow us to QC the data at a later time).

Of the 29 fields, 12 of them go into a main table for the tracking. The other 17 go into a 2nd table. In this 2nd table, it is normalized with a look-up for the “type” of what we are tracking. So that we can have 1 record for each thing we track, with copies of the types we are tracking, I have an un-normalized spreadsheet for use in the field, and once this workbook is imported, I need to insert the data into the appropriate tables, figuring I can update the working table with the foreign key of the normalized table record that gets created for that info.

I have been looking at some examples, but not sure how to best go about this. I am looking to create the following:

1. Have a form for entry of the main table data, and a button to prompt a dialog for a user to choose a spreadsheet (somewhere on the network)
2. Import all 29 fields into the “working” table
a. I may need to do some checks on the spreadsheet data, to require that if there is data on a row, 3 of the columns must have info (I would like to require 9 fields not be empty)
3. Insert the first 12 columns of data for all records that do not have a foreign key field id, indicating that that row has been inserted AND all the fields are not equal to a record in the main table (not a dupe)
4. Update the foreign key field in the working table to the ID for the main table row that was inserted
5. Insert the 9 columns into the type table for the type “original” with the corresponding main table foreign key
6. Update the foreign key for the type table record
7. Inset the 4 columns into the type table for the “replacement” with the corresponding main table foreign key
8. Update the foreign key for the type table record
9. Report that the import is done

Please let me know if this doesn’t make sense, or if you have any ideas/examples of how to get this going. Once I get a good push, I am usually off and running


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[lameid]

Seems like you want to do three things...

1 Import the Spreadsheet
2 Run queries to put the data where it belongs
3 Delete the table

You can do this through VBA code / module or Macro.

The below list is for VBA but there are usually equivalents for macros (for docmd.x type methods usually (maybe always, I don't use macros much) x is a macro action)

1. Docmd.transferspreadsheet
2. docmd.openquery or docmd.runsql or open a querdef object and execute it (you may also want to have a look at docmd.setwarnings for action query message supression)
3. I have a procedure I use for this...

Sub DeleteTable(strTable As String)
    'Deletes Table of specfied name
    'Intent is to delete temporary tables used for importing data
    On Error Resume Next
    DoCmd.DeleteObject acTable, strTable
End Sub

I find VBA more flexible and easier to read as everything ends up on a line at a time where as the steps in macro are in one place and the details when it is selected at the bottom. Conversely, Sharepoint for Access does not really support VBA so you are limited to Macros if going down that path of running access apps from sharepoint.

 

[misscrf]

Thank you for your response. I am trying to take this step by step, and I got the first part working. I have a command button on the form, which will open a windows dialog to choose the workbook an import the data. It is very nice and seamless:

Private Sub cmdImportSS_Click()
Dim dlg As FileDialog
Dim strTable As String
strTable = "tblCocWork"
Set dlg = Application.FileDialog(msoFileDialogFilePicker)
With dlg
    .Title = "Select the Excel file to import"
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "Excel Files", "*.xlsx", 1
    .Filters.Add "All Files", "*.*", 2
    If .Show = -1 Then
        strFileName = .SelectedItems(1)
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, strTable, strFileName, True, "MainSheet!"
    Else
        Exit Sub
    End If
End With

End Sub

These are my next steps -
'Validate that the first 3 columns have data that can be used - if not, submit report with issues? delete data, require re-import?
'import 12 columns to tblchainofcustody
'update each row in tblcocwork with id of tblchainofcustody
'import 9 orig columns to tblcocmediadetails with media type orig and fk of tblchainofcustody
'update each row intblcocwork with id of tblcocmediadetails for orig record
'import 4 replacement columns to tblcocmediadetails with media type replacement and fk of tblchainofcustody
'update each row intblcocwork with id of tblcocmediadetails for replacement record
'import 4 collection columns to tblcocmediadetails with media type collection and fk of tblchainofcustody
'update each row intblcocwork with id of tblcocmediadetails for collection record
'message to say how many rows imported and process is done
'refresh coc form

What I want to do now that I have the data into my "work" table, is to validate the data before I start appending it into normalized tables. Some discussions/thoughts on this have been to:

1 - Reject all data if any rows are incomplete
2 - Process the rows that are complete and have a flag column in the working table for those that validated, and those that didn't. Present a message to the person for the records that didn't validate and an option to open a form where they can fix the rows that aren't complete and then re-run
3 - Move all validated table to an archive table for historical purpose and move all data that is incomplete to another table for corrections

Are any of these a good process? Is there one that anyone would recommend? Is there a better way to go about it?

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[lameid]

Whether to reject or not is better than not has more to do with the process and people involved than what is best to implement in Access.

If you reject the whole file, people are usually more incentized to correct it.

On the other hand, if you have something like 100 orders and 1 from your smallest customer is holding up an order from your largest, that is not really good either.

For your options 2 vs 3, I would go with 3. When Access is involved, people that think they know what they are doing tend to do things like run a historical report without throwing out errors when the data is combined. Also if you are debugging a production issue it is a lot harder to mess up the setting of the flag if the data is in separate tables. And nobody wants to have to filter down to .5% of the data to look at errors when something funky sends you into the tables (granted queries work too).

 

[misscrf]

Thank you again for your response. Just to update you, I am not done yet. I posted a new thread for conditional formatting of a continuous form, which I have set up as a staging area for validating the data imported from the spreadsheet, before we append it to the normalized table structure.

That post is here:

thread702-1715037

Once I get to the next step (or bump in the road lol) I will post back!

misscrf

It is never too late to become what you could have been ~ George Eliot