Access/SQLServer Requery Recordsource

[Stuart]

Hi all,
I'm working with a new client that upsized from A2K to SQL-S. Access is
using SQL-S as linked tables. Many of his forms retrieve all the records from
tables, via queries. Big time delay now that SQL-S is the backend.

All he ever works with is one record at a time. A combox exists with all the
itemno's for those records for the forms.

My thinking, is all I have to change for these forms, is to limit the
initial form load to one record, and requery when a different itemno is
picked from the combobox.

I've been trying for 2 hours to make this happen and I just can't figure it
out.

I know it's gotta be simple so someone please hit me upside the head with it
!!

thanks

 

[smk23]

Hi Stuart:
You can write pass-through queries to handle this. That type query is
"passed through" Access to SQL server and the query is thus a SQL query
within the backend and data passed back to Access. You have to be able to
write SQL without the GUI, however. Post back if you need help getting
started with pass-throughs.
Sam

 

[Stuart]

Hi Sam,

I tried p-through earlier. When I execute the query, I keep getting a prompt
for the SQL-S database over and over.

What am I doing wrong ?

Wouldn't the best solution be to only retrieve the record(set) I need ? If
p-though cuts down the access time, I'd be happy though !

thanks

 

[david epsom dot com dot au]

Which methods have you tried? Which method would you like to use?

Changing the recordsource/Applying a filter/Using the Open cmd/
Using code in the open event?

(david)

 

[Stuart]

Hi David,

I guess it would be Changing the recordsource. All I want to do is run the
query that originally brought up the form(s), and attach a "WHERE CustId =
nnn", when the user selects a customer id. After he is through with that
loaded CustID, he can pick another and then I'd requery again with the new
selected CustID.

BTW, SQLPass didn't work because I'm drawing from 2 different SQL databases.

thanks

 

[david epsom dot com dot au]

If the recordset is empty, the detail section of the form will not
be initialised. So if there is a chance that the recordset will be
empty, make sure you put the record selection controls in the header
of footer of the form.

You want the form to open with one record, so if you are going to
alter the recordsource SQL, you need to do it in the form Open
event or before the form Open event. That means you must select
or determine the initial record before the CBO is visible.

You might save the last record visited (in a table, or in the
registry or in the saved querydef) If you do this, you need to
be careful if there is any change that the recordset might be
empty (see above).

Assuming you decide to re-open at the last saved record, you
might decide save the SQL when you do the selection from
the CBO.

If you have the SQL saved as the recordsource of the form, that
means you have to put the form (and the database) into design mode
to save the SQL. Which means that instead you want to use
a saved querydef as the source for the form.

So the coding will take place in the update event of the CBO, where
you might have something like (air code)

sSQL = codedb.querydefs(me.recordsource).sql
if instr(ssql,"where") then
sSQL = left(ssql,instr(ssql,"where"))
endif
sSQL = sSQL & "where idx = " & me.cbo
codedb.querydefs(me.recordsource).sql = ssql
me.recordsource = me.recordsource
me.requery

Is this similar to what you have tried?
(david)

 

[Stuart]

This is what I want to accomplish, I just didn't know how to go about it
without tearing into these forms and doing a lot of VB. I knew there had to
be an easier way without rewriting these forms.

I won't get a chance to try it until later, but it looks good.

Thanks David !