PDA

View Full Version : Update Access Record from Excel



AndrewKent
11-19-2008, 07:56 AM
Hi there,

I am trying to update a record in an Access database from Excel, I have two macros running to do this...


Sub UpdateRecord()
' =========================================================================== ==================
' This macro will firstly connect to the Access database. It will then update every field in
' each table based on the criteria that has been set. In order to simplify the code structure,
' the coding to update each table has been placed in individual macros.
' =========================================================================== ==================
StartTimer ' This code is required for testing purposes only
Dim DBName, DBLocation, FilePath As String
Dim DBConnection As ADODB.Connection

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Set DBConnection = New ADODB.Connection
DBName = Worksheets("Calculation Matrix").Range("CalculationMatrix_DatabaseName").Value
DBLocation = Worksheets("Calculation Matrix").Range("CalculationMatrix_DatabaseLocation").Value
FilePath = DBLocation & DBName
With DBConnection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open FilePath
End With

Call UpdateIndex(DBConnection)
' Call UpdateCustomerData(DBConnection)
' Call UpdateCPIData(DBConnection)
DBConnection.Close
Set DBConnection = Nothing

Worksheets("Index").Activate
Range("A1").Select

EndTimer ' This code is required for testing purposes only
End Sub

and...


Sub UpdateIndex(DBConnection As ADODB.Connection)
' =========================================================================== ==================
' This macro is responsible for updating one table within the database. It is called by the
' CreateRecord macro as part of a routine.
' =========================================================================== ==================
Dim DBRecordset As ADODB.Recordset
Dim Query As String

Query = "SELECT * FROM tblIndex WHERE Record_ID =" & Worksheets("Calculation Matrix").Range("CalculationMatrix_Search").Value
Set DBRecordset = New ADODB.Recordset
DBRecordset.CursorLocation = adUseServer
DBRecordset.Open Source:=Query, ActiveConnection:=DBConnection, CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, Options:=adCmdText
With DBRecordset
.Fields("Record_ID") = Worksheets("Data Capture").Range("C5").Value
.Fields("Created_By") = Worksheets("Data Capture").Range("C6").Value
.Fields("Created_Date") = Worksheets("Data Capture").Range("C7").Value
.Fields("Modified_By") = Worksheets("Data Capture").Range("C8").Value
.Fields("Modified_Date") = Worksheets("Data Capture").Range("C9").Value
.Fields("Stakeholder_ID") = Worksheets("Data Capture").Range("C10").Value
End With
DBRecordset.Close
Set DBRecordset = Nothing
End Sub

...however I'm getting the message "Operation is not allowed in this context" on the DBRecordset.Close line.

Anyone know why?

Andy

CreganTur
11-19-2008, 08:06 AM
In which Sub is the error happening? UpdateRecord or UpdateIndex?

AndrewKent
11-19-2008, 08:07 AM
Weird, I just closed it, and re-opened and it now works fine??? Strange. Thanks anway.