I am getting this error -2147467259 Unexpected Error from external database driver when running a vba macro code at this line-.Provider = "Microsoft.ACE.OLEDB.12.0"
in below code-
This is a simple vba macro (not using .net,sql server or ms access). Only excel is used. This works fine in following combinations- Excel 2007 & Windows Xp, Excel 2007 & Windows 7. But in Windows 7 and Excel 2010 it gives this error if the user is not admin. Interestingly, in Windows 7 and Excel 2010, if i login through admin user OR RUn excel.exe by clicking "Run as administrator" option (start-typoe excel-shift +right click-run as administrator)and then opening my excel file it does not give this error and more interestingly if after logging once through admin user and opening this excel file and closing it, I then log off and log in as normal user and open my excel file I still do not get this error.So the thing is that ONE TIME login as admin and opening this excel file temporariily fixes the issue.BUT the problem is that i need to distribute this application to various users and they will be logging it as normal users and i can not ask them to first login as admin user. Why this issue is ioccuring and what is the resolution for this? thanks.

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

It should be noted that the code sample in my previous reply is from a
VB6 project that automates Excel. To use the code in Excel VBA
projects, replace *appXL* with *Application*, OR use the *appXL*
variable as a fully qualified reference to the Excel application
running the code.

--
Garry

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