[SQL/Access] Evil Database of Doom

[dancefloorlandmine]

There appears to be a slight glitch, in that an unnecessarily complicated process is not working correctly.

Background information
The database is running on SQL Server, with a Microsloth Abscess 2000 front end on each relevant desktop, using linked tables.

Problem
The developer created a drop-down to enable the selection of previous contacts. This then triggered a macro (mutter mutter) which ran a Refresh operation, and a pair of queries (no, I don’t know why a pair either), which populated the main table with data from the saved information on the previous contact. However, this does not then display on the form on screen. Just to add to the fun, most users will view the form with the "Data Entry" property set to True.

When, on the test database, the tables were imported instead of linked, and a “repaint” operation solved the problem. However, when using the linked tables on the real thing, nowt - the repaint doesn't seem to work.

Anyone got any ideas?

Comments

[d-floorlandmine.livejournal.com]

Budget? Budget? [grin] That would be a decision for the Research department - technically we in IT don't strictly have responsibility for this system, so I'm really only having a look because I'm a nice generous soul (and it was more interesting than the blasted command line batch file I've been working on). Cheers for the offer, though, dude - I might just have to take you up on that. Where is your office?

Of course, if they'd waited until next year, I might have done a SQL module on my MSc ...

[thepaintedone.livejournal.com]

At the moment I'm at Oxford Circus, but in a couple of months we're moving to the City.

How big is your application? i.e. how many primary data tables (excluding lookups), forms and buttons? I'm wondering how much work would be involved in re-doing it properly. With the right functions it isn't that hard. Or at least the display part isn't. Processing and editing is more effort.

[d-floorlandmine.livejournal.com]

I think there's about seven tables - however, the main form has about four or five pages, and loads and loads and loads of controls ...

[thepaintedone.livejournal.com]

What kind of controls? Lots of text boxes are easy. I have a function that will do a whole forms worth in one pass, builds the query dynamicaly from a table which tells it where to put the data on the form.

It's buttons, list boxes and combo boxes that are more trouble, as they tend to have processing behind them.

[thepaintedone.livejournal.com]

Something I thought I'd mention. As I've been doing our migration from Access to SQL, I've once again been amazed at the performance difference, and not just because the SQL server itself is a better data provider.

I run some users over a VPN home connection, which since it's on DSL has a bandwidth of about 256k. When I tried running our old database over one of those connections (after tricking access, it doesn't like working over a different subnet), it took several minutes to open, then about a quarter of an hour to display a single record group. This is nothing to do with the data processing speed, but rather that Access drags all the required data to the client, which is obviously heavily bandwidth intensive. Trying the new optimised SQL client, it runs seveal times faster over the VPN (opens in less than a second, ditto opening a record) than the old Access solution does over the LAN, purely because of the greater efficiency in data transfer, i.e. true thinner client.

What got me thinking is that we've been having all sorts of unexplained network slowdowns for ages now, which are getting worse over time. Despite trawling Deja and Microsoft.com and investigating every possible bug in XP networking (of which there are a few), I've never been able to tie it down. Nor was there any pattern to it. Having graphically seen the quantity of data that access throws over the network in it's normal day to day operation, I'm now increasingly thinking that our network is simply being overloaded by the 40 odd people I have using various Access systems.

I thought I'd mention this, as while you have a SQL server, you are essentialy still running 'fat client'. If you are having bandwidth and network slowdown issues, that could be the culprit. Bizarely though, your system may be less pronce to this, as since your using extensive form and control binding, Access will tend to cache lots of the data locally (which has it's own problems). So it could throw loads of data about when opening a form, but not when moving from record to record. Whereas our fully unbound system will go to the recordset anew every time the user moves from record to record.

[d-floorlandmine.livejournal.com]

Cheers dude.

So it could throw loads of data about when opening a form, but not when moving from record to record.

Except that I don't know how many "Refresh" commands are buried in the macros ... [grin]