Results 1 to 5 of 5
  1. #1
    kloun04 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Wisconsin
    Posts
    33

    "recordset was not created!" error for appending records in Access from Excel

    I have an Excel spreadsheet that I want to use to append a table in Access. I pulled VBA code from this website and was able to modify the code so that it works up to when I get "The recordset was not created!" error. Can someone please advise on what I'm overlooking? I contacted the author of the code, but I haven't heard back yet. Thanks.

    Sub AddRecordsIntoAccessTable()

    '-----------------------------------------------------------------------------
    'The macro opens the APP Orientation Access Database.accdb database and adds the rows from the sheet
    '"Excel Data" in the "APP Orientation - Day 2" table of the database.

    '-----------------------------------------------------------------------------


    'Declaring the necessary variables.
    Dim accessFile As String
    Dim accessTable As String
    Dim sht As Worksheet
    Dim lastRow As Long
    Dim lastColumn As Integer
    Dim con As Object
    Dim rs As Object
    Dim sql As String
    Dim i As Long
    Dim j As Integer

    'Disable the screen flickering.
    Application.ScreenUpdating = False

    'Specify the file path of the accdb file.
    accessFile = "U:\UWHealth\Nrsg-PCS\SpecialShares\APP Director\Education and Professional Development\Orientation\13. APP Orientation Access Database"

    'Ensure that the Access file exists.
    If FileExists(accessFile) = False Then
    MsgBox "The Access file doesn't exist!", vbCritical, "Invalid Access file path"
    Exit Sub
    End If

    'Set the name of the table you want to add the data.
    accessTable = "APP Orientation - Day 2"

    'Set the worksheet that contains the data.
    On Error Resume Next
    Set sht = ThisWorkbook.Sheets("Survey Responses")
    If Err.Number <> 0 Then
    MsgBox "The given worksheet does not exist!", vbExclamation, "Invalid Sheet Name"
    Exit Sub
    End If
    Err.Clear

    'Find the last row and last column in the given worksheet.
    With sht
    lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    lastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With

    'Check if there is data in the worksheet.
    If lastRow < 2 Or lastColumn < 1 Then
    MsgBox "There is no data in the given worksheet!", vbCritical, "Empty Data"
    Exit Sub
    End If

    'Create the ADODB connection object.
    Set con = CreateObject("ADODB.connection")

    'Check if the object was created.
    If Err.Number <> 0 Then
    MsgBox "The connection was not created!", vbCritical, "Connection Error"
    Exit Sub
    End If
    Err.Clear

    'Open the connection.
    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & accessFile

    'Create the SQL statement to retrieve the table data (the entire table).
    sql = "SELECT * FROM " & accessTable

    'Create the ADODB recordset object.
    Set rs = CreateObject("ADODB.Recordset")

    'Check if the object was created.
    If Err.Number <> 0 Then
    Set rs = Nothing
    Set con = Nothing
    MsgBox "The recordset was not created!", vbCritical, "Recordset Error"
    Exit Sub
    End If
    Err.Clear

    'Set the necessary recordset properties.
    rs.CursorType = 1 'adOpenKeyset on early binding
    rs.LockType = 3 'adLockOptimistic on early binding

    'Open the recordset.
    rs.Open sql, con



    'Add the records from Excel to Access by looping through the rows and columns of the given worksheet.
    'Here the headers are in the row 1 and they are identical to the Access table headers.
    'This is the reason why, for example, there are no spaces in the headers of the sample worksheet.
    For i = 2 To lastRow
    rs.AddNew
    For j = 1 To lastColumn
    'This is how it will look like the first time (i = 2, j = 1):
    'rs("FirstName") = "Bob"
    'rs(sht.Cells(1, j).Value) = sht.Cells(i, j).Value
    Next j
    rs.Update
    Next i


    'Close the recordet and the connection.
    rs.Close
    con.Close

    'Release the objects.
    Set rs = Nothing
    Set con = Nothing

    'Re-enable the screen.
    Application.ScreenUpdating = True


    'Inform the user that the macro was executed successfully.
    MsgBox lastRow - 1 & " rows were successfully added into the '" & accessTable & "' table!", vbInformation, "Done"

    End Sub


    Function FileExists(FilePath As String) As Boolean

    '--------------------------------------------------
    'Checks if a file exists (using the Dir function).
    '--------------------------------------------------

    On Error Resume Next
    If Len(FilePath) > 0 Then
    If Not Dir(FilePath, vbDirectory) = vbNullString Then FileExists = True
    End If
    On Error GoTo 0

    End Function

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Did you consider just linking the spreadsheet and then running query(ies) to copy data into normalized db tables and eliminate all that code? Spreadsheets are virtually never properly designed for use in a database so it's customary to copy the data into properly designed tables. If you don't know what normalization is, see

    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.com...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    For future, please post code between CODE tags to retain indentation and readability.

    There is no need to pull records from Access table since you want to add data, not manipulate existing.
    sql = "SELECT * FROM " & accessTable & " WHERE 1=0"

    Could possibly even not need recordset, review https://stackoverflow.com/questions/...s-access-table
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Given the inadvisable spaces and symbols, try

    accessTable = "[APP Orientation - Day 2]"

    Edit: You have invalid SQL without the brackets.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    kloun04 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Wisconsin
    Posts
    33
    Thanks everyone. I was able to make it work by running the macro from Access to Excel instead of vice versa.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 03-22-2021, 06:38 AM
  2. Replies: 1
    Last Post: 06-19-2020, 01:57 AM
  3. Replies: 4
    Last Post: 08-12-2014, 08:47 AM
  4. Replies: 1
    Last Post: 04-30-2012, 10:09 AM
  5. Replies: 1
    Last Post: 04-07-2010, 08:49 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums