Microsoft.Jet.OLEDB.4.0 not working in 2007

[PivotMeThis]

The following code was included in a file (named Master) that was supplied to me by another MrExcel user. I have used it for several years and it has always worked perfectly. We have moved to Office 2007 but the reports I have been using have continued to come to me as .xls files. Today I received an .xlsx file and it would not work. I can save the file as an .xls and it will work but I would like to know if there is something I can do so that this will work with the newer format. There are other worksheets inlcuded in the file (ThisWeeksDataFile) and (LastWeeksDataFile). When I try to run the query I get this message:

Data file: ‘C:\Work\Excel Widgets\LastWeeksDate.xls’ not found.

Would you like to connect to W:\Highway\Construction\Reports\Project Status\Rhondas1 Weekly Report\LastWeeksData.xls instead?

It doesn't matter which answer I select, it opens up the VBA editor with this: ".Refresh BackgroundQuery:=False" highlighted.

Here is the code:

Sub UpdateQueries()
    Application.ScreenUpdating = False
    Dim txtThisWeeksDataFile As String, txtLastWeeksDataFile As String
    txtThisWeeksDataFile = Range("DataFilePath").Value & Range("ThisWeeksDataFile").Value
    txtLastWeeksDataFile = Range("DataFilePath").Value & Range("LastWeeksDataFile").Value
    Worksheets("Last Week").Select
    Application.Goto Reference:="LastWeeksData"
    With Selection.QueryTable
        .Connection = Array( _
        "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" & txtLastWeeksDataFile & ";Mode=Share Deny Write;Extende" _
        , _
        "d Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Lock" _
        , _
        "ing 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 Wi" _
        , "thout Replica Repair=False;Jet OLEDB:SFP=False")
        .CommandType = xlCmdTable
        .CommandText = Array("Sheet1$")
        .Refresh BackgroundQuery:=False
    End With
    Range("A2").Select
    Worksheets("This Week").Select
    Columns("A:R").Hidden = False
    Application.Goto Reference:="ThisWeeksData"
    With Selection.QueryTable
        .Connection = Array( _
        "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" & txtThisWeeksDataFile & ";Mode=Share Deny Write;Extende" _
        , _
        "d Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Lock" _
        , _
        "ing 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 Wi" _
        , "thout Replica Repair=False;Jet OLEDB:SFP=False")
        .CommandType = xlCmdTable
        .CommandText = Array("Sheet1$")
        .Refresh BackgroundQuery:=False
    End With
    Columns("A:R").Hidden = True
    Range("S2").Select
    Application.ScreenUpdating = True
End Sub

Thanks for any advise you might be able to offer.
Rhonda

 

[PivotMeThis]

I have figured out this part:

Data file: ‘C:\Work\Excel Widgets\LastWeeksDate.xls’ not found.

Would you like to connect to W:\Highway\Construction\Reports\Project Status\Rhondas1 Weekly Report\LastWeeksData.xls instead?

That doesn't normally happen but that was the original file name which was changed and it should go to the location on the w drive. So I do need to select yes, but I haven't figured out how to change the code because I don't understand the jet database at all.

 

[PivotMeThis]

I have learned that jet 4.0 does not work in 2007 and ace 12.0 must be used.

Does anyone know how to convert this or does it need to be rebuilt. The macro was recorded by someone else and I do not know how to record a macro that installs an OLEDB.

 

[Norie]

Have you downloaded the AccessDatabaseEngine file from Microsoft?

If you have then all you should need to do is run it and ACE 12.0 should be installed.

Then you can record your own macro using that driver and you should get the required code.

 

[PivotMeThis]

No, I haven't downloaded the driver, I thought it came with the program. I will look for it and install it. I do not know how to record a macro using that driver. Is there something special I need to do?

 

[Norie]

If you install that driver it should be the one used when you record a macro, as far as I'm aware there's nothing 'special' you need to do.

 

[PivotMeThis]

Thanks Norie, I really feel like a retard with this. Someone else recorded this macro back in 2003 for me and I've been using it every since. I'm going to try and pick it apart and record a new macro.

My only other option is to save the new files as .xls and run them through what I have. Hopefully I will get this figured out but at least I have that to fall back on.

I really appreciate your help - in reading about the AccessDataBaseEngine I did not think it was what I needed because I really don't understand how it (or Jet 4.0 work).

 

[xenou]

If you installed Access 2007 with your office upgrade you should have both ACE and JET available (as needed).

I believe that:
ACE will work with 2003 files (its backward compatible). JET will not work with 2007 files. But JET would work with files created in Office 2007 that are saved in 2003 file formats ... if that's not too confusing

 

[xenou]

Also, you could try re-recording the query as you add the table to a workbook (even a test workbook). That should give you a start at what's different for connecting. Possibly you can just cut and paste in the new connection information.

 

[PivotMeThis]

I have tried that (or thought I did) I absolutely do not know what I am doing or how this works. I have tried to find instructions on this and can't find any. Someone else created this several years ago and I asked at that time how it was done and did not learn anything. I am completely lost.