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 :
Expand|Select|Wrap|Line Numbers
- 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
Thanks for your help ;)