Solved

Using VBA in Access to create ADODB Connection to Excel

Posted on 2006-07-03
8
596 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 142

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 142

Expert Comment

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

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 142

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 142

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

747 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

14 Experts available now in Live!

Get 1:1 Help Now