surah79
asked on
Excel VBA(Tansferring data excel to access table
Hi guys
I am trying to use the below code to transfer the data in to access database. but I am getting an error like
"the INSERT INTO statement contains the following un known field name:'F12'.Make sure you have type the name correctly and try the operation again. I already check the fields name in database and in excel also
thanks
I am trying to use the below code to transfer the data in to access database. but I am getting an error like
"the INSERT INTO statement contains the following un known field name:'F12'.Make sure you have type the name correctly and try the operation again. I already check the fields name in database and in excel also
Sub Data_Transfer()
Dim cn As Object
'//I am using late binding for creating objects, this code will run on any version of Excel
Set cn = CreateObject("ADODB.Connection")
' //This line will set the path of Ms.Access database, in this case it is assumed that Ms.Access DB will be in same folder as in Excel File.
dbPath = Application.ActiveWorkbook.Path & "\SDOD.mdb"
' // This line will set the path of Active workbook
dbWb = Application.ActiveWorkbook.FullName
'// Get the Active sheet name, so that we can correctly Export data using this variable
dbWs = Application.ActiveSheet.Name
'// Set the connection, we are creating Excel workbook connection
scn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath
' //Another variable to hold worksheet name, but in this case it will //be in a format that is required to pass in open connection //method, which execute the SQL statement in next line
dsh = "[" & "Sheet1" & "$]"
'// Open the connection
cn.Open scn
'//Create SQL statement using proper sheet name dsh, created //before opening connection
ssql = "INSERT INTO Candidate ([Source],[C_Name])"
ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh
'//Run the SQL
cn.Execute ssql
End Sub
thanks
ASKER
Hi Ryan
Please find attached the database and excel file.
Thanks
Add-Banker-System-Request-.xlsm
SDOD.mdb
Please find attached the database and excel file.
Thanks
Add-Banker-System-Request-.xlsm
SDOD.mdb
Being an Access developer, I would approach this from the Access side and import the Excel data by linking to the workbook and running an append query.
In any event, the problem is most likely caused because the sheet has extraneous columns in it.
If you have ever had additional columns, Excel remembers them unless you delete them correctly. Simply selecting the columns and pressing the delete key, clears the contents but it does NOT delete the columns. You must select the columns and right -click to select delete. Same issue occurs with phantom rows. Since Excel is very flexible as to cell content, it is perfectly happy to have "empty" rows or columns because the data and presentation layers are merged since your sheet is most often laid out as a report rather than a table.
In any event, the problem is most likely caused because the sheet has extraneous columns in it.
If you have ever had additional columns, Excel remembers them unless you delete them correctly. Simply selecting the columns and pressing the delete key, clears the contents but it does NOT delete the columns. You must select the columns and right -click to select delete. Same issue occurs with phantom rows. Since Excel is very flexible as to cell content, it is perfectly happy to have "empty" rows or columns because the data and presentation layers are merged since your sheet is most often laid out as a report rather than a table.
ASKER
hi Pat
I am trying to make this excel as user form which will be used to collect/ input data in the access database.
I don't want everyone to go inside and run the append query as this will be a multi user database.
Thanks
I am trying to make this excel as user form which will be used to collect/ input data in the access database.
I don't want everyone to go inside and run the append query as this will be a multi user database.
Thanks
You must specify the two field names to pull data from:
ssql = "INSERT INTO Candidate ([Source],[C_Name]) "
ssql = ssql & "SELECT SomeField, SomeOtherField FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh
surah79,
I'm not sure why you are using Excel as a FE to Access. The process would be much simpler if you simply used an Access FE. If you are worried about licensing, don't. The Access runtime is free so you can distribute an Access FE to everyone and have it linked to the shared database. Download the free Access runtime engine from the Microsoft download site.
I'm not sure why you are using Excel as a FE to Access. The process would be much simpler if you simply used an Access FE. If you are worried about licensing, don't. The Access runtime is free so you can distribute an Access FE to everyone and have it linked to the shared database. Download the free Access runtime engine from the Microsoft download site.
@surah79,
based on the Excel file provided, you should try this:
based on the Excel file provided, you should try this:
ssql = "INSERT INTO [System Access] ([Banker_Name],[Staff-Group],[System_Name],[Request_Num],[Request_Date],[Request_By],[ENumber],[MNumber],[Status],[Batch],[Brand_Name])" & _
" SELECT [Banker_Name],[Staff-Group] ,[System_Name], [Request_Num], [Request_Date], [Request_By], [Enumber], [Mnumber], [Status], [Batch],[Brand_Name] " & _
" FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh
'//Run the SQL
cn.Execute ssql
ASKER
hi Ryan
It is Still giving the same error.
thanks
It is Still giving the same error.
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@surah79,
It is Still giving the same error.it was tested fine in my machine before I posted the scripts.
I have this now working but I have used the below code to get it work, hopefully it will help others. thanks a lot for your helpglad that you resolved the issue using another approach. you may close this question when necessary.
ASKER
Didn't work for me
can you provide us sample files of your Excel and Access db?
from that error, you may rename the field's name and select the correct fields before importing.