Inserting Recordeset into Access DB via VBE - Error operation must be an updatable...

[nikki333]

Hi Folks,

I'm trying to use Excel as front end (Input/Output) and Access as back end DB.
The idea is to have multiple Excel Input files, one DB file, and one Excel Output file that's supposed to be read-only.

So, I'm trying to insert new recordsets from the input files to access via VBA (multiple input files simultaneously). This is working as long as the output file is closed (and of course the DB). Otherwise, Excel throws the error - Operation must be an updateable query!
It seems that the output file is locking the DB. The linked table in the output file is set to update upon opening the file.

Any ideas to work around this issue?

'Code from input file
Sub subDatenBankEintrag()


    With Tabelle1


        Dim DBConn As ADODB.Connection
        Dim rst As ADODB.Recordset
        Dim cmd As New ADODB.Command
        Dim arrSpalten As Variant
        Dim rngLetzterEintrag As Range
        Dim arrLetzterEintrag As Variant
        Dim strSpalten As String
        Dim strSql As String
        Dim strLetzterEintrag As String
        Dim i As Long
        Dim numLetzteZeile As Long
        numLetzteZeile = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set rngLetzterEintrag = .Range(.Cells(numLetzteZeile, "A"), .Cells(numLetzteZeile, "K"))
        arrLetzterEintrag = rngLetzterEintrag
        Set DBConn = New ADODB.Connection
        DBConn.Mode = 16 + 3
'        DBConn.IsolationLevel =
        Set rst = New ADODB.Recordset
        arrSpalten = .Range("A1:K1")
        
        For i = 1 To UBound(arrSpalten, 2)
            If i = 1 Then
                strSpalten = arrSpalten(1, i)
            Else
                strSpalten = strSpalten & ", " & arrSpalten(1, i)
            End If
        Next i
        
        For i = 1 To UBound(arrLetzterEintrag, 2)
            If i = 1 Then
                If VarType(arrLetzterEintrag(1, i)) = vbString _
                Or VarType(arrLetzterEintrag(1, i)) = vbDate Then
                    strLetzterEintrag = "'" & arrLetzterEintrag(1, i) & "'"
                Else
                    strLetzterEintrag = arrLetzterEintrag(1, i)
                End If
            Else
                If VarType(arrLetzterEintrag(1, i)) = vbString _
                Or VarType(arrLetzterEintrag(1, i)) = vbDate Then
                    strLetzterEintrag = strLetzterEintrag & ", '" & arrLetzterEintrag(1, i) & "'"
                Else
                    strLetzterEintrag = strLetzterEintrag & ", " & arrLetzterEintrag(1, i)
                End If
            End If
        Next i
        
        With DBConn
            .CursorLocation = adUseServer
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .Properties("Data Source") = ThisWorkbook.Path & "\OEE.accdb"
            .Open
        End With
        
        strSql = "INSERT INTO OEE(" & strSpalten & ") VALUES(" & strLetzterEintrag & ")"
        Set rst = DBConn.Execute(strSql)    'Error here
        Set rst = Nothing
        DBConn.Close
        Set DBConn = Nothing
        
    End With


End Sub

[paulked]

Not sure, but have you tried without using the RecordSet? ie

    DBConn.Execute strSql