Exclusive use- ? coding issue?

  • Thread starter Thread starter DanRoy
  • Start date Start date
D

DanRoy

I have an Access database which will be populated with data read from an
excel spreadsheet. The following code defines the excel file, opens, reads
and saves a record to the table properly. However, if i run the module and
then try to change any of the code, I get an error message that "I do not
have exclusive use" of the file and that changes will not be saved. In
access, I checked the File/Options/Advanced tab and selected " Exclusive" for
Default open mode and "Edited record" for Default record locking.
This is a single user database so sharing is not a problem. I suspect I
have a coding problem such that i am not closing an object properly.
Can anyone help in identifying the issue?

Sub OpenExcel()

Dim xlApp As Object
Dim XlBook As Object
Dim Xlc As Object
Dim Filename As String
Dim db As Database
Dim rs As Recordset
Dim lngColumn As Long




Set db = OpenDatabase("G:\Proposals\ECP0419 Program Affordability\BOEs\BOE
Scorecard\BOE Database\RAM-BOE Log.mdb ")

'Attempt to bind to an open instance
On Error Resume Next

Set xlApp = GetObject(, "Excel.Application")

Filename = "G:\Proposals\ECP0419 Program Affordability\BOEs\BOE
Scorecard\BOE Database\ECP419-1CPSPass1c as of 7-30-08.xls"


If Err.Number <> 0 Then
Debug.Print Err.Number
'Could not get instance, so create a new one
Err.Clear
On Error GoTo ErrHandler

Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.Workbooks.Open Filename
.Worksheets("CPSPass").Activate
Set Xlc = xlApp.Range("A2")
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Dash-1", dbOpenDynaset, dbAppendOnly)
With rst
.AddNew
.Fields("Intrx ID") = Xlc.Offset(1, lngColumn + 0).Value
.Update
End With


.Workbooks(1).Close savechanges:=False
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End With
End If





ErrHandler:
'Release the object and resume normal error handling
Set XlBook = Nothing
Set xlApp = Nothing
Set Xlc = Nothing
On Error GoTo 0
End Sub
 
Maybe it's because you open this database but you never close it:

G:\Proposals\ECP0419 Program Affordability\BOEs\BOE Scorecard\BOE
Database\RAM-BOE Log.mdb

Why do you open it anyway? You don't seem to use it anywhere.
 
Also, you are leaving an instance of Excel running. You will not see it in
the applications tab of task manager, but you will in the processes tab.
You need to be sure you quit the excel application before you exit:

Sub OpenExcel()

Dim xlApp As Object
Dim XlBook As Object
Dim Xlc As Object
Dim Filename As String
Dim db As Database
Dim dbs As Database
Dim rs As Recordset
Dim lngColumn As Long
Dim lbnExcelWasNotRunning As Boolean

On Error Resume Next ' Defer error trapping.
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
End If
Err.Clear ' Clear Err object in case error occurred.

'Set Error Trapping back on
On Error GoTo ErrHandler

Filename = "G:\Proposals\ECP0419 Program Affordability\BOEs\BOE
Scorecard\BOE Database\ECP419-1CPSPass1c as of 7-30-08.xls"

Set db = OpenDatabase("G:\Proposals\ECP0419 Program
Affordability\BOEs\BOE
Scorecard\BOE Database\RAM-BOE Log.mdb ")

With xlApp
.Visible = True
.Workbooks.Open Filename
.Worksheets("CPSPass").Activate
Set Xlc = .Range("A2")
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Dash-1", dbOpenDynaset, dbAppendOnly)
With rst
.AddNew
.Fields("Intrx ID") = Xlc.Offset(1, lngColumn + 0).Value
.Update
End With
.Workbooks(1).Close savechanges:=False
End With

OpenExcelExit:
db.Close
rst.Close
Set rst = Nothing
Set dbs = Nothing
Set db = Nothingh
If blnExcelWasNotRunning Then
xlAppl.Quit
End If
Set XlBook = Nothing
Set xlApp = Nothing
Set Xlc = Nothing

Exit Sub

ErrHandler:
'Release the object and resume normal error handling
On Error GoTo 0
MsgBox Err.Number & " " & Err.Description
Goto OpenExcelExit
End Sub
 
However, if i run the module and
then try to change any of the code, I get an error message that "I do
have exclusive use" of the file and that changes will not be saved. In
Click to expand...

Don't do that. Close your database, then re-open it to make any code
changes. After a while it just became second nature to do this.

You probably have one of the older versions of Access with a bug in it
which caused this problem: if not, you have a newer version which still
has this bug.

(david)
 
Back
Top