Excel: Runtime error 13 type mismatch

Question

I got Run-time error '13' at then end of this code, even if I try to switch between the commented lines of code

Public Property Get cnnString() As String
'old: strcnnString = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & Me.DBFolderName & ";Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database 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 OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False"
'strcnnString = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & Me.DBFolderName & ";Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database 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 OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False"
'new code before edits: strcnnString = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" & folder_name & ";Mode=Share Deny None;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;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 OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False "
strcnnString = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & Me.DBFolderName & ";Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database 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 OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False"
cnnString = strcnnString
End Property

here is the rest of the code

Private strcnnString As String
Private folder_name As String

Public Property Get DBFolderName() As String
 DBFolderName = folder_name
End Property

Public Property Let DBFolderName(ByVal newvalue As String)
folder_name = newvalue
End Property

'calling the property which rais the error
  With ActiveSheet.QueryTables.Add(Connection:=Array(Me.cnnString), Destination:=Range("A1"))

Any solution please?

Answer 1

QueryTables.Add can accept a couple of things, but I am not sure an Array is one of them, can you try just passing it the connection string without placing it within an array.

Answer 2

I would suspect your connection string, here is a good place to look: http://www.connectionstrings.com/excel-2007

Or try:

strcnnString = "Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & Me.DBFolderName & ";"

EDIT re Comment

I am not quite sure I get you, but perhaps you mean:

strcnnString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" &  Me.DBFolderName & ";"

EDIT More notes

I cannot quite see why you are using Array(cnnString), how about:

 ActiveSheet.QueryTables.Add Connection:=cnnString, Destination:=Range("A1")