Solved

Importing an Excel 11 spreadsheet using OLEDB command in VB.Net into an Access 2003 table

Posted on 2009-05-11
14
342 Views
Last Modified: 2012-08-13
Hey Experts!!

As the question states:
I am trying to import an Excel spreadsheet into an Access table using an OLEDB command in VB.net.  

I cannot find anything on the internet that will help.  Basically I want to do the import like I would through the File>Import in Access, except I want to do it in VB.net.  

Thanks in advance,
0
Comment
Question by:LZ1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +1
14 Comments
 
LVL 15

Expert Comment

by:mohan_sekar
ID: 24358349
0
 
LVL 30

Author Comment

by:LZ1
ID: 24358654
I have looked at that one already.  Unfortunately that won't help me.  
Anything else anyone????  
0
 
LVL 15

Expert Comment

by:mohan_sekar
ID: 24358875
Why wouldn't that help you?
0
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 
LVL 30

Author Comment

by:LZ1
ID: 24364376
It is throwing an ISAM error
0
 
LVL 30

Author Comment

by:LZ1
ID: 24364732
Basically what's happening with this solution is when it runs, it's treating an alpha/numeric field in Excel as a "null" field in Access.
0
 
LVL 30

Author Comment

by:LZ1
ID: 24365031
Correction to my last post:
A SELECT statement against the Excel sheet that has a field that contains alpha AND numeric data, the numeric data is returned as null.  
This import method is not working for this reason.  I do apologize for mistake above.  I am basically a third party relaying info.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24396225
Here is a class that I use with Excel:


Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Text
 
Public Class ExcelDataTable
 
  Private m_adapter As OleDbDataAdapter
  Private m_connection As OleDbConnection
  Private m_connectionString As String = ""
 
  Private m_sheetName As String
  Private m_workBook As String
 
  Public Sub New(ByVal workBook As String, ByVal sheetName As String)
    m_workBook = workBook
    m_sheetName = sheetName
    m_connectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source={0};" & _
        "Extended Properties=""Excel 8.0;HDR={1};IMEX=1""", _
        workBook, "Yes")
  End Sub
 
  Protected Overrides Sub Finalize()
    MyBase.Finalize()
    If Not m_adapter Is Nothing Then
      m_adapter.Dispose()
    End If
  End Sub
 
  Public ReadOnly Property WorkBook() As String
    Get
      Return m_workBook
    End Get
  End Property
 
  Public ReadOnly Property SheetName() As String
    Get
      Return m_sheetName
    End Get
  End Property
 
  Public Function GetData() As DataTable
 
    Dim table As New DataTable(SheetName)
 
    Dim commandText As String = String.Format("Select * From [{0}]", SheetName)
 
    m_connection = New OleDbConnection(m_connectionString)
    m_adapter = New OleDbDataAdapter(commandText, m_connection)
 
    m_adapter.Fill(table)
 
    Return table
 
  End Function
 
  Public Sub Insert(ByVal ParamArray parameters As OleDbParameter())
    Dim sb As New StringBuilder
    Dim param As OleDbParameter
    sb.Append("INSERT INTO [" & SheetName & "] (")
 
    Dim names As New StringBuilder()
    For Each param In parameters
      names.Append(param.ParameterName & ",")
    Next param
    sb.Append(names.ToString().TrimEnd("?"c))
    sb.Append(") VALUES (")
 
    Dim values As New StringBuilder()
    For index As Integer = 1 To parameters.Length
      values.Append("?,")
    Next index
 
    sb.Append(values.ToString().TrimEnd("?"c))
    Using command As New OleDbCommand(sb.ToString(), m_connection)
      If m_connection.State = ConnectionState.Closed Then
        m_connection.Open()
      End If
      For Each param In parameters
        command.Parameters.Add(param)
      Next param
      command.ExecuteNonQuery()
    End Using
  End Sub
 
  Public Sub Update(ByVal ParamArray parameters As OleDbParameter())
    Dim sb As New StringBuilder
    sb.Append("UPDATE [" & SheetName & "] SET ")
    For Each param As OleDbParameter In parameters
      sb.Append(param.ParameterName & " = " & param.Value.ToString() & ",")
    Next param
    Using command As New OleDbCommand(sb.ToString(), m_connection)
      If m_connection.State = ConnectionState.Closed Then
        m_connection.Open()
      End If
      command.ExecuteNonQuery()
    End Using
  End Sub
 
End Class

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24396352
0
 
LVL 30

Accepted Solution

by:
LZ1 earned 0 total points
ID: 24412839
This is actually what ended up working:
I am personally not sure if this applies to any of the above answers as I am doing this for my programmer.  So if this is somehow relevant to any of the above posts, I will gladly award points.  I am however not versed at all in programming.  

Function ImportXLSFile(TableName As String, FilePath As String, FileName as string) As Boolean 
  
        If File.Exists(FilePath & "db.mdb") Then File.Delete(FilePath & "db.mdb") 
  
        'Open Access database 
        Dim 2Access as Access.Application = new Access.Application() 
        2Access.NewCurrentDatabase(FilePath & "db.mdb") 
        2Access.DoCmd.TransferSpreadsheet(Access.AcDataTransferType.acImport,Access.AcSpreadSheetType.acSpreadsheetTypeExcel9,TableName,FilePath & FileName,True,) 
        2Access.CloseCurrentDatabase() 
        2Access.Quit(Access.AcQuitOption.acQuitSaveAll) 
  
        Return True 
 End Function 

Open in new window

0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24414106
You will need Access installed to run that code, and the end result is totally different than what you asked for (since you asked for OLEDB), but if you are happy, don't worry about me.
0
 
LVL 30

Author Comment

by:LZ1
ID: 24414230
@ TheLearnedOne:
I may have misdirected/interpreted my own question.  I was doing this after receiving the information from our resident programmer.  The question asked was originally for OLEDB because that's what he requested.  I just don't want to award points just to close the question in case anyone else ever has the issue.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24414279
If the solution you have posted works for you and you have no problem with it(it requires ACCESS installed on the system), then we have no problem with you closing the question.
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question