Update a table using variable

Question

I'm pretty new to using VBA. I have an access database where the user clicks a button and this will upload multiple files.

The files uploaded are temp tables and get fields added to them etc. Once updates have been made to the temp tables, the records get transferred into a permanent table.

It uploads multiple files at once, so as files get uploaded the table they get input into tables called'temp_filename', with each file getting its own table.

Below is my code. For the alter table statement I want to upload the temp table which has just been created. As this table will be named something different every time I tried to assign it to a variable. I tried different syntax etc, however I keep getting errors. Can anyone see where I am going wrong? Any help would be appriciated.

Public Sub Import()

Dim oFileDiag As Office.FileDialog
Dim path As String: path = ""
Dim oFSO As New FileSystemObject
Dim FileSelected As Variant
Dim FileNameSelected As Variant
Dim UpdatedTableName As Variant

Set oFileDiag = Application.FileDialog(msoFileDialogFilePicker) ''Picks file to import
oFileDiag.AllowMultiSelect = True ''Allows multiple files to be selected
oFileDiag.Title = "Please select the reports to upload"
oFileDiag.Filters.Clear
oFileDiag.Filters.Add "Excel Spreadsheets", "*.xlsx, *.xls" ''Only allows xlsx and xls file types to upload

If oFileDiag.Show Then
    For Each FileSelected In oFileDiag.SelectedItems

        FileNameSelected = oFSO.GetFileName(FileSelected)
        UpdatedTableName = "temp_" & FileNameSelected

        If oFileDiag.SelectedItems.Count > 0 Then path = oFileDiag.SelectedItems(1)
            If Len(path) > 0 Then
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, UpdatedTableName, path, 1

            DoCmd.RunSQL "ALTER TABLE UpdatedTableName ADD COLUMN [Date_of_Report] TEXT(100);"

            MsgBox "The " & FileNameSelected & " file has been uploaded"

        Else
            MsgBox "File not found"
        End If
    Next
End If

It looks like DoCmd.RunSQL "ALTER TABLE UpdatedTableName ADD COLUMN [Date_of_Report] TEXT(100);" is treating UpdatedTableName not as a variable, but literally. I'm not familiar with Access VBA but shouldn't it be concatenated like a string? DoCmd.RunSQL "ALTER TABLE " & UpdatedTableName & " ADD COLUMN [Date_of_Report] TEXT(100);" Maybe you could drop the UpdatedTableName variable completely and instead write: DoCmd.RunSQL "ALTER TABLE temp_" & FileNameSelected & " ADD COLUMN [Date_of_Report] TEXT(100);" — ionizing
– ionizing, Commented Apr 27, 2020 at 13:19
Thanks @ionizing , I've tried both the following lines and still get a run time error 3293 DoCmd.RunSQL "ALTER TABLE" & UpdatedTableName & " ADD COLUMN [Date_of_Report] TEXT(100);" DoCmd.RunSQL "ALTER TABLE ' " & UpdatedTableName & " ' ADD COLUMN [Date_of_Report] TEXT(100);" — Amy
– Amy, Commented Apr 27, 2020 at 13:22
I'm not familiar enough with Access to help you, sorry. It sounds like Access is crashing, maybe at DoCmd.TransferSpreadsheet. You could add debug.print "whatever" commands after each DoCmd to see how far your code gets before crashing. View debug.print output in the Immediate window, which is Ctrl+G in the editor. — ionizing
– ionizing, Commented Apr 27, 2020 at 13:28

Applecore · Accepted Answer · 2020-04-27 13:37:01Z

1

You need to concatenate the table name into the SQL string:

    DoCmd.RunSQL "ALTER TABLE [" & UpdatedTableName & "] ADD COLUMN [Date_of_Report] TEXT(100);"

I've used square brackets just in case there are things like spaces in the table name.

Regards,

answered Apr 27, 2020 at 13:37

Applecore

4,0892 gold badges11 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update a table using variable

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related