Category Archives: VB

Classic VB go bye bye

Mainstream support for Visual Basic 6 (the last version before .Net) finished at the end of March, and there’s growing murmurs in the VB development community, calling for it to be resurrected. While “classic” VBers didn’t mind being shunted into the .Net framework, they objected to some of the bigger changes to the language itself, which made it difficult to migrate old projects over. And remembering that “classic” VB is the most popular computer language ever, there’s a lot of old systems out there still running with it. A petition has been organised, with nearly 200 MVPs having signed up so far.

Microsoft have put up a new site highlighting VB6 called VBRun (harking back to the pre version 5 days when the main DLL was called VBRun). It’ll have VB6 content on it, as well as nudging developers towards .Net.

I work with VB. I’ve got a lot of VB6 code still running. But I’m slowly moving some stuff over to .Net. I’m not convinced it’s better yet. It’s certainly different, but I’ll get used to it eventually.

Inaccuracies reading Excel via ADO

Yesterday’s discovery with reading Excel via ADO and the ODBC Text Driver: numeric values may be wrong if it takes a guess that the column contains date fields.

The geeky detail (found in VB6SP5, Excel 2003, ADO 2.7, Jet Provider 4.0):

The text driver looks at the first few lines (configurable via the MaxScanRows setting or the Rows To Scan field in the DSN configuration box) to figure out what sort of data it is dealing with in each column. If there are numbers, it assumes the columns are numbers.

But if it sees dates, and sees no other data in the next few rows, then it assumes all the data in those columns are dates. If it gets further down to rows containing actual numbers, it still thinks it’s reading dates, and when it tries to convert the number to a date and back again, it causes a rounding error and ends up adding 1 to the final number.

In my case, the solution was to get the spreadsheets to contain zeroes (quite valid for the data being loaded) in the first few rows, instead of blanks. A little tricky, but it remains a good method for getting data out of Excel.

Office Object Library problems

One of the machines a program of mine runs on is still NT4 and Office 97. It seemed to keep working okay when I was on Win2K/Office 2000, but now I’m on WinXP/Office 2003 it’s crashing when calling the Excel object library to open an XLS.

MS have documented that this can happen after re-compiling with the Office 2000 libraries or later (though I’m sure it didn’t happen with Excel 2000).

“This behavior is by design.” Application errors by design. Yeah. SURE.

Excel application error

Apparently the solution is to use that normally-considered-evil late-binding; DIMming as an Object and using CreateObject.

But it didn’t seem to work for me. So I dug out an old copy of Office 97, installed it into a separate directory to Office 2003. Removed the reference to Excel 11, added Excel 8 instead (EXCEL8.OLB), recompiled and all is well.

Excel to VB bug?

From my limited testing (I had to find a workaround pronto) seems to be some kind of bug when using VB6, ADO 2.7, ODBC text driver to read from an Excel spreadsheet. Recordsets with date fields come in with the dates encoded as integers. This is normally no problem, as you can use IsDate to check if it’s valid, then CDate to convert it to a date.

But what I’ve found is that IsDate has stopped working… that is, it’s stopped returning True for field values that can be converted to dates, eg CDate works. Whether this is something to do with the new year, or a new version of something on my machine, I haven’t yet figured out, but I ended up writing a wrapper IsDate function that just tries the CDate regardless, and returns a False (eg not a date) if it errors.

An initial dig in the MSKB found nothing about it, but I’ll do some more exhaustive digging and try to get a definite answer later. The answer being, I suspect, “Switch to VB.Net, you luddite!”

VB Garbage collection

This article claims it’s pointless to Set objects to Nothing in VB when you’re finished with them, except in particular cases. Maybe it’s those particular cases I’ve come up against, but I don’t believe that, myself. I’ve observed time and time again IIS apps that call VB6 DLLs grabbing more and more memory for DLLHost, and eventually choking until an iisreset has been done.

Going through the code (both the ASP VB Script and the underlying DLL VB) with a fine tooth comb and ensuring EVERY object got set to Nothing afterwards cleared these issues. Though I’m embarassed to admit that on one early project I worked on, a bunch of people looked through the code trying to find the leak, couldn’t find it, and someone eventually wrote a nightly iisreset batch job.

Reading that article, it occurs to me that maybe there was a case of destroying objects that referred to each other in the wrong order. The code is long sinced vanished, so I don’t know, and don’t care to find out.

As far as I’m concerned, it’s not only better for performance to destroy your objects, and a helluva lot easier to do all the time than just some of the time. It’s also tidier in the code. I’ll keep on doing it.

.Net, of course, is a different kettle of fish altogether.

Empty recordset from MDB queries via ADO

Every so often I’ll find the solution to an obscure issue, and wonder “why isn’t this in the MSKB? Why has nobody written an explanation I could find in Google?” Well through this site, now I can do something about that.

If you call an Access (Jet 4.0) query via ADO/MDAC, you may get empty recordsets back if the query uses parameters. There’s a bug that Microsoft reckons applies to RDO calls, but also appears to affect ADO/MDAC 2.7 in the same way.

To fix it you need to define the parameter size to an arbitrary amount before you set the parameter. Here’s some hopefully useful (to somebody, somewhere, some day) sample code…

(This is VB6. Dim everything beforehand of course, ‘cos everybody loves early-binding. What, you want to play with fire by using late-binding and no Option Explicit? Silly you.)

Set oCommand = New ADODB.Command
oCommand.ActiveConnection = gcDatabase.Connection
oCommand.CommandText = "select * from MyQuery"

Set oParam = New ADODB.Parameter
oParam.Size = 255 'Take this out and you get an empty recordset back, but no error. Wacky.
oParam.value = "your value"
oParam.Name = "your parameter name"
oParam.Type = adYourFieldType
oCommand.Parameters.Append oParam

Set oRS = New ADODB.Recordset
oRS.Open oCommand, adOpenForwardOnly, adLockReadOnly

(and close and destroy everything when you’re done, natch. You know what VB6’s garbage collection is like.)