none
Help Creating VBA to Export/Copy data from Excel to Access RRS feed

  • Question

  • Using Excel & Access 2013

    In Excel, I've a sheet called 'Access' that has relevant data located at (A2:U2).
    The Excel document will not always be in the same folder or even on the same computer.

    In MS Access I've a table called 'Tbl-Form' that I would like to append the Excel data into.
    The Access database is located at "F:\Team All\FileTrack.accdb" for all computers.

    I can manually copy the data from (A2:U2) in Excel and Paste/Append in the correct Table in Access without error.

    I have tried multiple iterations of VBA codes and do not seem to understand what is necessary to do this.

    I will take any and all help and be most appreciative.

    Thank you in advance.
    Friday, April 7, 2017 10:57 PM

All replies

  • Are you trying use menu/data/from acc?

    If you want use VBA reed about ADO/DAO


    Oskar Shon, Office System MVP - www.VBATools.pl
    if Helpful; Answer when a problem solved

    Monday, April 10, 2017 2:16 PM
    Answerer
  • No, I am not trying to use menu/data/from Access.
    Thank you

    Monday, April 10, 2017 3:12 PM
  • This is the current VBA code.

    Any ideas why I would received a Compile error: Syntax error on this line?

    "Data Source=C:\Users\Henry\Desktop\Tracking.accdb; Persist Security Info=False;"

    Sub ADOFromExcelToAccess()
    ' exports data from the active worksheet to a table in an Access database
    ' this procedure must be edited before use
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;"
    "Data Source=C:\Users\Henry\Desktop\Tracking.accdb; Persist Security Info=False;"
    
    
    
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "Tbl-Form", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    ' all records in a table
    r = 2 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0
    ' repeat until first empty cell in column A
    With rs
    .AddNew ' create a new record
    ' add values to each field in the record
    .Fields("ID") = Range("A" & r).Value
    .Fields("Date  Received") = Range("B" & r).Value
    .Fields("RD") = Range("C" & r).Value
    .Fields("Issue") = Range("D" & r).Value
    .Fields("Date Sent for Approval") = Range("E" & r).Value
    .Fields("Staff") = Range("F" & r).Value
    .Fields("Date Approval Received") = Range("G" & r).Value
    .Fields("Date Completed") = Range("H" & r).Value
    .Fields("Status") = Range("I" & r).Value
    .Fields("RIssue") = Range("J" & r).Value
    .Fields("Part Name") = Range("K" & r).Value
    .Fields("Part ID") = Range("L" & r).Value
    .Fields("Prog ") = Range("M" & r).Value
    .Fields("Enroll") = Range("N" & r).Value
    .Fields("Enrollment") = Range("O" & r).Value
    .Fields("Exit Date Removed") = Range("P" & r).Value
    .Fields("DA") = Range("Q" & r).Value
    .Fields("Office") = Range("R" & r).Value
    .Fields("Staff") = Range("S" & r).Value
    .Fields("Notes") = Range("T" & r).Value
    .Fields("Field1") = Range("U" & r).Value
    
    ' add more fields if necessary…
    .Update ' stores the new record
    End With
    r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    End Sub
    
    

    Monday, April 10, 2017 4:02 PM
  • I found my two quotes that seemed to be causing the first issue and this seems to have been resolved.

    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\Henry\Desktop\Tracking.accdb; Persist Security Info=False;"

    This is now causing the next issue.

    Runtime error '-2147217900 (80040e14)':

    Syntax error in FROM clause.

    rs.Open "Tbl-Form", cn, adOpenKeyset, adLockOptimistic, adCmdTable

    Any thoughts?



    • Edited by elzool Monday, April 10, 2017 5:43 PM
    Monday, April 10, 2017 4:08 PM
  • Okay, I keep hammering on this and getting a little further along.

    I found that the last line I mentioned needed square brackets [] around the Access table name. I did that and the last error went away as well.

    Now, I receive no errors, but nothing at all happens to my Access table.
    Nothing is being appended to the table.

    Current Code

    Sub ADOFromExcelToAccess()
    ' exports data from the active worksheet to a table in an Access database
    ' this procedure must be edited before use
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; 
    Data Source=C:\Users\Henry\Desktop\Tracking.accdb; Persist Security Info=False;"
    
    
    
    
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "[Tbl-Form]", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    ' all records in a table
    r = 2 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0
    ' repeat until first empty cell in column A
    With rs
    .AddNew ' create a new record
    ' add values to each field in the record
    .Fields("ID") = Range("A" & r).Value
    .Fields("Date  Received") = Range("B" & r).Value
    .Fields("RD") = Range("C" & r).Value
    .Fields("Issue") = Range("D" & r).Value
    .Fields("Date Sent for Approval") = Range("E" & r).Value
    .Fields("Staff") = Range("F" & r).Value
    .Fields("Date Approval Received") = Range("G" & r).Value
    .Fields("Date Completed") = Range("H" & r).Value
    .Fields("Status") = Range("I" & r).Value
    .Fields("RIssue") = Range("J" & r).Value
    .Fields("Part Name") = Range("K" & r).Value
    .Fields("Part ID") = Range("L" & r).Value
    .Fields("Prog ") = Range("M" & r).Value
    .Fields("Enroll") = Range("N" & r).Value
    .Fields("Enrollment") = Range("O" & r).Value
    .Fields("Exit Date Removed") = Range("P" & r).Value
    .Fields("DA") = Range("Q" & r).Value
    .Fields("Office") = Range("R" & r).Value
    .Fields("Staff") = Range("S" & r).Value
    .Fields("Notes") = Range("T" & r).Value
    .Fields("Field1") = Range("U" & r).Value
    
    ' add more fields if necessary…
    .Update ' stores the new record
    End With
    r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    End Sub


    • Edited by elzool Monday, April 10, 2017 6:02 PM Spelling error
    Monday, April 10, 2017 6:01 PM
  • Ok other direction :)

    My way.

    So I've Excel data range and empty ACC Database like:

    I should run meny selects to import datas from Excel to my "Tabela1" in ACC file

    To do that, I make that code to create "select into" row by row:

    Option Explicit
    
    Sub export_to_ACC()
    'MVP OShon from www.VBATools.pl
    Const Tabela$ = "Tabela1"
    Dim Pytanie_sql$
    Dim tbl(): tbl = Range("A1:C4") 'Excels range
    
    Dim c As New ADODB.Connection
    Dim d As New ADODB.Recordset
    Const plik$ = "C:\Temp\import.accdb" 'My file
    Dim ODBC$ 'https://www.connectionstrings.com/access/
      ODBC = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & plik & ";" & _
             "Persist Security Info=False;"
             
    If c.State <> 1 Then c.Open ODBC
    If d.State = 1 Then d.Close
    
    Dim x&, y&, kolumny$, wartosci$
    For x = 1 To UBound(tbl)
    wartosci = ""
        For y = 1 To UBound(tbl, 2)
            If x = 1 Then
                kolumny = kolumny & tbl(x, y) & ","
            Else
                If Len(tbl(x, y)) = Empty Then
                    wartosci = wartosci & "Null,"
                Else
                    If IsNumeric(tbl(x, y)) Then _
                        wartosci = wartosci & Replace(tbl(x, y), ",", ".") & "," Else _
                        wartosci = wartosci & "'" & tbl(x, y) & "',"
                End If
            End If
        Next
        If Len(wartosci) > 0 Then
        Pytanie_sql = "INSERT INTO " & Tabela & " (" & _
            Mid(kolumny, 1, Len(kolumny) - 1) & ") values (" & _
            Mid(wartosci, 1, Len(wartosci) - 1) & ")"
            
            d.Open Pytanie_sql, c, adOpenStatic, adLockOptimistic
        End If
    Next x
    End Sub
    

    Run code and open ACC file:


    Oskar Shon, Office System MVP - www.VBATools.pl
    if Helpful; Answer when a problem solved

    Monday, April 10, 2017 6:29 PM
    Answerer
  • I appreciate that and can import Excel data into Access from Access.

    Unfortunately this is going to have end users working in Excel, so I have to be able to Export into Access.
    If I have misunderstood you, I apologize.

    Monday, April 10, 2017 6:41 PM
  • I do not understand now :)

    In your code you use Excels Range and you talking now about "Access from Access"?

    Would you be a more specific? I create some addins used Excels interface and save data in Access that is a sample. Do you try to go that way?


    Oskar Shon, Office System MVP - www.VBATools.pl
    if Helpful; Answer when a problem solved

    Tuesday, April 11, 2017 9:30 PM
    Answerer
  • Good morning,

    What I meant was, that within MS Access, I can import the Excel data into MS Access and have no issues.
    Exporting the same data out of Excel and into Access is where I am having the issue.

    Unfortunately I am not able to use any addins.  :(


    Wednesday, April 12, 2017 4:25 PM