Importing Excel worksheet range to Ms Access Table

Question

Good Afternoon,

I have created a Macro that uploads data to a access database ( both on my desktop). The problem is it I keep getting errors when I try to expand the range.

I presumed it would be something simple but seems to be something I am overlooking.

here is the code - basically I would like to include the column or set it to a dynamic range? can you please help?

Sub AccessCode()

    Application.ScreenUpdating = False

    Dim db As Database
    Dim rs As DAO.Recordset

    Set db = OpenDatabase("C:\Users\user\Desktop\Test Copy.accdb")
    Set rs = db.OpenRecordset("Fact Table", dbOpenTable)

    rs.AddNew
    rs.Fields("GUID") = Range("g2").Value
    rs.Fields("StageID") = Range("h2").Value
    rs.Fields("Sync Date") = Range("i2").Value
    rs.Fields("Forecast HP") = Range("j2").Value
    rs.Fields("Owner Id") = Range("k2").Value
    rs.Fields("Recent Modified Flag") = Range("L2").Value
    rs.Fields("Upload Date") = Range("M2").Value

    rs.Update
    rs.Close
    db.Close

    Application.ScreenUpdating = True
    MsgBox " Upload To PMO Database Successful."

End Sub

Erik A · Accepted Answer · 2018-02-14T15:59:13.100

4

You can use a query instead of iterating through a recordset:

