Updating Access DB from Excel

Question

I am trying to use excel as a front end to update an access database with user inputs. However, I can not get this query to function properly. Currently, I have gotten the error message;

Run-time error '-2147467259 (80004005)': Cannot Update. Database or object is read-only.

I know the file is not stored in a location that is write protected and the file itself is not either. Opening the file itself on any users computer will allow them to write to it, but the code is blocking itself somehow. This is Excel and Access 2007. Code below;

       Sub PopulateOneField()
 Const TARGET_DB = "P:\Master\Part Number List.accdb"
   Dim cnn As ADODB.Connection
   Dim MyConn
   Dim rst As ADODB.Recordset
   Dim i As Long, j As Long
   Dim Rw As Long
   Dim sSQL As String

   Sheets("Sheet2").Activate
   Rw = Range("A65536").End(xlUp).Row

   Set cnn = New ADODB.Connection
     MyConn = TARGET_DB
    MyConn = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" & MyConn

   With cnn
     .Open MyConn
   End With

   Set rst = New ADODB.Recordset
   rst.CursorLocation = adUseServer
   For i = 3 To Rw
     sSQL = "SELECT * FROM sheet2"
     rst.Open Source:=sSQL, _
              ActiveConnection:=cnn, _
              CursorType:=adOpenKeyset, _
              LockType:=adLockOptimistic
     rst(Cells(1, 3).Value) = Cells(i, 3).Value
     rst.Update
     rst.Close
   Next i

   cnn.Close
   Set rst = Nothing
   Set cnn = Nothing

End Sub

Thanks in advance.

Why even use Excel as frontend? I inherited a db doing that and quickly abandoned that approach. Access table is named Sheet2? I use: cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='P:\Master\Part Number List.accdb'". Missing rst.EditMode. Should probably open recordset outside the loop. — June7
– June7, Commented Mar 7, 2022 at 21:31
Why does recordset not have filter criteria? Most likely same record will always be edited. — June7
– June7, Commented Mar 7, 2022 at 21:37
I wouldn't necessarily need to use it, but this is the most familiar method for end users so I figured I would do it that way. To be fair, I am not entirely familiar with VBA in relation to Access either. What filter criteria should be included? The excel is just an identical copy of the database with a macro button to add initials and date and then push that to the DB, meaning it has the key field in a hidden column. — BearsRfuk
– BearsRfuk, Commented Mar 8, 2022 at 16:47
I recommend you look at using Access for frontend. I think users will adapt easily. I don't know what filter criteria you need - you tell me. Do you want to edit one record or many records? If many, then code needs to loop recordset as well as loop Excel cells. That would mean nesting the cell loop inside recordset loop. — June7
– June7, Commented Mar 8, 2022 at 18:19
With every click of the macro button it would be two fields of one record. — BearsRfuk
– BearsRfuk, Commented Mar 8, 2022 at 18:34

HackSlash · Accepted Answer · 2022-03-08 16:46:19Z

I wrote a simple class for this purpose. It looks like this:

Option Explicit

' ConnectModeEnum
'Private Const adModeRead = 1
'Private Const adModeReadWrite = 3
Private Const adModeShareDenyNone As Long = 16

' adStateEnum
'Const adStateClosed As Long = 0                  'Indicates that the object is closed.
Const adStateOpen As Long = 1                    'Indicates that the object is open.
'Const adStateConnecting As Long = 2              'Indicates that the object is connecting.
'Const adStateExecuting As Long = 4               'Indicates that the object is executing a command.
'Const adStateFetching As Long = 8                'Indicates that the rows of the object are being retrieved.

' CursorTypeEnum
Const adOpenStatic As Long = 3

' LockTypeEnum
Const adLockOptimistic As Long = 3

Private dataSource As Object

Public FileName As String

Public Property Get Connection() As Object
    If dataSource Is Nothing Then
        Set dataSource = CreateObject("ADODB.Connection")
        
        With dataSource
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .Mode = adModeShareDenyNone
        End With
    End If
    
    Set Connection = dataSource
End Property

Public Sub Connect(ByVal dataBaseName As String)
    Connection.Open "Data Source=" & dataBaseName & ";"
End Sub

''' Recordset command is used to access table data
Public Function Record(ByVal sqlQuery As String) As Object
    If Not ((Connection.state And adStateOpen) = adStateOpen) Then
        Connect FileName
    End If
    
    Set Record = CreateObject("ADODB.Recordset")
    Record.Open Source:=sqlQuery, ActiveConnection:=Connection, CursorType:=adOpenStatic, LockType:=adLockOptimistic
End Function

Public Sub Dispose()
    If dataSource Is Nothing Then
        Debug.Print "You disposed of nothing..."
    Else
        If (Connection.state And adStateOpen) = adStateOpen Then dataSource.Close
        Set dataSource = Nothing
    End If
End Sub

Once you have that in a class module you can use it like this:

Dim myDB As AccessBackEnd
Set myDB = New AccessBackEnd
myDB.FileName = TARGET_DB

With myDB.Record(sSQL)
    .Fields(Cells(1, 3).Value) = Cells(i, 3).Value
    .Update
End With

myDB.Dispose

That said, your logic doesn't make sense. You are setting the same field 3 times. It's always going to hold the final value. So why do it 3 times?

Thanks, I will try applying this and report back. Simple seems to be a relative term here as most everything in that went over my head. As you can tell by my attempt to update the 3rd & 4th fields. I was not trying to update it three times, but rather start at the 3rd field and go to the 4th.
I'm talking about the target field getting updated. It looks like the field name is getting pulled from Cells(1, 3).Value which does not change. So you are updating that same field multiple times. You need to select a record or use .MoveNext to move through the records in the recordset.
The idea was to update two fields of one record at one time. Every press of the macro button would refresh the data connection from the db to excel so as to get the most up to date data. It would then initialize and date the fields and finally push it to the database which is what you are seeing here.
I would do that all in one go, instead of in a loop. Just add a second .Fields(otherOne) line to update the second field before the .Update call

Collectives™ on Stack Overflow

Updating Access DB from Excel

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related