June 2025

S M T W T F S
1234567
891011121314
15161718 192021
22232425262728
2930     

Style Credit

Expand Cut Tags

No cut tags
Tuesday, September 21st, 2004 12:09 pm
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?
Tuesday, September 21st, 2004 04:28 am (UTC)
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 :)
Tuesday, September 21st, 2004 04:29 am (UTC)
VBS shouldread VBa sorry
Tuesday, September 21st, 2004 07:12 am (UTC)
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.
Tuesday, September 21st, 2004 07:14 am (UTC)
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).
Tuesday, September 21st, 2004 07:20 am (UTC)
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/
Tuesday, September 21st, 2004 07:30 am (UTC)
[grin]
Tuesday, September 21st, 2004 08:10 am (UTC)
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).
Tuesday, September 21st, 2004 09:07 am (UTC)
Thanks for the offer, but it's probably not strictly third-partiable - although I'll see what the Research Department (who own it) say ...
Tuesday, September 21st, 2004 06:21 am (UTC)
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.
Tuesday, September 21st, 2004 06:22 am (UTC)
That should of course have read Access solution to SQL.
Tuesday, September 21st, 2004 09:06 am (UTC)
[grin]
Tuesday, September 21st, 2004 06:43 am (UTC)
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.
Tuesday, September 21st, 2004 06:50 am (UTC)
From the Access help (DAO Recordet, seek method)
--
You can't use the Seek method on a linked table because you can't open linked tables as table-type Recordset objects. However, if you use the OpenDatabase method to directly open an installable ISAM (non-ODBC) database, you can use Seek on tables in that database.

In an ODBCDirect workspace, the Find and Seek methods are not available on any type of Recordset object, because executing a Find or Seek through an ODBC connection is not very efficient over the network. Instead, you should design the query (that is, using the source argument to the OpenRecordset method) with an appropriate WHERE clause that restricts the returned records to only those that meet the criteria you would otherwise use in a Find or Seek.
Tuesday, September 21st, 2004 07:28 am (UTC)
Cheers dude. I only vaguely understand the recordset stuff, so I can almost certainly guarantee that the person who put this thing together doesn't. And yes, it's an ODBC set up. [grin] I had had some thoughts which were verging towards the WHERE clause, but it's been a while since I've been a-wrangling Access.
Tuesday, September 21st, 2004 07:34 am (UTC)
To be fair to the muppet who put it together, they might not even know it's been done. A wizzard used to create a Macro could have silently set this up under the surface.

You seriously need to re-engineer this thing. I expect you don't have the budget for a contractor to look at it (*waves*), but if you want to hook up sometime I'd be happy to spend an hour or so walking you through how to do this so it works properly. You could maybe pop down to my office one evening and I'll show you what I've done here. Or I can just e-mail you a load of code that you can drop in to do it for you. I use standard functions to work with SQL server in Access, which should be fairly portable.
Tuesday, September 21st, 2004 08:15 am (UTC)
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 ...
Tuesday, September 21st, 2004 08:28 am (UTC)
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.
Tuesday, September 21st, 2004 09:06 am (UTC)
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 ...
Tuesday, September 21st, 2004 09:11 am (UTC)
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.
Thursday, September 23rd, 2004 12:22 am (UTC)
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.
Thursday, September 23rd, 2004 06:36 am (UTC)
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]