Home

Excel VBA - Reading SQL data | Now you see it, Now you don't

%3CLINGO-SUB%20id%3D%22lingo-sub-201476%22%20slang%3D%22en-US%22%3EExcel%20VBA%20-%20Reading%20SQL%20data%20%7C%20Now%20you%20see%20it%2C%20Now%20you%20don't%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-201476%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I'm%20having%20the%20damnedest%20time%20trying%20to%20figure%20out%20what%20Excel%20is%20doing%20with%20my%20data.%26nbsp%3B%20I%20have%20an%20Excel%20spreadsheet%20where%20I%20connect%20to%20a%20SQL%20table%20using%20ADO%20to%20read%20data%20and%20enter%20that%20data%20into%20rows%20and%20columns.%26nbsp%3B%20That%20all%20works%2C%20well%20mostly%2C%20but%20I%20have%20a%20situation%20where%20data%20in%20certain%20fields%20of%20a%20recordset%20doesn't%20get%20written%20into%20Excel.%26nbsp%3B%20As%20I'm%20debugging%20my%20code%2C%20I've%20run%20into%20a%20situation%20where%20I%20can%20see%20the%20missing%20data%20-%20and%20then%20it%20disappears.%26nbsp%3B%20Hopefully%20this%20image%20will%20make%20it%20clear.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20image%20I%20have%20a%20break%20point%20on%20a%20line%20and%20my%20cursor%20positioned%20on%20the%20field%20value%20such%20that%20when%20I%20%22F5%22%20Continue%20the%20field%20value%20is%20instantly%20displayed.%26nbsp%3B%26nbsp%3BBoth%20the%20left%20and%20right%20image%20is%20the%20same%20break%20point%20-%20the%20only%20change%20between%20the%20two%20is%20me%20moving%20my%20cursor%20off%20and%20then%20back%20on%20the%20field%20value.%26nbsp%3B%20Now%20you%20see%20it%2C%20now%20you%20don't.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F35509iF5277C22A5E253C9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22VanishingValue.jpg%22%20title%3D%22VanishingValue.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI'm%20using%20Excel%202010%20(that's%20what%20we%20have%20here)%20and%20connecting%20to%20SQL%20using%20ADO%20connection.%26nbsp%3B%20I%20can%20stop%20debugging%20and%20restart%20the%20module%20and%20the%20issues%20happens%20again%20and%20again%20to%20the%20same%2040%20or%20so%20records%20(fields).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20recordset%20returned%20is%2017%20rows%20with%20128%20columns%20each.%26nbsp%3B%20Not%20big%20data%20so%20I%20don't%20think%20it's%20RAM%20related.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20or%20suggestions%20would%20be%20appreciated!%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EJim%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-201476%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-485544%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VBA%20-%20Reading%20SQL%20data%20%7C%20Now%20you%20see%20it%2C%20Now%20you%20don't%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-485544%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20the%20same%20issue.%20I%20converted%20from%20an%20Access%20database%20(where%20the%20same%20code%20worked)%20to%20an%20SQL%20database.%20The%20data%20exists%20as%20I%20am%20able%20to%20query%20the%20database%20using%20Excel%20and%20get%20the%20data.%20The%20VBA%20program%20actually%20creates%20this%20data%20and%20posts%20it%20to%20SQL.%20But%20the%20subsequent%20read%20back%20into%20the%20recordset%20has%20the%20same%20symptoms%20you%20indicated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-540089%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VBA%20-%20Reading%20SQL%20data%20%7C%20Now%20you%20see%20it%2C%20Now%20you%20don't%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-540089%22%20slang%3D%22en-US%22%3E%3CP%3E%40e%20have%20determined%20the%20problem.%20The%20field(s)%20in%20question%20are%20defined%20as%20NCHAR%20(variable%20length).%20Changing%20to%20a%20static%20length%20eliminates%20the%20issue.%20But%20this%20seems%20like%20a%20VBA%20issue.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Deleted
Not applicable

Hello, I'm having the damnedest time trying to figure out what Excel is doing with my data.  I have an Excel spreadsheet where I connect to a SQL table using ADO to read data and enter that data into rows and columns.  That all works, well mostly, but I have a situation where data in certain fields of a recordset doesn't get written into Excel.  As I'm debugging my code, I've run into a situation where I can see the missing data - and then it disappears.  Hopefully this image will make it clear.  

 

In the image I have a break point on a line and my cursor positioned on the field value such that when I "F5" Continue the field value is instantly displayed.  Both the left and right image is the same break point - the only change between the two is me moving my cursor off and then back on the field value.  Now you see it, now you don't.

VanishingValue.jpg

I'm using Excel 2010 (that's what we have here) and connecting to SQL using ADO connection.  I can stop debugging and restart the module and the issues happens again and again to the same 40 or so records (fields).

 

 

The recordset returned is 17 rows with 128 columns each.  Not big data so I don't think it's RAM related.

 

Any ideas or suggestions would be appreciated!

Thanks

Jim

 

2 Replies

I have the same issue. I converted from an Access database (where the same code worked) to an SQL database. The data exists as I am able to query the database using Excel and get the data. The VBA program actually creates this data and posts it to SQL. But the subsequent read back into the recordset has the same symptoms you indicated.

@e have determined the problem. The field(s) in question are defined as NCHAR (variable length). Changing to a static length eliminates the issue. But this seems like a VBA issue.

Related Conversations
Pulling data from one tab to another
krysphares in Excel on
6 Replies
vba sending email w/ attachment
katrina bethea in Excel on
7 Replies
Need some help with my Vlookup or a new idee
Jim Brastad in Excel on
5 Replies
Maps Data for Scotland
Chris Nicol in Excel on
19 Replies