I import data from a Spreadsheet into an Access Table. When I import the data I also want to populate one column with name of the Access Table. i.e. i’ve got a table called: MPI_IDS_IFFs so I want my fields to look like this:

ID --- USERNAME --- FORMANE --- TABLE_NAME
01 Hewston Richard MPI_IDS_IFFs
02 Smith John MPI_IDS_IFFs



So far I have this code which imports the data from the spreadsheet into an Access Table.

How can I edit this so I can also insert the table name Value into the “TABLE_NAME” column?

Dim sBuffer As String
Dim file As stcFileStruct

file.strDialogtitle = "Select file to import"
ShowOpenDialog file
sBuffer = file.strFileName
sBuffer2 = file.strFileTitle
strImport = sBuffer
strImport2 = sBuffer2

temp2 = Left$(strImport2, InStrRev(strImport2, ".") - 1)
temp1 = InputBox("Save Table Name as: (Table Name cannot contain 'Spaces' or 'File Extensions')", "SaveAs", temp2)

coltext = temp1

On Error Resume Next
Exists = IsObject(CurrentDb.TableDefs(coltext))

If Exists Then
    Dim Response As Integer
    Response = MsgBox(prompt:="Table already exsists. Overwrite table?", Buttons:=vbYesNo)

    If Response = vbYes Then
        SQLDEL = "DROP TABLE " & coltext
        DoCmd.RunSQL SQLDEL
        SQL = "Create TABLE " & coltext & " (ID Text)"
        DoCmd.RunSQL SQL

        SQL2 = "ALTER TABLE " & coltext & " ADD COLUMN USERNAME Text"
        SQL3 = "ALTER TABLE " & coltext & " ADD COLUMN FORMANE Text"
        SQL4 = "ALTER TABLE " & coltext & " ADD COLUMN TABLE_NAME Text"

        DoCmd.RunSQL SQL2
        DoCmd.RunSQL SQL3
        DoCmd.RunSQL SQL4
        DoCmd.TransferSpreadsheet acImport, 8, coltext, strImport, True, "01-Fields!A1:I5000"

Why would you want to store the name of the table the data is in when it is the table name itself? IOW, why would you query a column in a table when you must know the very table name you are looking for in order to query it?

Be that as it may, you could create an update query that fills in this column where column = ""

UPDATE MPI_IDS_IFFs
SET MPI_IDS_IFFs.TABLE_NAME = "MPI_IDS_IFFs"
WHERE (((MPI_IDS_IFFs.TABLE_NAME) Is Null)) OR (((MPI_IDS_IFFs.TABLE_NAME)=""));

I think there are some basic design problems that force this solution. HTH


mmcdonal

Look it up at: http://wrox.books24x7.com