on 12/21/2011, michael beckinsale supposed :
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/Da...rogramming.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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc