INSERT INTO query in VBA

1
votes

Im using Access 2013 and Excel 2013. In terms of References, I am using Microsoft Office 15.0 Access database engine Object Library.

So I am trying to run an INSERT INTO query from VBA. The worksheet has a list of part numbers, which I used this code to convert into an array.

Function partArray()
    Dim partList() As Variant
    Dim partArr(10000) As Variant
    Dim x As Long

    partList = ActiveWorkbook.Worksheets("Parts").ListObjects("Parts").ListColumns("Part Number").DataBodyRange.Value

    For x = LBound(partList) To UBound(partList)
        partArr(x) = partList(x, 1)
    Next x

    partArray = partArr

End Function

Now I am trying to use an INSERT INTO query to input these part numbers into a table in access. Any idea how I can do this?

2
sqlexcelvbams-access
How many row sin Excel are you trying to move into Access? - Brad
You can't do it directly with an array. You need to use a loop and create the INSERT string like you would if you were typing it manually. - SandPiper
about 1000 rows. so create aString and then loop each entry in the array. in each loop, aString & entry & ", ". Will that work? Or do I need to convert all of the entry into strings? - J. Doe

2 Answers

0
votes

You should use ADO to connect between Excel and Access. It will be a reference under Tools/References in the VBE. Using ADO you can run SQL statements. You can define your table in Excel as the origin table and then read data from that, put them into a recordset and then write the recordset into an Access table. There are plenty of examples on the internet. You can start with this: https://www.exceltip.com/import-and-export-in-vba/export-data-from-excel-to-access-ado-using-vba-in-microsoft-excel.html

0
votes

Whoa! I think your approach is totally wrong. Try something like this.

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.Jet.OLEDB.4.0; " & _
        "Data Source=C:\FolderName\DataBaseName.mdb;"
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable  
    ' all records in a table
    r = 3 ' 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("FieldName1") = Range("A" & r).Value
            .Fields("FieldName2") = Range("B" & r).Value
            .Fields("FieldNameN") = Range("C" & 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

Or, this.

Sub DAOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
    Set db = OpenDatabase("C:\FolderName\DataBaseName.mdb") 
    ' open the database
    Set rs = db.OpenRecordset("TableName", dbOpenTable) 
    ' get all records in a table
    r = 3 ' 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("FieldName1") = Range("A" & r).Value
            .Fields("FieldName2") = Range("B" & r).Value
            .Fields("FieldNameN") = Range("C" & 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
    db.Close
    Set db = Nothing
End Sub

Of course you could use the TransferSpreadsheet method if you want.

Option Explicit

Sub AccImport()
    Dim acc As New Access.Application
    acc.OpenCurrentDatabase "C:\Users\Public\Database1.accdb"
    acc.DoCmd.TransferSpreadsheet _
            TransferType:=acImport, _
            SpreadSheetType:=acSpreadsheetTypeExcel12Xml, _
            TableName:="tblExcelImport", _
            Filename:=Application.ActiveWorkbook.FullName, _
            HasFieldNames:=True, _
            Range:="Folio_Data_original$A1:B10"
    acc.CloseCurrentDatabase
    acc.Quit
    Set acc = Nothing
End Sub