Solved

Using VBA in Access to create ADODB Connection to Excel

Posted on 2006-07-03
8
638 Views
Last Modified: 2013-12-25
While using VBA in Access 2003, I have created an ADODB connection to connect to a workbook in excel. The workbook may have several sheets in it, and each sheet seldom has above 250 rows. The ADODB connection is created individually for each sheet. I have a particular column("A") in the excel file that is supposed to be a text string. In some fields the string is just numbers, and Excel is noticing that and has the green triangle error message saying "Number Stored as text".

When I loop through the rows made in the ADODB connection some of the cells with the number stored as text are Null, and some values are correct. I have made some attempts to edit the spreadsheet so this error message isn't there, but it is only successful some of the time. I've read some other questions on here, but wasn't sure what would be best for my situation.  I have attached my connection script below.

What is the best way to make these values import correctly 100% of the time?
My end result of this connection is to run an INSERT query to add these spreadsheet rows into the Access DB.

Thanks for your help. I will happy to expound more if needed.

        'variable b is the name of the sheet

        Dim rs2 As New ADODB.Recordset
        Dim cnn2 As New ADODB.Connection
        Dim cmd2 As New ADODB.Command
       
        With cnn2
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=" & fileName & "; Extended Properties=Excel 8.0;"
        .Open
        End With
       
        Set cmd2.ActiveConnection = cnn2
        cmd2.CommandType = adCmdText
        cmd2.CommandText = "SELECT * FROM [" & b & "$]"
        rs2.CursorLocation = adUseClient
        rs2.CursorType = adOpenStatic
        rs2.LockType = adLockReadOnly
        rs2.Open cmd2
       
        Dim rs As ADODB.Recordset
        Set rs = New ADODB.Recordset

0
Comment
Question by:DeYoungJD
[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
  • 4
  • 4
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17032255

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q257/8/19.asp&NoWebContent=1

Considerations That Apply to Both OLE DB Providers
A Caution about Mixed Data Types

As stated previously, ADO must guess at the data type for each column in your Excel worksheet or range. (This is not affected by Excel cell formatting settings.) A serious problem can arise if you have numeric values mixed with text values in the same column. Both the Jet and the ODBC Provider return the data of the majority type, but return NULL (empty) values for the minority data type. If the two types are equally mixed in the column, the provider chooses numeric over text.

For example:
•    In your eight (8) scanned rows, if the column contains five (5) numeric values and three (3) text values, the provider returns five (5) numbers and three (3) null values.
•    In your eight (8) scanned rows, if the column contains three (3) numeric values and five (5) text values, the provider returns three (3) null values and five (5) text values.
•    In your eight (8) scanned rows, if the column contains four (4) numeric values and four (4) text values, the provider returns four (4) numbers and four (4) null values.
As a result, if your column contains mixed values, your only recourse is to store numeric values in that column as text, and to convert them back to numbers when needed in the client application by using the Visual Basic VAL function or an equivalent.

To work around this problem for read-only data, enable Import Mode by using the setting "IMEX=1" in the Extended Properties section of the connection string. This enforces the ImportMixedTypes=Text registry setting. However, note that updates may give unexpected results in this mode. For additional information about this setting, click the article number below to view the article in the Microsoft Knowledge Base:
194124 (http://support.microsoft.com/kb/194124/EN-US/) PRB: Excel Values Returned as NULL Using DAO OpenRecordset

http://support.microsoft.com/kb/194124/EN-US/
0
 

Author Comment

by:DeYoungJD
ID: 17032720
Thanks for the help. I read the article and it seemed to be discussing settings for a DAO connection. I could not find an equivalent for an ADO connection.

Once I find that... I am just establishing a connection for ADODB and then inserting each row into a table on my access database. I believe that qualifies as read only.

From what I read this will keep the values from turning null. I intend to insert the data into a column in an access table with a text datatype.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17032984
The IMEX=1 will also work in ADODB, despite the article's scope.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:DeYoungJD
ID: 17033157
Thanks. I tried to insert the imex setting below and get an Error: "Could not find an installable ISAM". Is there something wrong with my connection string.

        Dim rs2 As New ADODB.Recordset
        Dim cnn2 As New ADODB.Connection
        Dim cmd2 As New ADODB.Command
       
        With cnn2
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=" & fileName & "; Extended Properties=Excel 8.0; IMEX=1;"
        .Open
        End With
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 17034985
please try this:
        .ConnectionString = "Data Source=" & fileName & "; Extended Properties=""Excel 8.0; IMEX=1;"""
0
 

Author Comment

by:DeYoungJD
ID: 17037858
Well, No more error message, but the data still has the null problem.

What are the main guidelines for preparing the cell, and what is pointless?

-Make the cells text format?
-Click ignore errors in Excel?

Just want to make sure I am on the right track to use the IMEX=1 method.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17038045
>Well, No more error message, but the data still has the null problem.
-Make the cells text format?
yes, making the cells text format might help, but I did not need to do that.
0
 

Author Comment

by:DeYoungJD
ID: 17038663
It worked! I even restored the original spreadsheet and it worked perfectly by just editing my connection string. Thanks!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…

752 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