neadbecker
New Member
- Joined
- Jan 15, 2009
- Messages
- 43
I have an Excel 2010 pivot table update process that I just recently passed on to a colleague using Excel 2007. Most of the data connections were originally created older versions of Access using a DSN referencing a 2003 databse. I've created a new connection in Excel 2010 that references a table in that same database, but this conenction is using ACE (see connection string below). I worked with my colleague to make sure the database went into the a directory with the same path as I had so the pivot table would update for her. The older connections updated just fine, but the new connection would not update for her. The error was something like it could not find the ISM.
I created a connection from her Excel 2007 just to see if somehow it was different. It turned out to be the same connection string except for this last part: ;Jet OLEDB:Bypass UserInfo Validation=False. When she deleted this part from the connection string in my pivot table file, she was able save the change and get the table to update. I figured I would remove the string from my version in case I need to have someone else update it. However, when I remove the line and update the connection, the change doesn't save and that little string shows up again.
Is there a way that I can save this modified connection string so it will work for my colleauges in Excel 2007? I've made changes in other parts of the connection string, like the data source, and the changes saved just fine.
My connection string created in Excel 2010:
Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Documents\Data Warehouse Reports\Cost Reports 2003.mdb;Mode=Read;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";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;Jet OLEDB:Bypass UserInfo Validation=False
I created a connection from her Excel 2007 just to see if somehow it was different. It turned out to be the same connection string except for this last part: ;Jet OLEDB:Bypass UserInfo Validation=False. When she deleted this part from the connection string in my pivot table file, she was able save the change and get the table to update. I figured I would remove the string from my version in case I need to have someone else update it. However, when I remove the line and update the connection, the change doesn't save and that little string shows up again.
Is there a way that I can save this modified connection string so it will work for my colleauges in Excel 2007? I've made changes in other parts of the connection string, like the data source, and the changes saved just fine.
My connection string created in Excel 2010:
Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Documents\Data Warehouse Reports\Cost Reports 2003.mdb;Mode=Read;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";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;Jet OLEDB:Bypass UserInfo Validation=False
