Solved

Appending a Excel sheet to an Access Database Via Visual Basic

Posted on 2002-07-13
3
142 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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you for such a quick response!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now