Referencing a newly imported table in Access-CodePudding

Wow, my first stack question despite using the answers for years. Very exciting.

I'm fairly new to VBA and Excel and entirely new to Access, full disclosure. So Im trying to create a core database of lab reports, and I have a form for entering the information about a new report which adds info about the report to a master table of all reports, including assigning it a unique label. After entering the info, I then have a button which allows the user to select the Excel .csv file accompanying the report and imports it into the DB as a new table. It returns a success or error message. And it works! (And the code came from somewhere on here)

The problem is I'd like to then add a field to the new table that adds the label assigned to the new report to all records so it can be referenced by queries through the use of that label. I'd also like to add an index field to the new table if possible as it doesn't seem like importing the .csv as a table creates an index. I figure I'll make another sub that gets passed the new report name as a name for the new field (which will also be the value of the field through all records) and the table to append that to.

How do I pass this sub the newly imported table if I just imported it? I need this all to work from the button as it will mostly be my manager using this form/button to import new files, and they won't be able to just manually go into the tables as they are created and add fields (yes, I know that's the obvious solution, but trust me...this must be a button)

Heres the code I'm using (yes, I know lots of it could be done differently but it works!)

Public Function ImportDocument() As String
    On Error GoTo ErrProc

    Const msoFileDIalogFilePicker As Long = 3
    Dim fd As Object
    Set fd = Application.FileDialog(msoFileDIalogFilePicker)

    With fd
        .InitialFileName = "Data Folder"
        .Title = "Enthalpy EDD Import"
        With .Filters
            .Clear
            .Add "Excel documents", "*.xlsx; *.csv", 1
        End With
        .ButtonName = " Import Selected "
        .AllowMultiSelect = False   'Manual naming currently requires one file at a time be imported

       'If aborted, the Function will return the default value of Aborted
        If .Show = 0 Then GoTo Leave  'fb.show returns 0 if 'cancel' is pressed
    End With

    Dim selectedItem As Variant
    Dim NewTableName As String
    
    NewTableName = InputBox(Prompt:="Enter the Report Name", _
        Title:="Report Name")
        
    For Each selectedItem In fd.SelectedItems  'could later be adapted for multiple imports
        DoCmd.TransferText acImportDelim, , NewTableName, selectedItem, True  'Imports csv file selected, true is 'has headers'
    Next selectedItem

   'Return Success
   ImportDocument = "Success"
   
   'Append report label and index
   AppendReportLabelField(NewTableName, #What to put here as the table to append to?)

'error handling
Leave:
    Set fd = Nothing
    On Error GoTo 0
    Exit Function

ErrProc:
    MsgBox Err.Description, vbCritical
    ImportDocument = "Failure" 'Return Failure if error
    Resume Leave
End Function

The AppendReportLabelField would get passed the name (and value) of the field and the name of the (newly imported) table. How do I pass it the table? NewTableName is just a string currently. If I can pass the new sub the table I'm sure the rest will be simple.

Thanks in advance for the help!

CodePudding user response：

Consider storing all user input data in a single master table with all possible fields and use a temp, staging table (a replica of master) to migrate CSV table to this master table. During the staging, you can update the table with needed fields.

SQL (save as stored queries)

(parameterized update query)

PARAMETERS [ParamReportNameField] TEXT;
UPDATE temptable
SET ReportNameField = [ParamReportNameField]

(explicitly reference all columns)

INSERT INTO mastertable (Col1, Col2, Col3, ...)
SELECT Col1, Col2, Col3 
FROM temptable

VBA

...

' PROCESS EACH CSV IN SUBSEQUENT SUBROUTINE
For Each selectedItem In fd.SelectedItems 
    Call upload_process(selectedItem, report_name) 
Next selectedItem

Sub upload_process(csv_file, report_name)
    ' CLEAN OUT TEMP TABLE
    CurrentDb.Execute "DELETE FROM myTempStagingTable"

    ' IMPORT CSV INTO TEMP TABLE 
    DoCmd.TransferText acImportDelim, , "myTempStagingTable", csv_file, True 

    ' RUN UPDATES ON TEMP TABLE
    With CurrentDb.QueryDefs("myParameterizedUpdateQuery")
       .Parameters("ParamReportNameField").Value = report_name
       .Execute dbFailOnError
    End With

    ' RUNS APPEND QUERY (TEMP -> MASTER)
    CurrentDb.Execute "myAppendQuery"
End Sub

If CSV uploads vary widely in data structure, then incorporate an Excel cleaning step to standardize all inputs. Alternatively, force users to use a standardized template. Staging can be used to validate uploads. Databases should not be a repository of many, dissimilar tables but part of a relational model in a pre-designed setup. Running open-ended processes like creating new tables by users on the fly can cause maintenance issues.