[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

[incy.livejournal.com]

are the queires on the fornt or back end?
I am a bti ocnused are you saying you are writing the (retrieved data from the back end tables) to a table and then to the form or directly to the form.

Can you turn the Macro into VBS (This would be a very godo thing generally) and check the vlaues of the controls they are ebign written to?

And lastly have you got any high velocity weapons to hand and the address of the developer (at a pionch his mothers will do :)

[thepaintedone.livejournal.com]

Not without actualy seeing the thing in the flesh. To be honest it sounds like one of Access's weird little quirks (of which it has many) rather than anything specific you're doing wrong. What you are doing is so bastard bad and wrong, on so many levels, that trying to troubleshoot it is a bit like worrying over a papercut on a dismembered corpse.

If you can zip up a copy of your client and mail it to me I can try to decontruct what it's doing, but without the tables as well it'll be half guesswork.

Incidentaly, please don't refer to that person and as a developer. Anyone who would voluntarily create such a Frankenstein mess needs killing.

Also, I'm half way through migrating our SQL solution to Access, if you want some more pointers on how to do things properly, let me know.

[thepaintedone.livejournal.com]

Hmm, I've had a thought although I can't say how it would directly relate to what you are doing.

In VBA, if you open up a recordset you have various modes you can open it in. On a simple level these are things like 'read only' versus 'editable'. Some access functionality, in particular the 'seek' function, requires you use a certain kind of recordset. The particular kind it needs is only available on local tables, not linked tables. So when you migrate from one to the other you can no longer use the seek method on a recordset. I hit this with a DB a couple of years ago that switched to a linked solution and had to re-write it to use a SQL query instead of the seek method in order to find a given record.

It's possible that whatever macro or wizzard thats running here is using the seek method, in fact it wouldn't surprise me at all in a Macro. This would be consistent with the behaviour you describe as basicaly it won't do anything when you run the 'seek' command. Of course running a 'seek' on a linked SQL server table is so horrible that it makes me want to weep (download the entire table, then do a table scan), but there you go.