ADODB – An Experience
I’m not quite sure whether this is an age thing or whether it’s an affliction one is born with but I always have reservations about the latest technology that the likes of Microsoft are pushing out as this years ‘greatest thing since sliced bread’ and the new technology that the latest crop of graduates think is better than anything us more experienced people would rather use.
The main reason why I’m being cynical about ADODB is twofold. Firstly the whole question of the ‘Majority Type’ when handling mixed number and text is still a problem. Apart from the reservations expressed by OneDayWhen below, non IT people designing spreadsheets still imagine that ‘Account numbers’ and ‘Project numbers’ are numbers and not text, which does present a problem when they are in Excel and need to be imported into another package. An additional problem is the way some people handle dates in Excel. I was on a project in 2003 where data was passed around in Excel workbooks and then imported into Access for administration and tracking. I ended up writing copious code to handle the date fields that were an inherent part of the data. I couldn’t believe the number of different formats people could use for dates. Sometimes, it was a date field, sometimes it was unformatted numeric, sometimes it was text and when it was every different date format you could dream of was in use.
I’ve reproduced below several methods that I have used successfully but like all these methods I think it wise not to get too attached to one method. Whilst the ADODB mostly works for bringing data from Excel to Access I wouldn’t advocate it as a method for pulling/pushing data from one Excel Workbook to another. The problem here is that Excel has a knack of generating a ‘ghost’ of itself which may corrupt either or both of the workbooks involved in the push/pull process.
Importing Excel data into Access (cribbed from OneDayWhen via
Dick Kusleika)
If you have both strings and numerics in a single data column you can get unexpected results when importing the data into Access.
The registry key (for Jet 4.0) that controls this is:
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/
The ImportMixedTypes registry key is always read (whether it is honoured is discussed later). You can test this by changing the key to ImportMixedTypes=OneDayWhen and trying to the ISAM: you get the error, ‘Invalid setting in Excel key of the Engines section of the Windows Registry.’ The only valid values are:
ImportMixedTypes=Text
ImportMixedTypes=Majority Type
Data type is determined column by column. ‘Majority Type’ means a certain number of rows in each column are scanned and the data types are counted. Both a cell’s value and format are used to determine data type. The majority data type (i.e. the one with the most rows) decides the overall data type for the entire column.
There’s a bias in favour of numeric in the event of a tie. Rows from any minority data types found that can’t be cast as the majority data type will be returned with a null value.
For ImportMixedTypes=Text, the data type for the whole column will be
Jet(MS Access UI):’Text’ data type
DDL:VARCHAR(255)
Note that this is distinct from:
Jet(MS Access UI):’Memo’ data type
DDL:N/A
ImportMixedTypes=Text will curtail text at 255 characters as ‘Memo’ is cast as ‘Text’. For a column to be recognised as ‘Memo’, majority type must be detected, meaning the majority of rows detected must contain 256 or more characters.
But how many rows are scanned for each column before it is decided that mixed and/or what the majority type is? There is a second registry key, TypeGuessRows. This can be a value from 0-16 (decimal). A value from1 to 16 inclusive is the number of rows to scan. A value of zero means all rows will be scanned.
There is one final twist. A setting of IMEX = 1 in the container string’s extended property determines whether the ImportMixedType value is honoured. IMEX refers to Import Export mode. There are three possible values. IMEX=0 and IMEX=2 result in ImportMixedTypes being ignored and the default value of ‘MajorityType’is used. IMEX=1 is the only way to ensure ImportMixedTypes=Text is honoured. The resulting connection string might look like this:
Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\db.xls
Extended Properties=’Excel 8.0;HDR=Yes;IMEX=1’
Finally although it is mentioned in MSDN articles that MAXSCANROWS can be used in the extended properties of the connection string to override the TypeGuessRows registry keys, this seems to be a fallacy. Using MAXSCANROWS=0 in this way does nothing under any circumstances and doesn’t give an error. Similarly ImportMixedType cannot be used in the connection string to override the registry setting.
In summary, use TypeGuessRows to get Jet to detect whether a ‘mixed types’ situation exists or use it to ‘trick’ Jet into detecting a certain data type being the majority type. In the event of a ‘mixed types’ situation being detected, use ImportMixedTypes to tell Jet to either use the majority type or coerce all values as ‘Text’ (max 255 characters).
The following code has worked and represents one method that you can use. Changing the connection string to use the Jet.OLEDB.4.0 provider does not appear to make any discernable difference.
Dim adoConn as ADODB.Connection
Dim strConn as String
Dim strPath as String
Dim strDBN as String
Dim strSQL as String
strConn = “Provider=MSDASQL;Driver={Microsoft Excel Driver (*.xls)};HDR=YES;IMEX=1;DBQ=”
strPath = “\\servername\path\
strDBN=CurrentDb.Name
strSQL = “INSERT INTO access_tablename (access_fieldname1,…) IN ‘” & strDBN & “’ ”
strSQL = strSQL & “SELECT excel_range_fieldname1…. FROM excel_RangeName”
Set adoConn = New ADODB.Connection
adoConn.Open strConn & strPath & strDBN
adoConn.Execute strSQL
adoConn.Close
Set adoConn = Nothing
Exporting Excel data from one Workbook to multiple Workbooks
Having mastered the art of using ADODB it struck me that it was more than reasonable to use the technique to split a table of data in Excel into a number of workbooks based on a value in a particular column and then to combine the amended workbooks using a similar process.
Ron de Bruin suggests using Filters to achieve this and I agree with him entirely. If you try and use ADODB you will can end up with a ghost Excel process that runs for a good 3 minutes after you’ve closed you main Excel Application and then corrupts your master workbook so you can’t do the same again without recreating it manually.