PDA

View Full Version : Inserting Recordeset into Access DB via VBE - Error operation must be an updatable...



nikki333
11-15-2019, 03:31 AM
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
11-15-2019, 09:01 AM
Not sure, but have you tried without using the RecordSet? ie


DBConn.Execute strSql