Solved

Using VBA in Access to create ADODB Connection to Excel

Posted on 2006-07-03
8
625 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
  • 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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 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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

839 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