ODBC Connection to Excel 2007

Hello everyone,

Since I would like to use an Excel 2007 File (*.xlsx) as Data Source, I created an ODBC Connection. It worked fine so far: the connection is established, I get the data as expected in the "Query Designer tab" and I could insert the fields in my report. However, when I want to see the result on the "Preview tab", I get an error saying that the "Report Definition '/myReport' is not valid...".

I don't know what could be wrong in my Report... Am I missing something in the ODBC configuration?

My Connection String is as follows:
Dsn=pl_excel;dbq=C:\Public\PL_DataSource.xlsx;defaultdir=C:\Public;driverid=1046;fil=excel 12.0;maxbuffersize=2048;pagetimeout=5

I also tried to create an OLE DB connection but I got the same error...

Thanks for your help, I'm waiting for your suggestions...
Did anyone have the same issue?
|||

Hi

With OLEDB it works fine ...

Test That with OLEDB:

OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=<your DB>;Server=<your Server>;

Test taht with ODBC:

ODBC;DSN=96SalesData;UID=Rep21;PWD=NUyHwYQI;

that should work

ODBC Connection to Excel 2007

Hello everyone,

Since I would like to use an Excel 2007 File (*.xlsx) as Data Source, I created an ODBC Connection. It worked fine so far: the connection is established, I get the data as expected in the "Query Designer tab" and I could insert the fields in my report. However, when I want to see the result on the "Preview tab", I get an error saying that the "Report Definition '/myReport' is not valid...".

I don't know what could be wrong in my Report... Am I missing something in the ODBC configuration?

My Connection String is as follows:
Dsn=pl_excel;dbq=C:\Public\PL_DataSource.xlsx;defaultdir=C:\Public;driverid=1046;fil=excel 12.0;maxbuffersize=2048;pagetimeout=5

I also tried to create an OLE DB connection but I got the same error...

Thanks for your help, I'm waiting for your suggestions...
Did anyone have the same issue?
|||

Hi

With OLEDB it works fine ...

Test That with OLEDB:

OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=<your DB>;Server=<your Server>;

Test taht with ODBC:

ODBC;DSN=96SalesData;UID=Rep21;PWD=NUyHwYQI;

that should work

ODBC connection prompting for 'sa' password

We use Pivot Tables in Excel to provide users with reports. The Pivot Tables connect to SQL Server via an ODBC connection. In the ODBC connection, SQL Server authentication is specified and the 'sa' user name and password are entered. In prior versions
of Excel, the password would be saved in the Pivot Table. However, in Excel 2003 the user is prompted to enter the 'sa' password in order for the pivot table to update. Is there a way to have Excel 2003 pivot tables function as they did in prior versio
ns so that the password in the ODBC connection does not have to be re-entered?
Don't know much about Excel. But can you create another account (not sa)
to access the DB? This way you can keep your sa password secret.
Eric
Mike wrote:

> We use Pivot Tables in Excel to provide users with reports. The Pivot Tables connect to SQL Server via an ODBC connection. In the ODBC connection, SQL Server authentication is specified and the 'sa' user name and password are entered. In prior versio
ns of Excel, the password would be saved in the Pivot Table. However, in Excel 2003 the user is prompted to enter the 'sa' password in order for the pivot table to update. Is there a way to have Excel 2003 pivot tables function as they did in prior vers
ions so that the password in the ODBC connection does not have to be re-entered?
Eric Li
SQL DBA
MCDBA