Solved

Excel VBA(Tansferring data excel to access table

Posted on 2016-09-25
11
106 Views
Last Modified: 2016-10-02
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
 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

Open in new window


thanks
0
Comment
Question by:surah79
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41815232
>>"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
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.
0
 

Author Comment

by:surah79
ID: 41815302
Hi Ryan

Please find attached the database and excel file.

Thanks
Add-Banker-System-Request-.xlsm
SDOD.mdb
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 41815319
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.
0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:surah79
ID: 41815338
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
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41815434
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

Open in new window

0
 
LVL 38

Expert Comment

by:PatHartman
ID: 41816577
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.
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41817210
@surah79,

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

Open in new window

0
 

Author Comment

by:surah79
ID: 41818863
hi Ryan

It is Still giving the same error.

thanks
0
 

Accepted Solution

by:
surah79 earned 0 total points
ID: 41818934
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 help
Public Function insertRecord() As Boolean

Dim SaveTime As Date
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lItem As Long
Dim listCount As Long
Dim fileTypes() As Variant
Dim docTypeString As String
Dim clipboard       As MSForms.DataObject
Dim i, j As Integer
Dim Lastrow As Integer

On Error GoTo Err:
'dataValidate(sourceField As String, dataEntered As String, Optional sendCompany As Boolean, _
'Optional sendTP As Boolean) As Boolean

For Each Cell In Sheet1.Range("a2:K1000")
    Cell.Value = Trim(Cell.Value)

    Cell.Value = Application.WorksheetFunction.Clean(Cell.Value)
    
Next

'ReDim fileTypes(1) As Variant

    
        

'//Database Location
'Changed so that people with different drive mappings wont have issues

Const DB_LOCATION = "\\au012\Tcsgroup\SCHTORS-\On-bData\BackUp\Test\SDOD.mdb"


    '//Table has a datecreated/datemodified timestamp for each record
    SaveTime = Now
    With ActiveSheet
        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
  i = 2
    '//Open Database
    If db Is Nothing Then
       Set db = DAO.Workspaces(0).OpenDatabase(DB_LOCATION)
    End If
 For j = 1 To Worksheets("Sheet1").Range("O4")
    '//Open Table
    If rs Is Nothing Then
        Set rs = db.OpenRecordset("System Access", dbOpenDynaset)
    End If
      
    
    '//Create a new record
    With rs
    
        .AddNew
        ![Brand_Name] = Sheet1.Cells(i, 1).Value
        '//isCompany has been removed. Defaults to false, database does not use.
        ![Staff-Group] = Sheet1.Cells(i, 2).Value
        ![System_Name] = Sheet1.Cells(i, 3).Value
        ![Banker_Name] = Sheet1.Cells(i, 4).Value
       ' ![custPosition] = Sheet1.Cells(2, 1).Value
        ![Batch] = Sheet1.Cells(i, 5).Value
       
            ![Request_Num] = Sheet1.Cells(i, 6).Value
            ![Request_Date] = Sheet1.Cells(i, 7).Value
            
            ![ENumber] = Sheet1.Cells(i, 9).Value
             ![MNumber] = Sheet1.Cells(i, 10).Value
             ![Status] = Sheet1.Cells(i, 11).Value
       ' ![docTypes] = docTypeString
       ![request_By] = Sheet1.Cells(i, 8).Value
      '  ![requestTime] = Now
        '//Insert Record into Database
        .Update
        insertRecord = True '//SUCCESSFUL INSERTION
        
       i = i + 1
              

    End With
 Next j
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
    Exit Function

Err:
    Select Case Err.Number
        Case 3024
            MsgBox "Error: Could not establish connection to DB: Could not find file"
            Exit Function
        Case 3008
            MsgBox "Error: Could not establish connection to DB: Opened exclusively"
            Exit Function
        Case -2147221040
            Resume Next
        Case Else
            MsgBox Err.Number & ": " & Err.Description
            Exit Function
        
    End Select
End Function

Open in new window

0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41819031
@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 help
glad that you resolved the issue using another approach. you may close this question when necessary.
0
 

Author Closing Comment

by:surah79
ID: 41825237
Didn't work for me
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question