ADOBE Connection to open Excel workbook

[michael beckinsale]

Hi All,

Windows 7 64 Bit, Office 2007 32 Bit, All updates for Windows & Office
installed.

I have am building a reasonably complex workbook in which an sql query
is run on a sheet within the same workbook. I have used the connection
string below and l am led to believe that this is the latest version
which supports Excel 2007 & 2010 and removes the limitations of the
65536 rows / records.

ADODB.Connection.Open "driver={Microsoft Excel Driver (*.xls,
*.xlsx,*.xlsm,*.xlsb)};driverid=1046;dbq=MyExcelFile.xlsm;"

On my home computer where l developed the solution everything runs
perfectly. On my colleagues laptop which has EXACTLY the same software
installed (we have both checked the software version numbers several
times) Excel crashes when the recordset exceeds 65536 rows! It would
appear that the only difference in the 2 machines is the amount of ram
available. The machine working has 16gb the machine not working only
has 2gb. However l believe that Excel 32 bit is restricted to 2gb
anyway.

The data source files are xl2003 but to overcome the issue that Excel
interprets this data as xl2003 data the whole workbook in which the
qery exists is saved as a xl2010 file before executing the query.
Anyway this doesn't answer why it should work on 1 machine but not
another.

Anybody got any idea's, this has really got me stumped!

Regards

Michael

 

[GS]

Not sure this will help but...

I use the JET ADODB provider in XL11 and earlier versions of Excel, the
ACE ADODB provider in XL12 and later versions. I don't know what a
Query uses but I assume Excel 12 and later will substitute the provider
engine in the earlier file with its ACE provider. In any case, there
should not be a row limitation in the later versions, but I expect
issues to appear trying to open a later version file in an early
version of Excel where the rows limitation exists, AND the early
version has no idea what the ACE provider is. AFAIK, early version
queries should continue to work in the later versions BUT NOT the other
way around!

Regardless, if you have tables with that many records then you're using
the wrong format for storing the data. While doable in Excel workbooks
it's NOT practical since a proper database file is readily available in
the same MSO suite. Also, you don't have to physically open workbooks
being used as data tables using ADO. You can load a table (worksheet)
into a recordset and work with it same as if it was in a db file.

 

[michael beckinsale]

Hi Gary,

I apprecaite the response as there is very little about this on the
internet that is clearly explained!

I thought the ACE ADODB provider was meant to be for both XL2007 &
XL2010 (eg 11.0 & 14.0, 8.0 being XL2003)?

I also thought that the ACE provider was brought in to replace the JET
because of the row limitations in JET? So although JET will work quite
happily in XL2007 & XL2010 it will bomb out once it reaches 65536 rows
whereas the ACE provider should continue past this mark. Or have l got
it totally wrong?

The source data is provided by 60 or so files that are dumped from a
mainframe each month. These are in XL2003 format and need summarising
which takes them over the 65536 rows, that is why they are opened,
copied, pasted into XL2007, and hence why the workbook being queried
is open eg querying itself. It is the end users decision to use
XL2007.

l have banged on about efficiency, proper db applications etc, but
they are accountants and understand XL so that is what they want!

I am wondering whether it might be worth trying the querytable route?

Any ideas welcome

Regards

Michael

 

[GS]

Hi Gary,

I apprecaite the response as there is very little about this on the
internet that is clearly explained!

I thought the ACE ADODB provider was meant to be for both XL2007 &
XL2010 (eg 11.0 & 14.0, 8.0 being XL2003)?

I also thought that the ACE provider was brought in to replace the JET
because of the row limitations in JET? So although JET will work quite
happily in XL2007 & XL2010 it will bomb out once it reaches 65536 rows
whereas the ACE provider should continue past this mark. Or have l got
it totally wrong?

Basically, this is the understanding I have of the ACE provider. I use
a version-aware function to set up my connection string according to
which provider is appropriate for the running instance of Excel.
Otherwise, everything else is the same as far as building
queries/recordsets goes.

Example:
Construct a connection string something like this:
If Application.Version => 12 Then
'use ACE provider connection string
Else
'use JET provider connection string
End If

This precludes that you'll have to construct 2 separate connection
strings. You could use constants for this:

Const sProvider As String = "Microsoft.Jet.OLEDB.4.0;"
Const sExtProps As String = "Excel 8.0;"

Const sProvider12 As String = "Microsoft.ACE.OLEDB.12.0;"
Const sExtProps12 As String = "Excel 12.0 Xml;"

If you know the data source beforehand, you could configure your code
something like this:

<aircode>
' Use a var to hold data source
sDataSource = "<FullPathAndFilename>"
If Application.Version => 12 Then
'use ACE provider connection string
sConnect = "Provider=" & sProvider12 & _
"Data Source=" & sDataSource & _
"Extended Properties=" & sExtProps12
Else
'use JET provider connection string
sConnect = "Provider=" & sProvider & _
"Data Source=" & sDataSource & _
"Extended Properties=" & sExtProps
End If
</aircode>

' Construct your SQL statement
sSQL = "SELECT * FROM..."

' Grab the data into a recordset
Set rsData = New ADODB.Recordset
rsData.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly,
adCmdText

The source data is provided by 60 or so files that are dumped from a
mainframe each month. These are in XL2003 format and need summarising
which takes them over the 65536 rows, that is why they are opened,
copied, pasted into XL2007, and hence why the workbook being queried
is open eg querying itself. It is the end users decision to use
XL2007.

l have banged on about efficiency, proper db applications etc, but
they are accountants and understand XL so that is what they want!

I am wondering whether it might be worth trying the querytable route?

Any ideas welcome

Regards

Michael

Hi Michael,
The format I'm familiar with as a DB 'dump' is plain text or CSV. There
are several ways to handle the import into Excel. Doing this manually
in XL12+ (2007 or 2010) should be no issue regard row limitations. I
suspect when you say the mainframe is dumped into an early XLS that
this is an actual workbook file rather than a CSV text file. It would
make more sense to me to 'dump' the data into text/csv files, making
them available to any DB provider (not just ADODB).

I use the ADO methodology outlined by Rob Bovey and I'm happy to say
that it serves me well for my needs thus far. You can download working
examples here...

http://www.appspro.com/conference/DatabaseProgramming.zip

Another way to go (in the case of text/csv files) might be to use
normal VB I/O to 'dump' the file contents into an array and 'dump' that
into a spreadsheet. This is a memory-sensitive process and so you might
have to read the file in blocks. Managing the row count is trivial
since you can use a loop with a Step option to parse large files into
separate worksheets.

HTH