Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Pivot Table connected to an Access Database Data Source
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Pivot Table connected to an Access Database Data Source
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 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Pivot Table connected to an Access Database Data Source
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 "Ed Ferrero" wrote: 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating pivot table from Access database | Excel Discussion (Misc queries) | |||
Open excel pivot source data in Access? | Excel Discussion (Misc queries) | |||
I NEED TO KNOW THE SOURCE OF A PIVOT TABLE FROM ACCESS in excel | Excel Discussion (Misc queries) | |||
Access Create Excel Pivot Table Data Source | Excel Discussion (Misc queries) | |||
Pivot Table against an Access Database | Excel Discussion (Misc queries) |