Excel ADO connection opens read-only instance of opened Excel

  • Thread starter Thread starter Serge L
  • Start date Start date
S

Serge L

ok here is my code from example on microsoft website
( http://support.microsoft.com/default.aspx?scid=kb;EN-
US;Q321686 )

With gxlCN
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & xlPath & ";" & _
"Extended Properties=""Excel 8.0; HDR=Yes;"" "
.Open
End With

PROBLEM SCENARIO:
2 separate Excel applications are open
1 create connection in one Excel instance -
Connection's creates fine, but the second instance of
Excel now has read-only-instance of first excel app. If I
finish running the code and close both Excel apps, Task
Manager shows instance of Excel running in the memory.

I terminate my connection object like this
If gxlCN Is Nothing = False Then
If gxlCN.State = adStateOpen Then gxlCN.Close
Set gxlCN = Nothing
End If

Please help me open Excel ADO connection without this read-
only copy.

Thank you

Serge
 
Hi Serge,

Reading data from an Excel workbook via ADO should not create a new instance
of Excel. Is there anything else in your code that starts a new Excel
application? Maybe I misunderstood your post....
 
Jake, if you run this function in Excel while another Excel is open it will
create Read-Only instance. As you can see there is nothing here that starts
a new instance of Excel. I am using MDAC 2.8

Private Function Create_CN() As Boolean
Dim xlPath As String
On Error GoTo Err_Handler
Create_CN = False

'-- open XL connection
Set gxlCN = New ADODB.Connection
xlPath = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

'Stop
With gxlCN
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & xlPath & ";" & _
"Extended Properties=Excel 8.0;"
.CursorLocation = adUseClient
.Open
End With

Create_CN = True
Exit Function

Err_Handler:
Create_CN = False
MsgBox Err.Description, vbCritical, "Error while creating connection..."
'Call Close_CN
End Function
 
Hi Serge,
Jake, if you run this function in Excel while another Excel is open
it will create Read-Only instance. As you can see there is nothing
here that starts a new instance of Excel. I am using MDAC 2.8
Click to expand...

I guess I'm not following what the problem is. If you open a workbook in
one instance of Excel, then open the same workbook in another instance, the
second instance will get a read-only copy of it. That is, unless you are
using a shared workbook.

Is your problem that you want to be able to update the workbook with ADO and
you can't? If so, that's because you already have it open for writing, so
you can't make changes via ADO (or a second instance).

What are you trying to do? Maybe there's a better way....
 
Ok, I'm sorry for now being clear. I'll try again, thanks for your patience
:)

If you open any blank workbook in one Excel and then open workbook that
contains the ADO connection code in the second instance of Excel and run
that code
what happens in my case is the first instance (the one with blank workbook)
of Excel now contains 2 workbooks! One is blank (from the start) + a
Read-only copy of the workbook that contained the code (i.e. second
Excel).That is not supposed to happen.

How's that?

Thanks

Serge
 
Serge,

In the meantime, you should only execute this code on a closed workbook. It
seems to create various problems when run against an open workbook. So
either move your code to another workbook or use a standalone VB executable
to connect to your data source workbook.
 
Jake,

That's alright. The worksheet contains DDE links and I save values to SQL
database during the day. It is not a good option for me to make user close
live data workbook to save the values to DB. I will just use named ranges
for my purpose. I wanted to use ADO in Excel just because I could. My ADO
connection object to SQL DB does not create any problems in VBA. For the
future I will keep this problem in mind.

I would still be interested to find out what causes this and if there is a
fix.

Thanks for looking into this.

Serge
 
Back
Top