telophase | (no subject)

YES!

*looks shifty*

I sort of like it....

I have an Access database hooked to a SQL Server database in Mysterious Ways [TM] that contains the records of our subscribed databases. In the form where I enter the databases, there's a sub-form that contains the subject categories, to which I've just added a bunch of new ones.

It won't sort alphabetically. None of the obvious solutions work, and they probably don't work because it's all hooked together in said Mysterious Ways [TM], which I don't understand.

And it's driving me insane.

Oh it gets ugly when they meld them in weird ways. I never can get them to play nice together. One or the other, OK, sure, but together? Nu-uh.

I have a sekrit theory that Access is designed with flaws so that Microsoft makes more money on tech support or custom help, but I am cynical that way.

I've kicked the whole mess up to my boss who, I think, is the one who created it in 1999. Maybe with luck he'll have a clue. :)

Did it sort alphabetically before you added the new subject categories?

If not, do you know what the field type for the categories is?

Nope. It looked like it did, because the original categories were entered into their table in alphabetical order, but it wasn't doing any sorting.

I'm going hands-off on it right now, though, as I don't want to change it if my boss is going to be looking at it.

So can eAccelerator. Yeah, it's that kind of day.

Ok. If the SQL Server database is using the "text" field type for the categories, then it's not sortable. The fix would be to change it to use "char" or "varchar". Where "change" means that you'd have to copy all of the data into a new column, because you can't just change column types.

"text" is intended for storing large amounts of data, and it's not a good idea to sort that kind of thing, because when I say large, I mean up to one or two gigabytes. Sadly, because Access is not a very good database, it has a "text" (or some similar name) column type that you are allowed to sort, because the amount of data in it isn't nearly as vast, and therefore people who start out using Access get into bad habits.

I'll shut up now.

So would one have to copy the column, delete the old, the reconnect everything? Or could "char" column be created with formulas to just set =old_text_column then still sort on that?

Pondering possible easy solutions... i.e. kluges.

Copying and so forth would be the cleaner solution, but might be a bit of a pain. That's what I'd do--I'd set up a new table, write a Perl script to do the copying, and then reset everything. (VB or other scripting languages would also work--it's just that Perl is what I mostly use for scripts right now.)

You might be able to get away with using a formula on the new "char" column, but remember that that formula will have to be invoked every time someone refers to that column, so, in the long run, that will use more processing power than the other solution. Also, my experience has been that the more scripts you put in a database, the more fragile it becomes. However, my experience is with giant corporate databases; if your database is not huge, it's probably not as much of an issue.