R
RogerManiccia@ebs
In Excel 2007, I opened an Excel 2003 workbook. The workbook has N number of
worksheets. Each worksheet has a query table. The source of the each query
table is a table in the same MS Access database. The external data sources
in the worksheets were created in Excel 2003 doing the following:
1. From the main menu selecting Data, Import External Data, Import Data
2. Select “Connect to New Data Source.odc†from My Data Sources
3. Select “ODBC DSNâ€
4. MS Access Database
5. Select the Access database and the table (each worksheet has a different
table selected).
When I refresh the data sources in Excel 2007, all the worksheets refresh,
but two. On two I get the following error message: “Data could not be
retrieved from the database. Check the database server or contact your
database administrator. Make sure the external database is available, and
then try the operation again.â€
When I investigate the error, I can find no difference between the data
sources that refresh, and the two data sources that do not receive the error.
Further, I create the external data source in Excel 2007 doing the following
steps:
1. Select “From Other Sources†on the Data Ribbon
2. Select “From Data Connection Wizardâ€
3. Select “ODBC DSNâ€
4. Select “MS Access Databaseâ€
5. Select the same Access Database and table as before.
The data refreshes fine. There are absolutely no problems working with the
workbook. I am still in Excel 2007.
I save the new workbook in the Excel 2003 format. I now open the new
workbook in Excel 2003. I do not have a problem refreshing the external data
sources, even the two external data sources created using Excel 2007.
However when I select Edit query (from the menu with the right mouse click),
Excel 2003 invokes the MS Query interface, and I receive an error “The
Microsoft Jet database engine cannot find the input table or query
‘mdb.ER_Cust_With_BillClass_Wrong’. Make sure that it still exists and that
its name is spelled correctly.†The other queries invoke the Edit OLE DB
Query dialog. Why the difference?
My question(s) is why will not the two external data sources refresh in
Excel 2007 while the other data sources that appear to be the same do? The
second question is what has changed in Excel 2007 external data sources that
Excel 2003 would read them differently? What ever assistance can be given
would be appreciated.
--
Roger Maniccia
emdeon
26 Century Blvd
Nashville, Tenn
615-886-9031
worksheets. Each worksheet has a query table. The source of the each query
table is a table in the same MS Access database. The external data sources
in the worksheets were created in Excel 2003 doing the following:
1. From the main menu selecting Data, Import External Data, Import Data
2. Select “Connect to New Data Source.odc†from My Data Sources
3. Select “ODBC DSNâ€
4. MS Access Database
5. Select the Access database and the table (each worksheet has a different
table selected).
When I refresh the data sources in Excel 2007, all the worksheets refresh,
but two. On two I get the following error message: “Data could not be
retrieved from the database. Check the database server or contact your
database administrator. Make sure the external database is available, and
then try the operation again.â€
When I investigate the error, I can find no difference between the data
sources that refresh, and the two data sources that do not receive the error.
Further, I create the external data source in Excel 2007 doing the following
steps:
1. Select “From Other Sources†on the Data Ribbon
2. Select “From Data Connection Wizardâ€
3. Select “ODBC DSNâ€
4. Select “MS Access Databaseâ€
5. Select the same Access Database and table as before.
The data refreshes fine. There are absolutely no problems working with the
workbook. I am still in Excel 2007.
I save the new workbook in the Excel 2003 format. I now open the new
workbook in Excel 2003. I do not have a problem refreshing the external data
sources, even the two external data sources created using Excel 2007.
However when I select Edit query (from the menu with the right mouse click),
Excel 2003 invokes the MS Query interface, and I receive an error “The
Microsoft Jet database engine cannot find the input table or query
‘mdb.ER_Cust_With_BillClass_Wrong’. Make sure that it still exists and that
its name is spelled correctly.†The other queries invoke the Edit OLE DB
Query dialog. Why the difference?
My question(s) is why will not the two external data sources refresh in
Excel 2007 while the other data sources that appear to be the same do? The
second question is what has changed in Excel 2007 external data sources that
Excel 2003 would read them differently? What ever assistance can be given
would be appreciated.
--
Roger Maniccia
emdeon
26 Century Blvd
Nashville, Tenn
615-886-9031