thayduck
asked on
How do you change ODBC Microsoft Access Driver
The ODBC Microsoft Access Driver is pointing to abc.mdb.
I need to change it to point to abc.accdb.
How do you do this ?
Access 2007 on W7 PC.
I need to change it to point to abc.accdb.
How do you do this ?
Access 2007 on W7 PC.
ASKER
I have been there before, but when I click User DSN tab, then click MS Access Database, then click Configure, then click Select, I do not see anything pointing to abc.mdb. So I am not sure why I get message ODBC Microsoft Access Driver is pointing to abc.mdb.
I guess I want to see it so I know I am in right place to make change.
I guess I want to see it so I know I am in right place to make change.
Are you logged on as correct user?
What do you have in system, DSN?
What do you have in system, DSN?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Under System DSN tab I scroll thru many drivers
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry, had a IT emergency.
The issue is within XL document and a pivot table.
XLdoc.png shows XL document with connection string showing .accdb
XLdoc.png shows what happens when I try to refresh XL document.
Not sure why I get this ODBC error since I changed connection string to point to .accdb not .mdb
Looks like pivot table or XL still points to .mdb somewhere but not sure where.
XLDoc.PNG
XLDoc1.PNG
The issue is within XL document and a pivot table.
XLdoc.png shows XL document with connection string showing .accdb
XLdoc.png shows what happens when I try to refresh XL document.
Not sure why I get this ODBC error since I changed connection string to point to .accdb not .mdb
Looks like pivot table or XL still points to .mdb somewhere but not sure where.
XLDoc.PNG
XLDoc1.PNG
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Tried but still have same issue. It keeps wanting abc.mdb (see .png).
I have changed everything (that I know of) to point to abc.accdb but still it wants abc.mdb when I Refresh pivot table.
Very frustrating.
I have changed everything (that I know of) to point to abc.accdb but still it wants abc.mdb when I Refresh pivot table.
Very frustrating.
ASKER
Looks like the issue is with the .xlxs with the pivot tables.
Seems like the old path is stored in the .xlxs somewhere, so even though connection points to .accdb the old path .mdb is still being used. Something to do with the PivotCache which no matter what I try I cannot change. Tried below in VBA code but still does not work (found on internet) . Still looks for old .mdb path.
Any ideas on how I can change this Pivot Cache so it points to .accdb ?
Const sOLDPATH As String = "C:\Symposium Phone Reports\Symposium Phone Reports.mdb"
Const sNEWPATH As String = "C:\Symposium Phone Reports\Symposium Phone Reports.accdb"
xlApp.DisplayAlerts = False
Set wb = xlApp.Workbooks.Open("C:\S ymposium Phone Reports\Call Distribution - Customer.xlsx")
Set ws = wb.Worksheets("Data")
With ws.PivotTables(1).PivotCac he
.Connection = Replace(.Connection, sOLDPATH, sNEWPATH)
ws.Activate
Set pt = ws.PivotTables(1)
pt.RefreshTable **Fails here. Trys to refresh .mdb not .accdb
Set qt = ws.QueryTables(1)
qt.Refresh **Fails here. Trys to refresh .mdb not .accdb
End With
Set ws = wb.Worksheets(1)
ws.Activate
wb.Save
wb.Close
Seems like the old path is stored in the .xlxs somewhere, so even though connection points to .accdb the old path .mdb is still being used. Something to do with the PivotCache which no matter what I try I cannot change. Tried below in VBA code but still does not work (found on internet) . Still looks for old .mdb path.
Any ideas on how I can change this Pivot Cache so it points to .accdb ?
Const sOLDPATH As String = "C:\Symposium Phone Reports\Symposium Phone Reports.mdb"
Const sNEWPATH As String = "C:\Symposium Phone Reports\Symposium Phone Reports.accdb"
xlApp.DisplayAlerts = False
Set wb = xlApp.Workbooks.Open("C:\S
Set ws = wb.Worksheets("Data")
With ws.PivotTables(1).PivotCac
.Connection = Replace(.Connection, sOLDPATH, sNEWPATH)
ws.Activate
Set pt = ws.PivotTables(1)
pt.RefreshTable **Fails here. Trys to refresh .mdb not .accdb
Set qt = ws.QueryTables(1)
qt.Refresh **Fails here. Trys to refresh .mdb not .accdb
End With
Set ws = wb.Worksheets(1)
ws.Activate
wb.Save
wb.Close
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for help
Ahh ok, good man.
ASKER
Thanks for help jerseysam...
C:\Windows\SysWOW64
C:\Windows\System32
Then just make the changes in the settings