I was up til two last night, tweaking a small Microsoft Access application I was prototyping to show to some end-users this morning for their feedback.
<"techie twaddle">I'd built an initial screen with a subform linked to a big union query - 11 unions in fact - which would give the user a snapshot of the project status with all the key statistics on it. Worked perfectly on my Access 2000 setup. But when you open the beast in Access 2002 as used at the client site, something dreadful happens to the query and you show the users a nice blank subform. The query itself seems to be totally trashed and you can't open it in design mode any more. Bad news, because you've lost all your SQL. The fix seems to be to write another (non-union) query which uses the union query as a view, and link the form to the non-union query. You have to rewrite all the SQL first, of course. <"/techie twaddle">
Fired it up, it was bust, a ten o'clock meeting, and I would be demonstrating (to three people who'd driven 80 miles to see it) a broken application that I wouldn't have time to find a fix for before they arrived.
Initial feedback was that it would be nice if the screen wasn't blank.
There were other screens which did work, and while the visitors were checking out the screenprints and writing their comments and suggestions, I was frantically finding a fix - which happily materialised in time for me to show them a kosher front-end.
Not the most stress-free walkthrough I've ever done. I'm somewhat tired. Need a drink.
Or three.
UPDATE - A bad day for me at the office is like yesterday's. A bad day for Susan, a nurse, is working for an hour in the back of an ambulance on someone who's arrested - and not being able to bring them back. Or visiting and talking with a thirtysomething mother of three young children, whose cancer has just been diagnosed as terminal. I need to remind myself of that occasionally. Working in IT isn't exactly Helmand province !
Revert to Default
12 hours ago
3 comments:
ELEVEN unions in one query? In an ACCESS database? And then you thought you could move from ONE major version of an MS product to another without testing!
I had you down as a wise man Mr Tall.
One of the things I like about LAMP is that there's very little of this kind of thing to worry about. You kind of know what you're getting in terms of presentation, business and database logic, and it doesn't change very much, and of course if you provide the software as a service the only variable you don't have control over is their browser.
Be fair, it's not got a huge amount of data in it - a couple of hundred thousand rows scattered over three or four tables.
As for the migration/testing - you live and learn. Timescales are tight.
After a few hundred thousand rows Access data import/export starts to crumble - but using VB code you can import over a million rows and get meaningful results from queries quite quickly.
I think it would take me much longer to develop in LAMP than Access - I'd have to learn it first, for a start. And the client site's administrators would have told me to go away - I have to use their tools. The Access is a 'quick and dirty' solution and approved as such - a mighty .net / SQL Server app is being built, but it's six months away and they want this bit of the functionality in six weeks.
Access has no way of allowing the user to prioritise the joins. Or rather it chooses the priority for you. For performance sake, you should create sub queries and then join those. That reduces the amount of joins and makes the result a bit easier to read.
Also when Access gets corrupted so as to refuse to show the SQL, you can still sometimes access it from VB.
Something along the lines of
---
Sub prLabanSafe()
Dim qry As DAO.QueryDef, f As Long
With CurrentDb
'create a file
f = FreeFile
Open .Name & ".txt" For Output As f
'loop thru queries
For Each qry In .QueryDefs
Print #f, qry.Name
Print #f, "--------------"
Print #f, qry.SQL
Print #f, vbCrLf & vbCrLf
Next
'tidy up
Close
Set qry = Nothing
End With
End Sub
---
That would create a text file containing all the SQL in the db including that included in forms. Then recovery would be achieved by cut and paste. In the past I have found SQL recoverable even though the query won't open, but there's no guarantees.
Of course, I'm probably teaching you to suck eggs and you've long moved on by now.
Post a Comment