Export from Excel to AccessDB, error Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another

I try to export some data from excel to my access database, but on line 15 rs.open I get the error Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. I can't seem to figure out what is going wrong here. Any help would be appreciated, thanks!

Public Sub updateAntibiotics(abName As String, Optional startDate As Date, Optional stopDate As Date)
    On Error GoTo ErrorHandler
    
    Dim cn As ADODB.Connection, rs As ADODB.Recordset
    Dim currPath As String, DbPath As String
    Dim sProduct As String, sVariety As String, cPrice As Variant
    Dim patientID As Integer

    currPath = Application.ActiveWorkbook.Path
    DbPath = Left$(currPath, InStrRev(currPath, "\")) & "IZ Damiaan.accdb"
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Ace.OLEDB.12.0; Data Source='" & DbPath & "';"

    Set rs = New ADODB.Recordset
    rs.Open "Antibiotics", cn, adOpenKeyset, adLockOptimistic, adCmdTable

    patientID = Val(Sheets("PatientData").Range("A2"))

        rs.Filter = "fkPatientID='" & patientID & "' AND Antibiotic='" & abName & "' AND stopDate IS NULL"
        If rs.EOF Then
            Debug.Print "No existing record - adding new..."
            rs.Filter = ""
            rs.AddNew
            rs("fkPatientID").Value = patientID
            rs("Antibiotic").Value = abName
        Else
            Debug.Print "Existing record found..."
        End If
        If Not IsNull(startDate) Then rs("startDate").Value = startDate
        If Not IsNull(stopDate) Then rs("stopDate").Value = stopDate
        rs.Update
        Debug.Print "...record update complete."
        
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    
ErrorHandler:
    If Not rs Is Nothing Then
        If rs.State = adStateOpen Then rs.Close
    End If
    Set rstTitles = Nothing

    If Not cn Is Nothing Then
        If cn.State = adStateOpen Then cn.Close
    End If
    Set cn = Nothing

    If Err <> 0 Then
        MsgBox Err.Source & " --> " & Err.Description, , "Error"
    End If
End Sub
0
edit delete
are you sure?
asked Arne Clicteur

1 answer

I tought of a late binding problem and declared the constants in the sub header, but the error persists.

Const adOpenKeyset As Long = 1
Const adLockOptimistic As Long = 3
Const adCmdTable As Long = &H2
0
edit delete
are you sure?
answered Arne Clicteur

Post an answer but please log in first

Post Answer
  1. Please log in to post answer

Similar Questions

Tagsfor this question

Forumall questions