Intermittent Errors Connecting Excel 2007 to SQL Server 2005

tommill · Sep 17, 2009

We use Excel 2007 to read data from a SQL Server-based manufacturing application to standard Windows XP PCs and laptops. The spreadsheet has a worksheet to store the data read from SQL Server, and another worksheet to manipulate and format it. In theory, this is a great process. In practice, it can't be relied on as the Excel files keep ending up with corrupt data for no apparent reason.

The Excel files are set as 'read only' to avoid users changing them. I keep master copies of them.

On a regular basis, after opening them, they will display '#REF#' in some cells. When checking the formula in the cells in question, the formula has changed (even though it is a 'read only' file), and stays changed until the file is corrected and re-written. For example

"=IF(G$1=$C354,$D354,0)" is replaced by "=IF(G$1=#REF!,#REF!,0)"

Occasionlly, references to rows or columns change, for example

"=IF(G$1=$C354,$D354,0)" is replaced by "=IF(G$1=$C354,$F354,0)"

Once these errors occur, a user will let me know the file has 'gone wrong' and I check my master copy, only to find the identical errors within them as well.

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5Ctomm%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CDOCUME%7E1%5Ctomm%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5Ctomm%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman";} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-size:10.0pt; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt;} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 72.0pt 72.0pt 72.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> We are using the standard Excel Data Connection (OLE DB Query) with the connection string and SQL statements shown below (data read into worksheets 'LIVE_MPS_PRODUCTS' and 'LIVE_PLAN_DATA'). I have tried modified connection strings, to no avail. Users of Excel 2003 were getting the same problems (although the files were created using Excel 2007 and saved in 1997-2003 format).

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5Ctomm%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CDOCUME%7E1%5Ctomm%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5Ctomm%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman";} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-size:10.0pt; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt;} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 72.0pt 72.0pt 72.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> [FONT=&quot]Connection String:<o

></o

>[/FONT]
[FONT=&quot] <o

></o

>[/FONT]
[FONT=&quot]Provider=SQLOLEDB.1;Password=Exe1Exe1;Persist Security Info=True;User ID=efacs;Initial Catalog=efacdb;Data Source=erpdb;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=D3V0Q64J;Use Encryption for Data=False;Tag with column collation when possible=False<o

></o

>[/FONT]
[FONT=&quot] <o

></o

>[/FONT]
[FONT=&quot]SQL for LIVE_MPS_PRODUCTS<o

></o

>[/FONT]
[FONT=&quot] <o

></o

>[/FONT]
[FONT=&quot]Select allpartmaster.partnum As planpart, allpartmaster.partdesc,<o

></o

>[/FONT]
[FONT=&quot]allpartmaster.uom, allpartmaster.packfactor,<o

></o

>[/FONT]
[FONT=&quot]LTrim(RTrim(allpartmaster.prodgroup)) As prodgrp,<o

></o

>[/FONT]
[FONT=&quot]stockedparts.abcclassification As abc<o

></o

>[/FONT]
[FONT=&quot]From allpartmaster Right Join<o

></o

>[/FONT]
[FONT=&quot]mpsdetail On allpartmaster.partnum = mpsdetail.mpsdpart Left Join<o

></o

>[/FONT]
[FONT=&quot]stockedparts On mpsdetail.mpsdpart = stockedparts.partid<o

></o

>[/FONT]
[FONT=&quot]Group By allpartmaster.partnum, allpartmaster.partdesc, allpartmaster.uom,<o

></o

>[/FONT]
[FONT=&quot]allpartmaster.packfactor, stockedparts.abcclassification,<o

></o

>[/FONT]
[FONT=&quot]allpartmaster.prodgroup<o

></o

>[/FONT]
[FONT=&quot]Having (Sum(mpsdetail.mpsdwip) > 0) Or<o

></o

>[/FONT]
[FONT=&quot](Sum(mpsdetail.mpsdplan) > 0) Or<o

></o

>[/FONT]
[FONT=&quot](Sum(mpsdetail.mpsdsales) > 0) Or<o

></o

>[/FONT]
[FONT=&quot](Sum(mpsdetail.mpsdfcast) > 0) Or<o

></o

>[/FONT]
[FONT=&quot](Sum(mpsdetail.mpsdmps) > 0)<o

></o

>[/FONT]
[FONT=&quot]Order By allpartmaster.partnum<o

></o

>[/FONT]
[FONT=&quot] <o

></o

>[/FONT]
[FONT=&quot]SQL for LIVE_PLAN_DATA<o

></o

>[/FONT]
[FONT=&quot] <o

></o

>[/FONT]
[FONT=&quot]Select mpsdetail.mpsdpart, mpsdetail.mpsdsdate,<o

></o

>[/FONT]
[FONT=&quot]LTrim(RTrim(mpsdetail.mpsdperiod)) As period, mpsdetail.mpsdplan,<o

></o

>[/FONT]
[FONT=&quot]mpsdetail.mpsdwip<o

></o

>[/FONT]
[FONT=&quot]From mpsdetail<o

></o

>[/FONT]
[FONT=&quot]Where (mpsdetail.mpsdplan > 0) Or<o

></o

>[/FONT]
[FONT=&quot](mpsdetail.mpsdwip > 0)<o

></o

>[/FONT]

These are 'read-only' files that are obviously somehow being overwritten.

I know very little about SQL Server, so do not know if this is involved. Can anyone throw any light on this?

Thanks

Intermittent Errors Connecting Excel 2007 to SQL Server 2005

tommill

New Member

Excel Facts

Similar threads

Forum statistics

Share this page

Intermittent Errors Connecting Excel 2007 to SQL Server 2005

tommill

New Member

Excel Facts

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock