Results 1 to 2 of 2

Thread: Export data from excel to access

  1. #1

    Export data from excel to access

    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am facing a problem while exporting data from excel to Access. The example provided is working well but after changing data with my own excel and access , an error message appears

    Can an body help me

    see files in attachment,

    Following code i am using

    Option Explicit
    Sub DB_Insert_via_ADOSQL()
    'Author       : Ken Puls (
    'Macro purpose: To add record to Access database using ADO and SQL
    'NOTE:  Reference to Microsoft ActiveX Data Objects Libary required
        Dim cnt As New ADODB.Connection, _
                rst As New ADODB.Recordset, _
                dbPath As String, _
                tblName As String, _
                rngColHeads As Range, _
                rngTblRcds As Range, _
                colHead As String, _
                rcdDetail As String, _
                ch As Integer, _
                cl As Integer, _
                notNull As Boolean
        'Set the string to the path of your database as defined on the worksheet
        dbPath = ActiveSheet.Range("B1").Value
        tblName = ActiveSheet.Range("B2").Value
        Set rngColHeads = ActiveSheet.Range("tblheadings")
        Set rngTblRcds = ActiveSheet.Range("tblRecords")
        'Concatenate a string with the names of the column headings
        colHead = " ("
        For ch = 1 To rngColHeads.Count
            colHead = colHead & rngColHeads.Columns(ch).Value
            Select Case ch
                Case Is = rngColHeads.Count
                    colHead = colHead & ")"
                Case Else
                    colHead = colHead & ","
            End Select
        Next ch
        'Open connection to the database
        cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & dbPath & ";"
        'Begin transaction processing
        On Error GoTo EndUpdate
        'Insert records into database from worksheet table
        For cl = 1 To rngTblRcds.Rows.Count
            'Assume record is completely Null, and open record string for concatenation
            notNull = False
            rcdDetail = "('"
            'Evaluate field in the record
            For ch = 1 To rngColHeads.Count
                Select Case rngTblRcds.Rows(cl).Columns(ch).Value
                        'if empty, append value of null to string
                    Case Is = Empty
                        Select Case ch
                            Case Is = rngColHeads.Count
                                rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "NULL)"
                            Case Else
                                rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "NULL,'"
                        End Select
                        'if not empty, set notNull to true, and append value to string
                    Case Else
                        notNull = True
                        Select Case ch
                            Case Is = rngColHeads.Count
                                rcdDetail = rcdDetail & rngTblRcds.Rows(cl).Columns(ch).Value & "')"
                            Case Else
                                rcdDetail = rcdDetail & rngTblRcds.Rows(cl).Columns(ch).Value & "','"
                        End Select
                End Select
            Next ch
            'If record consists of only Null values, do not insert it to table, otherwise
            'insert the record
            Select Case notNull
                Case Is = True
                    rst.Open "INSERT INTO " & tblName & colHead & " VALUES " & rcdDetail, cnt
                Case Is = False
                    'do not insert record
            End Select
        Next cl
        'Check if error was encounted
        If Err.Number <> 0 Then
            'Error encountered.  Rollback transaction and inform user
            On Error Resume Next
            MsgBox "There was an error.  Update was not succesful!", vbCritical, "Error!"
            On Error Resume Next
        End If
        'Close the ADO objects
        Set rst = Nothing
        Set cnt = Nothing
        On Error GoTo 0
    End Sub
    Attached Files Attached Files

  2. #2
    Acolyte maninweb's Avatar
    Join Date
    Mar 2011
    Excel Version
    2010, 2016, 2016 Insider
    Hi mubi_masti...

    the problem is the name of the field "Date" in your database table. When generating the INSERT statement, the SQL can not be executed as Date is also a function in Access. The simplest way to resolve the problem is IMHO just renaming the field "Date" to something like "myDate" both in the Access database and in the Excel file.

    Regards :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts