Excel Pivot Table connected to an Access Database Data Source

[Alex Zimmerhaven]

Hi,

I have a client that is using an Excel spreadsheet to connect with a table
in an Access database. The db is 2003. We've tried an XLS file and an XLSX
file (both the same) but neither work. Essentially, somewhere along the line
the connection from the Excel file to the Access database got broken. Now
when we go through the dataconnections, to reconnect it, it won't take.
Everytime we refresh the pivot table, it clears out the pivot table. So,
it's not giving an error message that it can't find the Access table, it's
just clearing the pivot table, which makes we think that there's a field that
changed back on the table or something like that. But the fields are all the
same on the Access DB and the pivot tables. I realize that this question is
weird and hard to comment on. Anyways, any insight anyone has would be
great. Thanks again to all the MVP's!

-Alex

 

[Ed Ferrero]

Hi Alex,

Sounds strange. Try doing this - it will at least give us more information.

Data Ribbon -> Connections. Select connection, click Properties. Click the
Definition tab.

The connection string should look like this;

DBQ=C:\Documents and Settings\Ed\My Documents\Developent\Pivot
Tutor\Tutorial_Files\Wine 97.mdb;
DefaultDir=C:\Documents and Settings\Ed\My Documents\Developent\Pivot
Tutor\Tutorial_Files;
Driver={Microsoft Access Driver (*.mdb)};
DriverId=281;
FIL=MS Access;MaxBufferSize=2048;
MaxScanRows=8;
PageTimeout=5;
SafeTransactions=0;
Threads=3;
UserCommitSync=Yes;

Check that the DBQ points to the right database file (and path).

The command text shows the SQL statement. Something like;

SELECT *
FROM Sales

Easiest way to check that the SQL statement is correct is to copy it into an
access query in the original db.

Ed Ferrero
www.edferrero.com

Alex

 

[Alex Zimmerhaven]

Ed,

Thank you so much for your quick response! I really appreciate it. I will
give this a try. Hope you have a good rest of the week.

Thanks again!

-Alex