I cannot get Excel VBA to connect to an Access Database

[TimJC]

Hello All.

I have a 1 table (at the moment) Access 2007 database with 150 records. When I try to import the records to Excel 2007 using the "From Access" option on the Data tab I have no problem at all.

However when I try to do the same thing from within VBA my sub breaks down with the message "Authentication Failed". I have been knocking my head up against a brick wall trying so solve this but no luck so far.

I am hoping that someone here can help me.

The relevant part of my code is as follows.

In a standard module I have declared the following:-

Public gcnConnection As ADODB.Connection

In a different module my code begins as follows:-

Sub createCollectionFromAccess()
Dim lstrConnection As String

lstrConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Password="";User ID = Admin;Data Source=C:\Users\Tim\Documents\databases\FFL.accdb;" _
& "Mode=Share Deny Write;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDBatabase Password="";" _
& "Jet OLEDB:Engine Type=6;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;" _
& "Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;" _
& "Jet OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;" _
& "Jet OLEDB:Support Complex Data=False"

Set gcnConnection = New ADODB.Connection
gcnConnection.ConnectionString = lstrConnection
gcnConnection.Open

It's when I get to the last line of code shown above that I receive the run time error message "Authentication failed".

The connection string is identical to the one that Excel creates when I import data via the Data tab in Excel itself.

Help! (and thank you for any that you can give)

 

[TimJC]

After about a day and a half of frustration with this I finally managed to get it to work by stripping everything out of the connection string except the provider and source details.

Now on to the mysteries (to me) of recordsets.

 

[David R]

I think your computer is laughing at you...

(you can disable smilies in the post editor, or wrap your strings in

 tags)[/I]

 

[TimJC]

I think you are right David! I have no idea how they got there. I presume I must have clicked a smiley somewhere by mistake,

 

[David R]

The code for that smiley is ""... your connection string included things like "OLEDBatabase Locking Mode"