Sub AccessCode()
    Application.ScreenUpdating = False
    Dim db As Database
    Dim rs As DAO.Recordset

    Set db = OpenDatabase("C:\Users\user\Desktop\Test Copy.accdb")
    db.Execute "INSERT INTO [Fact Table] ([GUID], [StageID], etc) " & _
    "SELECT * FROM [SheetName$G:M] " & _
    "IN """ & ActiveWorkbook.FullName & """'Excel 12.0 Macro;HDR=No;'"
End Sub

This has numerous advantages, such as often being faster because you don't have to iterate through all the fields.

If you would trigger the import from Access instead of Excel, you wouldn't even need VBA to execute the query.

edited Feb 14 '18 at 15:59

answered Feb 14 '18 at 15:10

Erik A

31,639
12
42
67

The `FROM` clause should have Excel sheet name with range, `SheetName$[G:M]`. First time I ever seen `IN` used with Excel workbooks. But with double/single quote mix, I would use `FROM [Excel 12.0 Xml;HDR=No;Database=C:\Path\To\Workbook.xlsx].[SheetName$G:M]`. But adding this to my notes. – Parfait Feb 14 '18 at 15:50
Yes, the quote mix is a bit weird. I've adopted it to avoid Access from casting two single quotes to a literal double quote in the SQL, and to not have a _lot_ of quotes to account for that. The non-IN clause syntax is much more clear (I'm a bit less familiar with it). Specifying the sheet name is only necessary when there are multiple sheets, but a nice addition. I've edited into my answer. – Erik A Feb 14 '18 at 15:56
Thanks for the response! agreed a trigger from access would be easier but I'm developing this for someone that doesn't want to use access only excel sheets. I am now getting an error 3127 - my table names are correct but its saying that there an unknown field - anything obvious I'm missing? – SIRO1 Feb 14 '18 at 16:49
Have you replaced the `etc` with all the other fields? That's short for [et cetera](https://en.wikipedia.org/wiki/Et_cetera) – Erik A Feb 14 '18 at 16:59
Yep sure have - "INSERT INTO [Fact Table] ([GUID],[StageID],[EDB Sync Date],[Forecast HP],[Owner Id],[Recent Modified Flag],[Upload Date]) " & _ "SELECT * FROM [EDITS$G:M] " & _ "IN """ & ActiveWorkbook.FullName & """'Excel 12.0 Macro;HDR=No;'" - I've tried using underscores, exclamation marks thinking that is was something stupid. is there anything in the above that sticks out as a problem? would the spaces be causing the problem? – SIRO1 Feb 15 '18 at 10:20
Eh, that certainly is strange. You could use `SELECT F1, F2, F3, F4, F5, F6, F7` instead of `SELECT *` to make the fields from the lower part explicit. Spaces shouldn't be a problem if you're properly using brackets, which you are as far as I can see. – Erik A Feb 15 '18 at 10:30
Select F1 etc. worked perfectly! thanks for your help Erik!! much appreciated! – SIRO1 Feb 15 '18 at 11:06

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

1

Change the rs section to this one:

With rs
    .addnew
    !GUID = Range("g2").Value
    !StageID = Range("h2").Value
    '...etc
    .Update
End With

MSDN source

Use the AddNew method to create and add a new record in the Recordset object named by recordset. This method sets the fields to default values, and if no default values are specified, it sets the fields to Null (the default values specified for a table-type Recordset).

After you modify the new record, use the Update method to save the changes and add the record to the Recordset. No changes occur in the database until you use the Update method.

Edit: This is how your code should look like, when you change the rs section with the code above:

Sub AccessCode()

    Application.ScreenUpdating = False

    Dim db As Database
    Dim rs As DAO.Recordset

    Set db = OpenDatabase("C:\Users\user\Desktop\Test Copy.accdb")
    Set rs = db.OpenRecordset("Fact Table", dbOpenTable)
    
    With rs
        .addnew
        !GUID = Range("g2").Value
        !StageID = Range("h2").Value
        '...etc
        .Update
        .Close
    End With

    Application.ScreenUpdating = True
    MsgBox " Upload To PMO Database Successful."

End Sub

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 14 '18 at 13:44

Vityata

42,633
8
55
100

I'm getting a "compile error - expected end with" I need to include all of column G also - the above doesn't seem to work – SIRO1 Feb 14 '18 at 14:06
@SIRO1 - compile errors are the best ones you can get in VBA. (no irony - they are the easiest & quickest to solve in debugging). Give it a few tries, you probably are missing `End With` at the end or something. – Vityata Feb 14 '18 at 14:09
Ah Yes End With was the problem - which brings me back to setting the ranges which is still the big issue? any thoughts? appreciate you input! – SIRO1 Feb 14 '18 at 14:17
@SIRO1 - have you tried the code under the **Edit**? Does it still throw errors? – Vityata Feb 14 '18 at 14:19
!GUID = Range("G:G").Value I would of thought this would work but it doesn't – SIRO1 Feb 14 '18 at 14:21
@SIRO1 - yes, it does not work - you should refer one row per recordset. Thus `!GUID = Range("g2").Value` is what should be working. – Vityata Feb 14 '18 at 14:23
how do I return all the records than? is there a dynamic way? the record number will be different per week – SIRO1 Feb 14 '18 at 14:25
@SIRO1 - with a loop through all rows. – Vityata Feb 14 '18 at 14:29
Great! would you have to have code for that? I am fairly new to Macros – SIRO1 Feb 14 '18 at 14:31
@SIRO1 - here you go - https://stackoverflow.com/questions/1463236/loop-through-each-row-of-a-range-in-excel – Vityata Feb 14 '18 at 14:32

Brad · Answer 3 · 2018-02-14T18:44:20.503

1

Just thought I'd add in an alternative to @Erik von Asmuth's excellent answer. I use something like this in a real project. It's a little more robust for importing a dynamic range.

Public Sub ImportFromWorksheet(sht As Worksheet)

    Dim strFile As String, strCon As String

    strFile = sht.Parent.FullName

    strCon = "Excel 12.0;HDR=Yes;Database=" & strFile

    Dim strSql As String, sqlTransferFromExcel As String

    Dim row As Long
    row = sht.Range("A3").End(xlDown).row
    Dim rng As Range

    sqlTransferFromExcel = " Insert into YourTable( " & _
                " [GUID] " & _
                " ,StageID " & _
                " ,[sync Date] " & _
                " ,[etc...] " & _
                " ) " & _
                " SELECT [GUID] " & _
                " ,StageID " & _
                " ,[sync Date] " & _
                 " ,[etc...] " & _
                " FROM [{{connString}}].[{{sheetName}}$G2:M{{lastRow}}]"

    sqlTransferFromExcel = Replace(sqlTransferFromExcel, "{{lastRow}}", row)
    sqlTransferFromExcel = Replace(sqlTransferFromExcel, "{{connString}}", strCon)
    sqlTransferFromExcel = Replace(sqlTransferFromExcel, "{{sheetName}}", sht.Name)

    CurrentDb.Execute sqlTransferFromExcel

End Sub

edited Feb 14 '18 at 18:44

answered Feb 14 '18 at 17:36

Brad

11,934
4
45
73

That's certainly more nicely wrapped, full-featured solution, but what's with the `ADODB.Connection` and `ADODB.Recordset`? Also, you're assuming row 1 contains headings (which it probably should, but that hasn't been specified) – Erik A Feb 14 '18 at 17:39
@ErikvonAsmuth Ah you're so right. I was using this in a different way before. I guess I didn't totally clean it up. And you're right about the column headers. I guess You could modify the `strCon` string/have that be an input parameter. – Brad Feb 14 '18 at 18:43
Looks great! but this would be importing from access? i am trying to ( and failing so far) to do this from excel into access – SIRO1 Feb 15 '18 at 10:22
@SIRO1 if you use your `db.` instead of `CurrentDb`, you can run this from Excel – Erik A Feb 15 '18 at 10:31

Importing Excel worksheet range to Ms Access Table

3 Answers3

Linked