Database Query For Excel 2010

[LittleRock]

I am using Voiceguide 7 on a Windows 7 machine and setup a database query to link to a Windows XP machine with Excel 2010. I can place an Excel file on the W7 machine and query the file no problem, but when using db query to get the data on WXP machine it fails. Not sure if this is a permissions issue, virus protection issue or ado.net ODBC data provider doesn't work with WXP?

 

 

152859.414 6 2 1 db using ado.net ODBC data provider : OdbcConnection/OdbcCommand/etc

152859.417 6 2 1 db odbc RunQuery_Odbc [TR QUERY], db=[], connect=[Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=Source=Z:\\Wcdaesos\Report\tr\Reports\TR_VRU_DATA.xlsx;], sql=[select * from [sheet1$A1:H2]]

 

 

152859.558 22 2 1 ERROR v7.3.4612.19509 (2012-08-17 9:50:19.15) ConnectAndRun_Odbc init : ERROR [HY000] [Microsoft][ODBC Excel Driver] Failure creating file.

ERROR [iM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed

ERROR [HY000] [Microsoft][ODBC Excel Driver] Failure creating file.

[SupportTeam]

I can place an Excel file on the W7 machine and query the file no problem

 

So sounds like the actual drivers and query is working,

 

but when using db query to get the data on WXP machine it fails. Not sure if this is a permissions issue, virus protection issue or ado.net ODBC data provider doesn't work with WXP?

 

and the issue is only when you try to run this when Excel file in on another machine.

 

 

From the posted trace it looks like you have mapped drive Z to a shared folder on another machine.

 

Trace shows that the ODBC connection string specified this as:

 

Source=Z:\\Wcdaesos\Report\tr\Reports\TR_VRU_DATA.xlsx

 

Have you tried to change "Z:\\" to "Z:\" ?

 

If that does not work then the next place to look would be the permissions set on the created share.

If you have anti-virus software running on the remote machine then it could be affecting remote access as well.

[LittleRock]

I tried using "Z:\" and that didn't work and also checked the permissions and turned off the virus software and still get the same result.

[SupportTeam]

Have you tried setting up an ODBC DSN to the Excel file on the remote machine?

 

Use the ODBC Data Source Administrator (Control Panel -> Administrative Tools)

 

This is most likely a permissions related issue, and using the DSN approach may allow you to specify the username/password that will then allow you to have access to files on remote machine.

 

Failing that, a workaround may be to regularly copy the Excel file to local machine.

[LittleRock]

The System DSN is setup. I am not sure how to establish the workaround (copy the Excel file at interval).

[SupportTeam]

To copy file regularly you can use the Windows Task Scheduler.

 

Create a .BAT file that performs the copying and then setup the scheduler to run the batch file at intervals.

 

See: Start->Programs->Accessories->System Tools->Task Scheduler