Importing into Access

[hewstone999]

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"

 

[jdraw]

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"

After you run this code and populate your table:

Run another bit of SQL, somethng along this sample:

UPDATE coltext SET TABLE_NAME = "MPI_IDS_IFFs"

 

[Guus2005]

Don't drop the table, just empty it. Execute the DoCmd.TransferSpreadsheet command
Noneed to alter the table any more, only when the table doesn't exist.