Category Archives: Access

Importing into SQL Server

Alas SQL Server Management Studio isn’t as friendly as it could be for pasting in data. You’d think Microsoft would have this humming, but when I tried to paste from Excel, it attempted to paste the entire first row from my spreadsheet into the first column (in one row) of the database.

Using MS Access to open up the database probably would have worked, but I didn’t have it on that machine.

Trying to import using the SQL Server Import And Export Data wizard from a CSV text file worked for a small amount of data, but the 80,000 rows I was trying to import from the world ports code list didn’t. Time and time again it would report an error (unspecified) and give me the option of Abort, Retry, Ignore. No matter option I chose, it crashed.

While the 64-bit version of the wizard on my 64-bit Win7 machine didn’t allow you to import from Excel/Access, the 32-bit version did (presumably because MS Office, at least the version I have installed, is 32-bit).

The next problem was that it only supported Excel 2003 format, which can’t handle more than 64K rows. I ended up having to split the data into two and import the two spreadsheets separately. Then it worked.

Shame the wizard is so flaky, and of course it’s a big shame that Management Studio doesn’t do copy/paste like one would expect. (Maybe that too was a 32-bit/64-bit issue.)

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.)

Do you really really want to open the file?

I know the spread of macro viruses via consumer products is a dangerous thing, and obviously Microsoft in particular have had to take action to help slow them down. But I’m not convinced the plethora of dialog boxes that now adorns every application is really the way to go.

For instance, if you open an MDB in Access 2003 that was created in Access 2000, you are likely to get no less than three separate security dialogs asking if you’re sure, if you’re really sure you want to open the file.

I’ve been using Access for some years, but I don’t know what an “unsafe expression” is. I created the MDB I’m opening, and it’s just got tables in it. No macros, no VBA modules, not even a report or query. There’s nothing unsafe in it. So I said No, don’t block the unsafe stuff you imagine is in this file. Give it all to me.

Having said no, I don’t want them blocked, it then complains that it can’t block them. Obviously it doesn’t trust me to answer sensibly, it really wants to block those imaginery unsafe items. But it can’t without sending me off to Windows Update to install Jet 4 SP 8 or later.

I had to really concentrate to work out what the Yes/No options at the bottom of the dialog are for. They’re nothing to do with blocking the alleged unsafe expressions, or installing the service pack. Nope. What it’s asking is if I still want to open the file.

Having ascertained that I don’t care about the unsafe expressions that don’t exist, and I still want to open the file… it asks me just one more time, by suggesting the bleeding obvious: “This file may not be safe if it contains code that was intended to harm your computer.” Well duh, no kidding.

The cunningly placed Cancel button on the left could easily lead one to click that by default. But finding and clicking the Open button finally really opens the file.

Now, why did I want to look at this file again?