Renaming a column in temp table after import

[newbie30]

Hi,

Am basically importing data from EXCEL into Access and below is the code.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "DLPTemp3", FileName, True



Dim Rs1 As ADODB.Recordset

Set Rs1 = mdbx.OpenRecordset("SELECT * FROM DLPTemp3;", SeeChangesReadWrite)

DoCmd.Close acTable, "DLPTemp3"
mdbx.Execute ("DROP TABLE DLPTemp3")

However the Excel contains a field with a & (eg: test & Test) and I need to rename that to 'Test and Test' before importing into an SQL table.

Could someone pl tell how do I go about renaming in a tempdb.

Thanks

[aytee111]

You will import a spreadsheet to a temp table then use an append query to clean up the field names and the data to add it to a permanent table.

[newbie30]

Yes I will import it into a temp table and then after some validations import the field names and data from the temp table into a permanent one. Somehow I need to rename the field before the import into the permanent one. I tried this

str1 = "ALTER TABLE DLPTemp3 Add [Postage and packaging] money"
mdbx.Execute str1
str2 = "UPDATE DLPTemp3 SET DLPTemp3.[Postage & packaging] = DLPTemp3.[Postage and packaging] FROM DLPTemp3 JOIN DLPTemp3 N ON DLPTemp3.[Sales Record Number] = N.[Sales record number] "
mdbx.Execute str2
str3 = "Alter table DLPTemp3 drop column [Postage & packaging]"
mdbx.Execute str3

However the data isn't getting copied.it renames the field though.

Is there a better solution?

Thanks

[NoellaG]

Hi,

if you load the library (Tools -> References in VBA window) Microsoft ADO ext. for DDL and security, you can use the following function to alter the name of a column in a Access table with the name tbl1 (change the name according to your situation):
===================================
Public Function fRenameColumn(strOldColName As String, strNewColName As String) As Integer
On Error GoTo Err_fRenameColumn
Dim cat As New ADOX.Catalog
Dim tblT As ADOX.Table
Dim colC As ADOX.Column

Set cat.ActiveConnection = CurrentProject.Connection
Set tblT = cat.Tables("tbl1")
For Each colC In tblT.Columns
If colC.Name = strOldColName Then colC.Name = strNewColName
fRenameColumn = 1
Next colC

Exit_fRenameColumn:
Set colC = Nothing
Set tblT = Nothing
Set cat = Nothing
Exit Function

Err_fRenameColumn:
fRenameColumn = -1
Resume Exit_fRenameColumn

End Function

======================
Kind regards