Locked file problem 2

[judgeh59]

I have written (also somewhat leveraged) this code to pull info from an excel 2007 file. I step through the code and it works fine. I create an executable and it works fine. But if I open another instance of the executable, either on my computer or somebody elses, I get this error.

run-time error '-2xxxxxxxxxxxxxxxx'
ODBC excel driver] could not use '(unknown)'; file already in use.

Public rst As ADODB.Recordset
Public cnn As ADODB.Connection

Private Sub cmdQuit_Click()

Set rst = Nothing
cnn.Close
Set cnn = Nothing
Unload Me

End Sub

Private Sub cmdRS_Click()

Dim sConn As String, sSQL As String
Dim sPath As String, sDB As String

If Len(Me.txtAssyNum) <> 0 Then
AssyNum = frmMain.txtAssyNum
Else
MsgBox "Please enter an Assembly Number"
frmMain.Hide
Exit Sub
End If

sPath = "\\namp-dsk-002\Eng\Common Area\Ernest H"
sDB = "BH RI defects 04_11_12.xlsx"

Set cnn = New ADODB.Connection

sConn = "Provider=MSDASQL.1;"
sConn = sConn & "Persist Security Info=False;"
sConn = sConn & "Extended Properties=""DSN=Excel Files;"
sConn = sConn & "ReadOnly=True;"
sConn = sConn & "DBQ=" & sPath & "\" & sDB & ";"
sConn = sConn & "DefaultDir=" & sPath & ";"
sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"""

cnn.Open sConn

Set rst = New ADODB.Recordset

sSQL = "SELECT TOP 15 QN, Defect, count(defect) as Num_of_Defects"
sSQL = sSQL & " From [QN Raw Data$] "
sSQL = sSQL & " WHERE board = " & Chr(39) & AssyNum & Chr(39)
sSQL = sSQL & " group by qn, defect"
sSQL = sSQL & " order by count(defect) desc, qn"

rst.Open sSQL, cnn, adOpenStatic

Set MyGrid.DataSource = rst

MyGrid.Columns(0).Width = 2000
MyGrid.Columns(1).Width = 2000
MyGrid.Columns(2).Width = 2000

MyGrid.ScrollBars = dbgAutomatic

End Sub

any help would be greatly appreciated..

Ernest

Be Alert, America needs more lerts

 

[Bluejay07]

Unless you have it specified elsewhere (code not included), you do not have anything associated with "Public cnn As ADODB.Connection". When clicking cmdQuit, you are closing a connection that hasn't been opened.

Also, does your code open the spreadsheet (or is the spreadsheet already opened)? If so, you will have to make sure the file is closed and nothing is referencing that spreadsheet before opening another instance of it. Sometimes VB code will lock the spreadsheet preventing any other usage of it.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Bluejay07]

Sorry, I didn't look close enough. I see cnn is used in cmdRS_click.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[judgeh59]

there is a

set cnn.open sconn

my code doesn't open the actual excel file it just connect to it. I see what you are saying about the open file...my issue is, and maybe there is a cleaner way of doing it, I connect, set the recordset, run the SQL, load the recordset and then use it in the Grid. If I close the recordset the data is gone and I get nothing in the recordset...does that make sense.

Ernest

Be Alert, America needs more lerts

 

[Andrzejek]

I would try:

    rst.Open sSQL, cnn, adOpenStatic
        
    Set MyGrid.DataSource = rst
[blue]
    rst.Close
    Set rst = Nothing
    cnn.Close
    Set cnn = nothing
[/blue]

Have fun.

---- Andy

 

[judgeh59]

Andy - I did try that technique earlier and as soon as you close the recordset the data goes away....when I do the Set my.grid.datasource = rst, the grid is loaded and looks just like I want, but, again, as soon as you close the rst the data goes away, it's like I need the datagrid to be persistent...

Ernest

Be Alert, America needs more lerts

 

[gmmastros]

I think your problem may be with your connection string. Apparently, you are using the MSDASQL.1 provider. Instead, I would suggest that you use the ACE provider. Take a look at this article that describes connection strings for Excel.

http://www.connectionstrings.com/excel-2007

The article doesn't mention the ReadOnly attribute, but I would suggest you add that to the extended properties section anyway.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[judgeh59]

George - I attempted the connection string for .xlsx and ACE but I get some errors...I commented out the Readonly string. If I don't comment out the Extended properties string I get and invalid argument error.

sConn = "Provider=Microsoft.ACE.OLEDB.12.0;"
'sConn = sConn & "Persist Security Info=False;"
'sConn = sConn & "Extended Properties=""Excel 12.0 Xml;"
'sConn = sConn & "ReadOnly=True;"
sConn = sConn & "DBQ=" & sPath & "\" & sDB & ";"
sConn = sConn & "DefaultDir=" & sPath & ";"
sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"""

with this connection string I get an ISAM error....I did notice that the link you put in might be for .NET stuff and I don't know subtle difference between the two...Also, I beleive there is a different DriverId for 2007. Does anybody know what that is?

Thanks
Ernest

Be Alert, America needs more lerts

 

[Bluejay07]

According to SkipVought's post in the VBA thread, the 2007 driver should be DriverId=1046

If at first you don't succeed, then sky diving wasn't meant for you!

 

[gmmastros]

Try this:

    sConn = "Provider=Microsoft.ACE.OLEDB.12.0;"
    sConn = sConn & "Data Source=" & sPath & "\" & sDB & ";"
    sConn = sConn & "Extended Properties="Excel 12.0 Xml;HDR=YES;ReadOnly=True";

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[judgeh59]

Bluejay = thanks for that number - I was pretty sure it was from Skip but I couldn't find it.

George - I got that connection string to work - a few quotes here and there....But when I walk through the code nothing shows up in the datagrid....but in the locals windows I look at the rst object and there is data in there....so that is weird...also I created the exe with your connection string and tried to run 2 seperate instances and I get the same error...well, it looks different because it's coming from ACE vs MSDASQL, but basically it says the file is locked...

Ernest


Be Alert, America needs more lerts

 

[gmmastros]

try changing this:

[tt]rst.Open sSQL, cnn, adOpenStatic[/tt]

to

[tt]rst.Open sSQL, cnn, adOpenStatic[!], adLockReadOnly[/!][/tt]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[judgeh59]

George - another good idea but "no Joy"...in looking at the locals window in the rst object, even though that object was not set, that is default....I really appreciate all the ideas, keep it going...what gets me is probably not the 1st person to do this...it should be this hard...thanks again...

Ernest

Be Alert, America needs more lerts

 

[strongm]

You just need to detach the recordset

set rst.ActiveConnection = Nothing

And then you can close the connection

cnn.close

So Andrzejek's solution becomes

[blue]cnn.Cursorlocation=adUseClient[/blue]
rst.Open sSQL, cnn, adOpenStatic
Set MyGrid.DataSource = rst
[blue]set rst.ActiveConnection = Nothing
cnn.Close
Set cnn = Nothing[/blue]

 

[judgeh59]

strongm - that's the ticket. works great - thank you very much and have a star on me....

George - even though you didn't have the solution that worked for me I really appreciate you out there trying to find something - so you have a star on me also...

thanks TT

Ernest

Be Alert, America needs more lerts

 

[dilettante]

Read-Only Status (Excel Driver) describes why this occurs.

 

[judgeh59]

dilettante - I guess I knew that to a degree and real problem was keeping the data in the recordset persistant...maybe that should have been the OP subject...thanks for the link though...


Ernest

Be Alert, America needs more lerts