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
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies