473,320 Members | 1,838 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

VB.NET : Excel To Access through ADOX

3
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 :

Expand|Select|Wrap|Line Numbers
  1.     Sub AttachXLStoDB(ByVal ExcelPath As String, ByVal sheetName As String)
  2.  
  3.         Dim ADOXTable As New ADOX.Table
  4.         Dim ADOXCatalog As New ADOX.Catalog
  5.         Dim ADOConnection As New ADODB.Connection
  6.  
  7.         'Delete de first and last square bracket character in worksheet name (eg. [Sheet1$] -> Sheet1$
  8.         If (sheetName.EndsWith("]")) Then
  9.             sheetName = sheetName.Remove(sheetName.Length - 1, 1)
  10.             sheetName = sheetName.Remove(0, 1)
  11.         End If
  12.  
  13.  
  14.         Try
  15.             'link an access table to the excel country menu file
  16.             ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
  17.             "Data Source=C:\Documents and Settings\Administrator\My Documents\Projets\foo.mdb;" & _
  18.             "Jet OLEDB:Engine Type=4;")
  19.  
  20.             ADOXCatalog.ActiveConnection = ADOConnection
  21.  
  22.             ADOXTable.ParentCatalog = ADOXCatalog
  23.             ADOXTable.Name = sheetName
  24.             ADOXTable.Properties("Jet OLEDB:Remote Table Name").Value = sheetName
  25.             ADOXTable.Properties("Jet OLEDB:Link Provider String").Value = "Excel 8.0;DATABASE=" & ExcelPath & ";HDR=YES"
  26.  
  27.             ADOXTable.Properties("Jet OLEDB:Create Link").Value = True
  28.             ADOXCatalog.Tables.Append(ADOXTable)
  29.  
  30.  
  31.         Catch ex As Exception
  32.             MessageBox.Show(ex.Message)
  33.         Finally
  34.             ADOConnection.Close()
  35.         End Try
  36.  
  37.     End Sub
  38.  
I need to delete those after the applications stops, so I can't use a ADOXCatalog.tables.Delete(xxxx)...

Thanks for your help ;)
Apr 3 '07 #1
2 2617
mazelx
3
no idea???????????
Apr 6 '07 #2
kenobewan
4,871 Expert 4TB
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.
Apr 6 '07 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: JBAdamsJr | last post by:
I want to be able to create an Excel file with a VB.NET program on a server that does not have Microsoft Excel loaded on it. I am using the Jet OLE DB to read other data files. Can this be used to...
4
by: Paxton | last post by:
I can connect to an Excel worksheet and get records with no problem, if I know the worksheet's name. However, when my users upload their excel files, I don't know what the active sheet they want...
41
by: Ruby Tuesday | last post by:
Hi, I was wondering if expert can give me some lite to convert my word table into access database. Note: within each cell of my word table(s), some has multi-line data in it. In addition, there...
12
by: Art | last post by:
Hi everyone I was hoping someone might be able to help me with this. I'm just starting to try to work with MS Access tables through VB.net. In Access I can take an existing table and add a new...
6
by: syvman | last post by:
Hi everyone... I am pulling my hair out trying to do this, and was wondering if someone could give me some assistance... I have an Excel spreadsheet containing several worksheets. I'd like to be...
5
by: magix | last post by:
Hi, If I have following code to read the excel file, how do I validate the worksheets name ? In code below, if the filename doesn't contains "SomeName" worksheet, I will need to prompt error. ...
0
by: DrewYK | last post by:
I have the project that may never end in front of me. I am creating a routine that will take SpreadSheets from Excel and bring them into Access. I am not using any "DoCmd"s because the goal...
4
by: laurasesma18 | last post by:
I've got a convertion tool with Access 2003. It imports an excel sheet into the data base and with the records in it creats different outputfiles. Its programmed with VBA. Now what I need is, in...
2
by: KC-Mass | last post by:
Is it possible to read the spreadsheet names in an Excel workbook with VBA and no Excel?
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.