Results 1 to 3 of 3

Thread: [RESOLVED] Help with Appending Access from Excel

  1. #1

    Thread Starter
    New Member
    Join Date
    Sep 2020
    Posts
    1

    Resolved [RESOLVED] Help with Appending Access from Excel

    Hi,

    I have been creating a customer ordering system for a small local Butchers shop and have been using various tutorials to build on the functionality however I have hit a brick wall, any help would be much appreciated.

    Basically the operator builds an invoice based on customer requirement this gets transferred into access for later retrieval if required.

    The tutorial I was following showed how to draw the data from access into a form, edit then send back to database, everything works apart from the append function.

    The first item in the access database is the autonumber ID

    Private Sub cmdAppend_Click()
    'Declaring the necessary variables.
    Dim cnn As ADODB.Connection 'dim the ADO collection class
    Dim rs As ADODB.Recordset 'dim the ADO recordset class
    Dim dbPath As String
    Dim i As Integer
    Dim x As Integer
    'add error handling
    On Error GoTo errHandler:
    If Me.Arec1.Value = "" Then
    MsgBox "You must enter a valid ID number.", _
    vbOKOnly Or vbInformation, "Insufficent data"
    Exit Sub
    End If
    'get the path to the database
    dbPath = "A:\Database\CustomerData.accdb;"

    Set cnn = New ADODB.Connection ' Initialise the collection class variable

    'Connection class is equipped with a —method— named Open
    '—-4 aguments—- ConnectionString, UserID, Password, Options
    'ConnectionString formula—-Key1=Value1;Key2=Value2;Key_n=Value_n;
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath

    Set rs = New ADODB.Recordset 'assign memory to the recordset
    'Create the SQL statement to retrieve the data from table.
    rs.Open "SELECT * FROM Christmas " & _
    "WHERE ID = " & CLng(Me.Arec1), ActiveConnection:=cnn, _
    CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
    Options:=adCmdText

    If rs.EOF And rs.BOF Then
    'Close the recordet and the connection.
    rs.Close
    cnn.Close
    'clear memory
    Set rs = Nothing
    Set cnn = Nothing
    'Enable the screen.
    Application.ScreenUpdating = True
    'In case of an empty recordset display an error.
    MsgBox "There are no records in the recordset!", vbCritical, "No Records"
    Exit Sub
    End If
    With rs
    For i = 2 To 71
    rs(Cells(1, i).Value) = Me.Controls("Arec" & i).Value
    Next i
    rs.Update
    End With

    'clear the userform values
    For x = 1 To 71
    Me.Controls("Arec" & x).Value = ""
    Next

    'Close the recordset and the connection.
    rs.Close
    cnn.Close
    'clear memory
    Set rs = Nothing
    Set cnn = Nothing
    'refresh the listbox
    ImportUserForm
    'Enable the screen.
    Application.ScreenUpdating = True

    Me.lstDataAccess.RowSource = "Christmas"
    'Inform the user that the macro was executed successfully.
    MsgBox "Congratulations the data has been appended", vbInformation, "Append successful"
    'error handler
    On Error GoTo 0
    Exit Sub

    errHandler:
    'clear memory
    Set rs = Nothing
    Set cnn = Nothing
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Import_Data"

    End Sub

    Name:  Capture.jpg
Views: 115
Size:  40.7 KB

  2. #2
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Help with Appending Access from Excel

    It would be helpful if we knew what line was actually causing the error.

    Possibly useful link:

    https://social.msdn.microsoft.com/Fo...orum=accessdev

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: [RESOLVED] Help with Appending Access from Excel

    A drive-Letter "A"??? Floppy-Disk?

    IIRC i too had problems with something similiar.
    What cursor-Type do you use? I remember that i had to switch to server-side cursor to make it work

    And i agree with JDC: Which line makes the code go Kaboom?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Posting Permissions

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



Click Here to Expand Forum to Full Width