Solved

Appending a Excel sheet to an Access Database Via Visual Basic

Posted on 2002-07-13
3
151 Views
Last Modified: 2013-11-25
Hi all,

I am having a problem while trying to import an Excel sheet into an Access database Via Visual Basic.  I have tried this two ways, first by connecting a data control to the excel worksheet and stepping through each record in the dataset and utilizing ado to connect to the access database.  

Second I used the following code that I found on the net.  

Private Sub Command1_Click()
    On Error GoTo errHandler
    Dim mExcelFile As String
    Dim mAccessFile As String
    Dim mWorkSheet As String
    Dim mTableName As String
    Dim mDataBase As Database
    mExcelFile = App.Path & "\Book1.xls"
    mAccessFile = App.Path & "\Db1.mdb"
    mWorkSheet = "Sheet1"
    mTableName = "Table1"
      ' Below you may use "Excel 7.0" or 8.0 depending on your installable ISAM.
    Set mDataBase = OpenDatabase(mExcelFile, True, False, "Excel 5.0")
    mDataBase.Execute "Select * into [;database=" & mAccessFile & "]." & mTableName & _
        " FROM [" & mWorkSheet & "$]"
    MsgBox "Done.  Use Access to view " & mTableName
    Exit Sub
errHandler:
    If Err.Number = 3010 Then
         MsgBox mTableName & " already exist." & vbCrLf & _
             "Delete " & mTableName & " first or use another table name."
    Else
         MsgBox Err.Number & "  " & Err.Description
    End If
End Sub

Both of these kind of half work.  The problem is that one of the fields in my excel table is alpha-numeric and it seems to be excluding the cells in the column that are truly numeric.

EX:  It will pick up the cell 11B but not the cell with just 11 in it.  Weird huh?

Has anyone got a solution to this?
0
Comment
Question by:mitai
  • 2
3 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7151685
The answer is that Excel is not a true DBMS.  Take a look at this article from MSDN (yes, I realize you are using DAO, but the same applies):

HOWTO: Use ADO with Excel Data from Visual Basic or VBA (Q257819)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257819

And in particular this paragraph:
<quote>
Rows to Scan: Excel does not provide ADO with detailed schema information about the data it contains, as a relational database would. Therefore, the driver must scan through at least a few rows of the existing data in order to make an educated guess at the data type of each column. The default for "Rows to Scan" is eight (8) rows. You can specify an integer value from one (1) to sixteen (16) rows, or you can specify zero (0) to scan all existing rows. This is done by adding the optional MaxScanRows= setting to the connection string, or by changing the Rows to Scan setting in the DSN configuration dialog box.
</quote>

Also, please maintin this open question:
Drill down question for Crystal Date: 05/01/2002 07:38AM PST
http://www.experts-exchange.com/crystal/Q_20295856.html

Thanks,
Anthony
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 100 total points
ID: 7151686
Here are a couple of articles from MSDN, using DAO:
HOWTO: Extract Information from Excel Sheet with DAO (Q190195)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q190195
PRB: Excel Values Returned as NULL Using DAO OpenRecordset (Q194124)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q194124

Anthony
0
 

Author Comment

by:mitai
ID: 7152499
Thank you for such a quick response!
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

770 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