[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 :)

[incy.livejournal.com]

VBS shouldread VBa sorry

[thepaintedone.livejournal.com]

Having chatted to Simon previously about this lash up, it's a really horrible nasty mess. Bascialy they have an Access client using linked tables to talk to a SQL server. The queries are not only stored client side, but because Access doesn't know about the SQL server, they are exectuted client side as well. i.e. Access downloads the whole damn table then works it out for itself, rather than passing the query up to the SQL server. That isn't by any design, simply how Access linked tables work if you don't tell it otherwise. Even worse when you bind a form to the linked table, access will leave cursors in the SQL table while the user is inputing data, same as it would do with an Access back end.

If you know anything about databases you'll be screaming and trying to claw your eyes out at this point. It's probably a worse solution than just using an Access backend. The performance is about the same (all the data gets dumped to the client for processing either way), but unlike an Access backend, SQL server will actualy try and do locking and other such things to manage the horrible mess Access will inflict upon it.

Makes me weep to think of it.

[d-floorlandmine.livejournal.com]

Queries: I have no idea - I've been trying to avoid getting too involved in it! [grin] Doesn't help that I don't really have any knowledge or experience of the Access/SQL combination concept anyway - and at present they haven't budgeted for training.

Personally, I much prefer VBA to macros. Unfortunately, it would appear that the "writing to" stuff is done using an update query, so it is copying data across, rather than performing it using the form's controls.

The [cough] so-called developer (who knew less than I do about Access when she started on this project) has now returned to the Antipodes, from where she's attempting to give remote support via email (ultimate arms-reach support).

[thepaintedone.livejournal.com]

Unfortunately, it would appear that the "writing to" stuff is done using an update query, so it is copying data across, rather than performing it using the form's controls.

This is actualy the correct way to talk to the SQL server for edits. The alternative, binding the controls and/or form to the table via the linking would do horrible things to the server.

If they only could have done the same for reading :o/

[d-floorlandmine.livejournal.com]

[grin]

[incy.livejournal.com]

in which case you will need very long range weapons. Looks like development issues. Guessin git needs a bit of a rewrite. If it is any help I can try and see if I can get time to have a look (that is if you are allowed to show it to third parties) one night after work (mind you it might be into next week now).

[d-floorlandmine.livejournal.com]

Thanks for the offer, but it's probably not strictly third-partiable - although I'll see what the Research Department (who own it) say ...