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?
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?
no subject
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 :)
no subject
no subject
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.
no subject
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).
no subject
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/
no subject
no subject
no subject
no subject
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.
no subject
no subject
no subject
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.
no subject
--
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.
no subject
no subject
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.
no subject
Of course, if they'd waited until next year, I might have done a SQL module on my MSc ...
no subject
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.
no subject
no subject
It's buttons, list boxes and combo boxes that are more trouble, as they tend to have processing behind them.
no subject
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.
no subject
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]