An access violation has more to do with user permissions rather than a
problem with the provider, but in this case it may be due to the way
you construct your connection string and/or initiate connection to the
db.

I use a version-aware function to set up my connection string according
to which provider is appropriate for the running instance of Excel. My
connection string and connection constructs are slightly different than
yours, though!

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

This precludes that I have to construct 2 separate connection strings.
I 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;"


You could configure your code something like this:

<aircode
'Use a var to hold data source
sDataSource = "<FullPathAndFilename" '//edit to suit
If appXL.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

I can't speak to XL2010 because I don't have it installed yet. Apps
using this are run on client machines and nobody's reported a problem
with it not working in XL2010.

<FWIW
I have apps that were built before XL2007 that still work in v12 using
the JET provider because (apparently) support for that is included with
ACE (or so I've been told)!

--
Garry

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