I am trying to convert an app from 2003 VB to 2010

Question

I opened the older app in VS 2010 and made changes based on the recommendations in the error statements that popped up. However one part that is still not working is shown below.

The error that comes back is

"file is already opened exclusively by another user or you need permission to view it"

I am opening up an access database and " select" and put it in an excel worksheet. The name of the worksheet changes every time the app is used. This worked in 2003 but not in 2010. I have goggled this and none of the answers have worked.

  Dim AccessConn8 As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\UpdateExportFile\ExportFile.mdb")

    AccessConn8.Open()

    Dim AccessCommand8 As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Excel " & _
"5.0;DATABASE=c:\" & strfilename & ".xls;HDR=YES;].[sheet1] from ExcelExport", AccessConn8) ' 

    Try
        AccessCommand8.ExecuteNonQuery()
    Catch exe As DataException
    Catch exc As System.Exception
        MsgBox("EXCEL not updated. Contact your System Administrator. " & strfilename)
        MsgBox(" ---->  " & exc.Message)

        AccessConn8.Close() ' added sat 2/23/15

        Exit Sub
    End Try

    AccessConn8.Close()


    Dim obook As Microsoft.Office.Interop.Excel.Workbook
    Dim oexcel As Microsoft.Office.Interop.Excel.Application
    oexcel = CType(CreateObject("Microsoft.Office.Interop.Excel.Application"), Microsoft.Office.Interop.Excel.Application)

    obook = oexcel.Workbooks.Open("c:\" & strFileName & ".xls")
    Try
        With oexcel
            .Visible = False
            .Range("C1").Value = "'Store #"
            .Range("D1").Value = "'Vendor #"
        End With
    Catch ex As Exception
        MsgBox("error:" & ex.ToString, MsgBoxStyle.Critical, "ERROR")

    End Try

    ' added 

    Dim myrange As Excel.Range
    myrange = oexcel.Range("a1:l90")
    myrange.Sort(Key1:=myrange.Range("c1"), Order1:=Excel.XlSortOrder.xlAscending, Header:=Microsoft.Office.Interop.Excel.XlYesNoGuess.xlYes, Orientation:=Excel.XlSortOrientation.xlSortColumns)

    obook.Save()
    obook.Close()
    oexcel.Quit()

Can you help me downed the right path to an answer?

In which line did you get the exception? – Steve Feb 22 '15 at 23:06 — Steve, Feb 22 '15 at 23:06

score 0 · Answer 1 · answered Feb 22 '15 at 23:16

0

I cannot be sure but I suspect that this behavior is caused by connection pooling that keeps the connection (and perhaps the file) open also after you have tried to close it.

I suggest two changes to your code above, all around the connectionstring and the way in which you open close the connection

Using AccessConn8 As New OleDbConnection("...;OLE DB Services = -2;")
   AccessConn8.Open
   ......
   ' Code as above....
   ......
End Using

The OLE DB Services = -2 disables the automatic use of Connection Pooling for this connection, while the Using Statement ensures that the connection is closed and DISPOSED after you have finished to use it

answered Feb 22 '15 at 23:16

Steve

213,761
22
232
286

Steve, I replied using Ask a Question rather than comment method. – JohnM Feb 23 '15 at 18:46
The section I got the error message was on the AccessCommand8 . – JohnM Feb 23 '15 at 18:47
Did you try to add the `OLE DB Services = -2` to your connectionstring? Does it make any difference? – Steve Feb 23 '15 at 18:52
It appear at the AccessConn8.open statement – JohnM Feb 24 '15 at 01:17
This could happen if your application TargetCPU is AnyCPU without Prefer 32bit set and you run on a 64bit system. [More info here](http://stackoverflow.com/questions/17716207/the-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine-w/17716238#17716238) (That was for ACE OLEDB 12.0 but the reverse is also true for JET 4.0) – Steve Feb 24 '15 at 08:09
Yes I did go into the configuration and changed it to x86 before this. In the code " OLE DB Services = -2" should it be exactly like this with spaces between Ole DB? Maybe that's the problem? – JohnM Feb 24 '15 at 12:24
Hi Steve I added the Using and End using statements. The Can t find installable ISAM still occurs. I added the Ole Db Services=-2 also. – JohnM Feb 25 '15 at 01:05
I think the file already open error is fixed. The installable ISAM error is still there. Any suggestions where to look is appreciated. Thank you – JohnM Feb 25 '15 at 01:07
This is my code now: Using AccessConn8 As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\UpdateExportFile\ExportFile.mdb;") ' added oledb services=-2;") AccessConn8.Open() Dim AccessCommand8 As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Excel " & _ "12.0;DATABASE=C:\" & strfilename & ".xlsx;HDR=YES;].[sheet1] from ExcelExport", AccessConn8) ' – JohnM Feb 25 '15 at 01:42
Sorry... above is my code now... The error I get is Installable ISAM not found. it occurs on the statement that "select *" from the access database to fill in the Excel worksheet. John – JohnM Feb 25 '15 at 01:45

I am trying to convert an app from 2003 VB to 2010

1 Answers1