Guidance requested

[mole999]

I have the following

DSA = DLookup("[DSA]", "Units", "[Unit]=Forms!Data![Hospital]")    
DSB = DLookup("[DSB]", "Units", "[Unit]=Forms!Data![Hospital]")    
DSC = DLookup("[DSC]", "Units", "[Unit]=Forms!Data![Hospital]")    
DSD = DLookup("[DSD]", "Units", "[Unit]=Forms!Data![Hospital]")

It lives in a Front End (multiple users, 2003 and eventually 2007), and "Units" lives in the Back End (Access 2003)

DSA to DSZ are used to populate the text on a form, which adjusts to the location. In essesence I have one form that acts as many and I can therefore from a central position change the value used for future.

Is there a quicker way that as the request for this information is read I can pull all the information over as a single hit, rather than 26 requests.

I have no server priledges and am basically confined to what i can do within the Access setup.

The usage is likely to be low so volume is not an issue, but for the few seconds this takes to be pulled, I have a user level with short attention spans and itchy hurry up fingers.


A very quick routine that copies the table from Back to Front, on each session would be considered, though I might not need the information for days

 

[xenou]

It's not easy to tell exactly how many "front ends" you have, and how they "share" this form.

The general principle is to try to pass filtered data when travelling across the wire, to reduce the amount of data. If you can populate a local table when the form is initialized that would of course run faster - perhaps set a "flag" value in your local table that indicates the date/time of the last "refresh", and only refresh once a day.

 

[mole999]

It's not easy to tell exactly how many "front ends" you have, and how they "share" this form.

The general principle is to try to pass filtered data when travelling across the wire, to reduce the amount of data. If you can populate a local table when the form is initialized that would of course run faster - perhaps set a "flag" value in your local table that indicates the date/time of the last "refresh", and only refresh once a day.

I have only seen 4 open occurrences at any one time (from the LDB), though more exist, currently all are 100Mb network connections (but much lower available bandwidth). The form is contained in each Front End, and BE is just for storing data and tables, and necessary tools for me to clean as necessary.

Occasionally I need to ask for the 26 pieces of data, and as I see what I have written, it looks like 26 requests, so I'm in essence after a way of making 1 request that can provide all 26 pieces in a single call, and I don't know how to do that. If I added another field to the master record that contained all the values, one after another, and pulled that single combined value, I'm not sure who to separate out all the bits, into the correct locations, some sort of string slice, variable length (mixed text and numbers(no calculations))

 

[xenou]

DSA = DLookup("[DSA]", "Units", "[Unit]=Forms!Data![Hospital]"   
DSB = DLookup("[DSB]", "Units", "[Unit]=Forms!Data![Hospital]"
DSC = DLookup("[DSC]", "Units", "[Unit]=Forms!Data![Hospital]"    
DSD = DLookup("[DSD]", "Units", "[Unit]=Forms!Data![Hospital]"

What do these functions return? Is it only a single result each time?

Note that I'm not at all certain we can ever gain "a few seconds" of performance - its not awfully slow to begin with and maybe that's just how it is.

 

[Norie]

Please don't tell me you have all these fields.

It sounds like a badly structured database.

I realise you might not have too much control over the data but I don't see why you couldn't manipulate the data to put it in a more conducive format.

It might take some time/effort but I think it might help in the long run.

 

[xenou]

The best I can come up with is:
1) on form initialize query the database for all possible values of units/codes and store these in a local recordset object
2) when the user selects a unit, use the seek method on your local recordset in memory
3) if using a recordset is too hard, you could do the same with a temporary local table in the front end databases - just populate the local table on the form initialization, then run your DSum's against the copy.

But overall I'd let your users get used to a few seconds of waiting and all will be well. My guess is that you'd trade your waiting time this way - just pushing it all up front for the initialization (but folks might be slightly more patient there).

Hopefully someone will have a better idea!

 

[mole999]

Please don't tell me you have all these fields.

It sounds like a badly structured database.

I realise you might not have too much control over the data but I don't see why you couldn't manipulate the data to put it in a more conducive format.

It might take some time/effort but I think it might help in the long run.

er....................

Hello, that's why i'm asking how to make this work quicker / better.

I won't tell you it's like that because it is, as i said above.

I hate to say it AGAIN Norrie, everytime someone asks for help often the answer is don't do it that way.

Well I'm asking so that I can learn and make it better

 

[mole999]

The best I can come up with is:
1) on form initialize query the database for all possible values of units/codes and store these in a local recordset object
2) when the user selects a unit, use the seek method on your local recordset in memory
3) if using a recordset is too hard, you could do the same with a temporary local table in the front end databases - just populate the local table on the form initialization, then run your DSum's against the copy.

But overall I'd let your users get used to a few seconds of waiting and all will be well. My guess is that you'd trade your waiting time this way - just pushing it all up front for the initialization (but folks might be slightly more patient there).

Hopefully someone will have a better idea!

Thank you. I was intending of making the front MDE. What I have currently works, even over a remote access VPN network, I will do some more work

 

[xenou]

No problem. I'm not sure how helpful I've been but I'd be willing to get my hands dirty a little more if you further ideas to bat around.