locked
Error Import data in to accdb file RRS feed

  • Question

  • User-1771609044 posted

    hi i am trying to import file from excel file to access database but i error

    Unrecognized database format 'C:\Documents and Settings\Administrator\Desktop\WebSite3\App_Data\tracker.accdb'.

    please find the code below suggest me solution asap !!!! thanks



            If FileUpload1.HasFile Then
                Dim fileExt As String
                fileExt = System.IO.Path.GetExtension(FileUpload1.FileName)
                'validating the file before being uploaded
                If (fileExt = ".xls") Then

                    FileUpload1.SaveAs("C:\Documents and Settings\Administrator\Desktop\WebSite3\uploads\" & FileUpload1.FileName)
                    Try
                        FileUpload1.SaveAs("C:\Documents and Settings\Administrator\Desktop\WebSite3\uploads\" & FileUpload1.FileName)
                        Response.Write("File name: " & FileUpload1.PostedFile.FileName & "<br>" & FileUpload1.PostedFile.ContentLength & " kb<br>" & "File Size: " & " <br> File Uploaded Successfully :)")
                        Dim filename As String
                        filename = FileUpload1.FileName.ToString()
                        My.Computer.FileSystem.RenameFile("C:\Documents and Settings\Administrator\Desktop\WebSite3\uploads\" & filename, "Staff.xls")
                        Dim Access As String = "C:\Documents and Settings\Administrator\Desktop\WebSite3\\App_Data\tracker.accdb"
                        Dim Excel As String = "C:\Documents and Settings\Administrator\Desktop\WebSite3\uploads\Staff.xls"
                        Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Excel & ";Extended Properties=Excel 8.0;"
                        Using conn As New OleDbConnection(connect)
                            Using cmd As New OleDbCommand()
                                cmd.Connection = conn
                                cmd.CommandText = "INSERT INTO [MS Access;Database=" & Access & "].[Tickets_tracker] SELECT * FROM [Sheet1$]"
                                conn.Open()
                                cmd.ExecuteNonQuery()
                                Response.Write(cmd.ExecuteNonQuery().ToString())
                            End Using
                            conn.Close()
                        End Using
                    Catch ex As Exception
                        Response.Write("ERROR: " + ex.Message.ToString() + ":(")
                    End Try
                Else
                    Response.Write("Only excel files  are allowed! You have not specified a file!!!  :(")

                End If

            End If

    Wednesday, April 11, 2012 3:08 PM

Answers

  • User-821857111 posted

    Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Excel & ";Extended Properties=Excel 8.0;"

    You need to use the ACE provider to connect to an accdb file. If you can convert tracker.accdb to an mdb file, you can use the JET provider instead.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 12, 2012 1:08 AM

All replies

  • User-821857111 posted

    Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Excel & ";Extended Properties=Excel 8.0;"

    You need to use the ACE provider to connect to an accdb file. If you can convert tracker.accdb to an mdb file, you can use the JET provider instead.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 12, 2012 1:08 AM
  • User-1771609044 posted

    Hi Mikesdotnetting,

    Thanks for reply

    i have converted the file to mdb but i get an error

    trackerconnectionstring already exists thats in the webconfig file

    i had one more question if i change the provider string will affect the connection or will i get i error message stating the file is already in use by another source...

    Thursday, April 12, 2012 5:13 PM
  • User3866881 posted

    Unrecognized database format 'C:\Documents and Settings\Administrator\Desktop\WebSite3\App_Data\tracker.accdb'.

    Hello:)

    I think because your accdb file cannot be recognized by the system driver of database……Maybe that's the problem……

    So in my mind,I think you can:

    1)Check whether the file is "valid"(meaning not destroyed……)。

    2)Check your driver of db matches the type of that db。

    Just directly use accdb,don't do conversion to mdb and use ACE's driver of db,like what MVP said……

    Reguards……

    Thursday, April 12, 2012 9:30 PM
  • User-821857111 posted

    i have converted the file to mdb but i get an error

    trackerconnectionstring already exists thats in the webconfig file

    Remove or rename the existing trackerconnectionstring entry in the web.config, or provide a new name for the connection string you are adding for the mdb file. 

    i had one more question if i change the provider string will affect the connection or will i get i error message stating the file is already in use by another source...

    You get that if the correct permissions have not been set on the folder where your database file lives. Make sure that the NETWORK SERVICE account has MODIFY permissions on the folder.

    Friday, April 13, 2012 1:50 AM
  • User-1771609044 posted

    i am sure the db is not corrupt ... but i dont understand one think that in my whole project i have been using the jet connection string and it works but why only in append the data in access file i am getting this error 

    Friday, April 13, 2012 3:18 AM
  • User-1199946673 posted

    but i dont understand one think that in my whole project i have been using the jet connection string and it works but why only in append the data in access file i am getting this error 

    Because you need Modify Permissions to append (,edit or delete) data

    http://www.mikesdotnetting.com/Article/74/Solving-the-Operation-Must-Use-An-Updateable-Query-error

    Friday, April 13, 2012 4:25 AM