VB.NET : Excel To Access through ADOX

[mazelx]

Hi,

I'm a bit confused with all the types of connections (odbc, oledb, adox, etc...)

I'm making an application which imports the data from an excel file to a table into MS Access.

I decided to connect directly the excel file with a linked Access table (through adox), and make all my queries to the database. No need to store the data in a datatable or such!

The problem is that I don't know how to delete those tables after the user exits the application. Of course, I need to delete those tables to avoid my database growing every executions of the application...

here's the interesting part of this app :

    Sub AttachXLStoDB(ByVal ExcelPath As String, ByVal sheetName As String)
 
        Dim ADOXTable As New ADOX.Table
        Dim ADOXCatalog As New ADOX.Catalog
        Dim ADOConnection As New ADODB.Connection
 
        'Delete de first and last square bracket character in worksheet name (eg. [Sheet1$] -> Sheet1$
        If (sheetName.EndsWith("]")) Then
            sheetName = sheetName.Remove(sheetName.Length - 1, 1)
            sheetName = sheetName.Remove(0, 1)
        End If
 
 
        Try
            'link an access table to the excel country menu file
            ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\Documents and Settings\Administrator\My Documents\Projets\foo.mdb;" & _
            "Jet OLEDB:Engine Type=4;")
 
            ADOXCatalog.ActiveConnection = ADOConnection
 
            ADOXTable.ParentCatalog = ADOXCatalog
            ADOXTable.Name = sheetName
            ADOXTable.Properties("Jet OLEDB:Remote Table Name").Value = sheetName
            ADOXTable.Properties("Jet OLEDB:Link Provider String").Value = "Excel 8.0;DATABASE=" & ExcelPath & ";HDR=YES"
 
            ADOXTable.Properties("Jet OLEDB:Create Link").Value = True
            ADOXCatalog.Tables.Append(ADOXTable)
 
 
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            ADOConnection.Close()
        End Try
 
    End Sub
 

I need to delete those after the applications stops, so I can't use a ADOXCatalog.tab les.Delete(xxxx )...

Thanks for your help ;)

[mazelx]

no idea???????????

[kenobewan]

Welcome to the site. You may be able to use thread management. However, it may be easier to delete tables each time the application is started. HTH.