none
Could not find installable ISAM

    Question

  • Hi All,

    my code is below... 

    Dim OpenFileDialog As New OpenFileDialog
    
            Try
    
          
            'Get the file to import
            OpenFileDialog.Filter = "Excel Files (*.xls)|*.xls; *.xlsx"
            OpenFileDialog.Title = "Find the invoice to import"
            OpenFileDialog.ShowDialog()
            excelConnection = OpenFileDialog.FileName
    
    
            Dim ssqltable As String = "ResImport"
                Dim myexceldataquery As String = "select * from [sheet1$]"
    
    
                'connection strings 
                Dim sexcelconnectionstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelConnection + ";" + "Extended Properties= Excel 12.0; HDR=YES;IMEX=1;Persist Security Info=False;"
                Dim ssqlconnectionstring As String = "Data Source=CYHSQL03;Initial Catalog=Resident Involvement;Integrated Security=True"
                'Empty Table
                Dim sclearsql As String = Convert.ToString("delete from ") & ssqltable
            Dim sqlconn As New SqlConnection(ssqlconnectionstring)
            Dim sqlcmd As New SqlCommand(sclearsql, sqlconn)
            sqlconn.Open()
            sqlcmd.ExecuteNonQuery()
            sqlconn.Close()
                'Copy data  into table 
            Dim oledbconn As New OleDbConnection(sexcelconnectionstring)
            Dim oledbcmd As New OleDbCommand(myexceldataquery, oledbconn)
            oledbconn.Open()
            Dim dr As OleDbDataReader = oledbcmd.ExecuteReader()
            Dim bulkcopy As New SqlBulkCopy(ssqlconnectionstring)
            bulkcopy.DestinationTableName = ssqltable
            While dr.Read()
                bulkcopy.WriteToServer(dr)
            End While
            dr.Close()
            oledbconn.Close()
                Label1.Text = "File imported Successfully"
    
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try

    but it is coming up with an the error "could not find installable ISAM"

    so there is something wrong with this line....

    Dim sexcelconnectionstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelConnection + ";" + "Extended Properties= Excel 12.0; HDR=YES;IMEX=1;Persist Security Info=False;"

    But i can't see to find what, everything i have googled hasn't worked! any ideas?

    Thanks,

    Darren

    Tuesday, April 11, 2017 6:57 PM

Answers

  • Hi darrenjshirley,

    This error will also be generated when the syntax of the connection string is incorrect. Please refer to the example below.

    MyConnection = New OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='D:\Date.xlsx';Extended Properties=""Excel 12.0 Xml;HDR=YES"";")

    More detailed info about connectionstring, please refer to https://www.connectionstrings.com/excel/

    Best Regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, April 12, 2017 5:13 AM
    Moderator

All replies

  • Hello,

    Are you running in 64bit mode?

    Try these settings.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, April 11, 2017 8:51 PM
    Moderator
  • Hi darrenjshirley,

    This error will also be generated when the syntax of the connection string is incorrect. Please refer to the example below.

    MyConnection = New OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='D:\Date.xlsx';Extended Properties=""Excel 12.0 Xml;HDR=YES"";")

    More detailed info about connectionstring, please refer to https://www.connectionstrings.com/excel/

    Best Regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, April 12, 2017 5:13 AM
    Moderator