Dynamic Query Sets as RecordSource

[BobMcClellan]

Hello.....
I continue bumping into the same problem and building
work-arounds. I know there has to be an easier answer.....

Front End... Acces2k.adp file
Back End.. SQL 2k database....

I have forms with event procedures that feed a customer number
or a unit number or some other param as an input var to a stored procedure.

This sp then drops the target table then rebuilds it.
the target table is the underlying rec set for a subForm.

Problem:
in our multi-user environment, Unless I prefix the targetTable with the
CurrentUserName and then work it so the subForm uses that table for
RecordSource... people bump into each other...

This solution results in too many unNeeded redundant tables.
I'm sure there has to be a better way....

I know that a tempTable or a Stored Proc running as a query would do the
trick but
not when using them as the Recordsource for a form.


Any help with this problem is much appreciated.....
thanks in advance,
bobmcclellan

ps: I know this was in the Programming NG yesterday but since there were no
replies I posted here.....
Hope someone can help.....

 

[Grayson Ferrantex]

Hi Bob,
I'd add a simple key to the table you are rebuilding: lngBuildID. Then
create a new permanent table tblBuild or more accurately tblCurrentBuild.
The current build has only a single ID in it and this single ID is joined to
select the current records. Then there won't be any contention: you can
enter new records as you build and other users will still have the old build
ID. When done building just change the single ID in tblCurrentBuild and
everyone will suddenly get the new table. Then at your leisure you can
cleanup the old records.
Hope this inspires more ideas,
Gray

 

[Rick Phillips]

Hi Bob see the Stored procedures I created below and how I pass parameters
for Forms.

When running reports in ADP and you are passing parameters you will be using
Stored Procedures.

Reference to passing parameters will be referenced in the Input Parameters
field under the Report properties.

Reference to passing parameters for forms you will be using VBA for Forms.

Report::::
Example of a Stored Procedure that I created

Alter PROCEDURE [Booth Procedure]
@Select_Show int
As
SELECT shows.s_name, contracts.con_booth_assigned,
contracts.con_booth_sqft, exhibitors.e_name, shows.s_facility,
convert( int, rtrim(contracts.con_booth_assigned)) as BOOTH
FROM contracts INNER JOIN
exhibitors ON contracts.e_id = exhibitors.e_id INNER JOIN
shows ON contracts.s_id = shows.s_id
WHERE dbo.contracts.s_id = @Select_Show

Report (Main) Properties

Input Parameters @Select_Show = Forms![frm_SelectShowDialog]![SelectShow]

Form using a List Box

VBA Code:

Private Sub Last_Click()
' --- This resets the query for the last names ----
Dim FoundLastName As String

FoundLastName = Forms![frm_record_find].FindLastName
FoundLastName = "%" + FoundLastName + "%"

Me.List19.RowSource = "Exec FindLastName @LName = ' " &
FoundLastName & " ' "

Me.Repaint ' Save data for query
Me.Refresh ' Requery drop down listing
End Sub

Stored Procedure used:

Alter Procedure FindLastName
@LName as varchar(30)
As
SELECT DISTINCT
buyers.store_id as StoreID, buyers.buy_lname as Last,
buyers.buy_fname as First, stores.store_name as Store
FROM buyers INNER JOIN
stores ON buyers.store_id = stores.store_id
WHERE (buyers.buy_lname LIKE @LName)
ORDER BY buyers.buy_lname
return

Rick Phillips