Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Appending a Excel sheet to an Access Database Via Visual Basic

Posted on 2002-07-13
3
Medium Priority
?
181 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
[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
  • 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 400 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Suggested Courses

618 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