Fighting with MS Access and version incompatibility
I'm working on a nice little reporting tool to smash together some management information from a number of weird places. (Not quite as bad as this gem, but interesting nonetheless).
The adventure started last weekend with an attempt at a direct connect from Access to Lotus Notes, using good ol' ODBC. That driver was slower than the old AS/400 ODBC driver, and I used to use that connection (400 ODBC) as a worst-case example. Still, some fun collaboration late on a Saturday night, validating that I am not insane.
I gave up on that, and went to the indirect approach of importing and cleaning the data into temporary tables in my Access database. I prefer using Access as little as possible, and then only as a nice little report writer, but the performance of that connection would have made report development untenable. Like most decent techs these days, I google for code, and assistence when that code breaks; I ran into interesting problem #1 with the TransferSpreadsheet method. It was incredibly difficult to find the specific problem that I ran into (Run-Time error 3125, where an exclamation point in a string constant magically transformed into a dollar sign). Weird error, and the answer (which I luckily stumbled onto) was that an Excel tab should not have spaces in it. I couldn't even find that in MS's Knowledge Base, which surprised me.
The real challenge, however, was creating something in Access XP that could run on all the instances of Access 2000 in my group. Actually, the fix was simple, yet again, all the clues were misleading. Everything I could search on was leading me to reference problems, and there just weren't any. However, after I finally just read the code slowly, I found a specifc method that had been added in to move from 2000 to XP. So, instead of filling my list box using a loop with the .AddItem method, I had to build a string and create the .RowSource property.
Translation Required: To this point, I've written about stuff that would put most business folks asleep. Why is any of this relevant? I would explain my objectives and problems much differently, like this:
I'm working to get some visibility on our resource prioritization issues, but I need to create something that everyone can use. That can be tricky.
Actually, that's enough for most folks, everything else is just details.
Technorati Tags: access, excel, google, odbc, error, code, simple
posted by James P. MacLennan at 6:35 PM
| Permalink
| 0 Comments
