2 years ago
#61401
rockatheman1
The INSERT INTO statement contains an unknown field name
I'm using the following code to export information from an excel file to an access database. It worked fine last time I used it but ever since I started it up today I've been running into the following issue.
Public Sub Export_to_ACCDB()
Set cn = CreateObject("ADODB.Connection")
dbPath = Application.ActiveWorkbook.Path & "\Newdatabase.accdb"
dbWb = Application.ActiveWorkbook.FullName
dbWs = Application.Worksheets("DATABASE").Name
scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
dsh = "[" & Application.ActiveSheet.Name & "$]"
cn.Open scn
'ssql = "INSERT INTO Database ([Column1],[Column2],[Column3]) "
ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh
cn.Execute ssql '<<< The error occurs here
End Sub
I get the following error running this code:
Run-time error '-2147217900 (80040e14)':
The INSERT INTO statement contains the following unknown field name: 'F12'. Make sure you have typed the name correctly, and try the operation again.
I don't have a field name called 'F12' in either my excel file or my access database. I can't seem to figure out what's going on.
- Column headers are written correctly.
- Column headers do not contain spaces or other irregularities.
EDIT:
Seems like Excel thinks you got a 12th column (maybe by accidentally entering something). Mark column L and delete it @FunThomas
This seemed to be the problem. I had to delete all the columns after the last one which contained the correct fieldname. Now it's working again.
excel
vba
ms-access
ms-access-2010
ms-access-2007
0 Answers
Your Answer