EXCEL 2007 VBA getting error using Microsoft.ACE.OLEDB.12.0

[DougP]

Error is on the .open line in yellow
run time error 3701: Provider cannot be found. It may not be properly installed.
I suspect I need a reference? tried several but No Go.

    FileName = Application.ActiveWorkbook.Fullname

    ' open ADODB connection
     With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0; " & _
        .ConnectionString = "Data Source=" & FileName & ";" & _
        "Extended Properties=\Excel 12.0;HDR=YES;"
        .CursorLocation = adUseClient
        [highlight #FCE94F].Open[/highlight]   
    End With

TIA

DougP

 

[PHV]

I'd get rid of the 1rst & _

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DougP]

Ok now I get Error: Cound not Find Installable ISAM

     With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0;"
        .ConnectionString = "Data Source=" & FileName & ";" & _
        "Extended Properties=\Excel 12.0;HDR=YES;"
        .CursorLocation = adUseClient
        .Open
    End With

DougP

 

[DougP]

If I use this, it opens the connection just fine, but then barks at the rs.open?
the orginal SQL string works on Excel 2010 but not on 2007. So I think there is still something wrong with the connection string.
Which is why I found to switch.
here is the connection string after it opens??? Do we need all that just to open?

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\z533560\Documents\Forte Reporting\Forte APP Loading Factor to VAST List Compare 6-26-13-2.xlsx;Mode=Share Deny None;Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDBatabase Password="";Jet OLEDB:Engine Type=35;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;Jet OLEDB:Bypass UserInfo Validation=False;

        connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & FileName & _
                    "';Extended Properties='Excel 12.0;HDR=NO;IMEX=1';"
        cn.Open connString
        SQLUpdateString = "UPDATE [Application Loading Combined$] SET [Parent Entity] = Left([Parent Entity],Len([Parent Entity])-2) WHERE ((Right([Parent Entity],2)='-V') OR ((Right([Parent Entity],2)='-G'))  OR ((Right([Parent Entity],2)='-B'))"
      [highlight #FCE94F]rs.Open SQLUpdateString, cn[/highlight]

DougP

 

[Andrzejek]

barks at the rs.open" that's too technical for me

Did you try:
[tt]
[red]
cn.Execute SQLUpdateString[/red]
[/tt]

Have fun.

---- Andy

 

[DougP]

Error: no value given for one or more parameters.
If I pass a simple "Select * from [Application Loading Combined$]"
I don't get an error but anything else more complicated gives error above.
this code all worked when using the lower connection string in 2010 but the Jet connection will not run in Excel 2007.

       With cn
        connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & FileName & _
                    "';Extended Properties='Excel 12.0;HDR=NO;IMEX=1';"
        cn.Open connString
    End With
     
                SQLUpdateString = "UPDATE [Application Loading Combined$] SET [Parent Entity] " & _
                    " = Left([Parent Entity],Len([Parent Entity])-2) WHERE ((Right([Parent Entity],2)='-V'))" & _
                    "OR ((Right([Parent Entity],2)='-G'))  OR ((Right([Parent Entity],2)='-B'))"
                    
        ' open connection to the Excel workbook and get data from String above
        'rs.Open SQLUpdateString, cn
        cn.Execute SQLUpdateString

this works great in Excel 2010 but not 2007.

    With cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=" & FileName & ";" & _
        "Extended Properties=Excel 8.0;"
        .CursorLocation = adUseClient
        .Open
    End With
[code]

DougP

 

[DougP]

Another person who uses the same JET connection says she does not get an error in Excel 2007. So there is something else going wrong different on his machine.
Eveyone is a thousand miles apart.

DougP

 

[DougP]

OMG the guy renamed some of the column headings from when I got the details. so no wonder it would not work!!!

DougP

 

[SkipVought]

Hand the bum by this thumb. ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}