From: Jonathan on
Hi, using Access 2003 and SQL 2005. I have an Access application that uses a
combination of pass-through queries and dao recordsets to load forms and
reports. This is not an Access Project. My question is about design.

A search populates a table with parent record IDs. I then have a bunch of
stored procedures that use an inner join on this table to filter records. In
a multiuser environment what is the best practice method to ensure that the
table of IDs is not compromised by an user's search results? For example,
will using a ##temp table to hold the search result IDs give the required
outcome?

Any ideas or recommendations appreciated :-)

Many thanks,
Jonathan
From: Sylvain Lafontaine on
##temp tables are global temporary tables, so the possibility of having its
content getting compromised in a multiuser environement is very high.

You should either create a local temporary table #temp outside of any stored
procedures - otherwise it will be deleted at the end of the SP - or you
should use a permanent table but add to it some sort of id to separate the
result of each search. You can also add a time value and use it for say
deleting anything that is 48 hours old whatever the user is.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"Jonathan" <Jonathan(a)discussions.microsoft.com> wrote in message
news:D1C4DECF-A7E6-4C2E-BB85-A03F83B77B7A(a)microsoft.com...
> Hi, using Access 2003 and SQL 2005. I have an Access application that uses
> a
> combination of pass-through queries and dao recordsets to load forms and
> reports. This is not an Access Project. My question is about design.
>
> A search populates a table with parent record IDs. I then have a bunch of
> stored procedures that use an inner join on this table to filter records.
> In
> a multiuser environment what is the best practice method to ensure that
> the
> table of IDs is not compromised by an user's search results? For example,
> will using a ##temp table to hold the search result IDs give the required
> outcome?
>
> Any ideas or recommendations appreciated :-)
>
> Many thanks,
> Jonathan


From: Jonathan on
Thanks Sylvain.

Can you tell me how a local temporary table #temp is "linked" to the current
user? That is, does every process using the #temp table need to be using the
same connection?

Jonathan

"Sylvain Lafontaine" wrote:

> ##temp tables are global temporary tables, so the possibility of having its
> content getting compromised in a multiuser environement is very high.
>
> You should either create a local temporary table #temp outside of any stored
> procedures - otherwise it will be deleted at the end of the SP - or you
> should use a permanent table but add to it some sort of id to separate the
> result of each search. You can also add a time value and use it for say
> deleting anything that is 48 hours old whatever the user is.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Windows Live Platform
> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
> Independent consultant and remote programming for Access and SQL-Server
> (French)
>
>
> "Jonathan" <Jonathan(a)discussions.microsoft.com> wrote in message
> news:D1C4DECF-A7E6-4C2E-BB85-A03F83B77B7A(a)microsoft.com...
> > Hi, using Access 2003 and SQL 2005. I have an Access application that uses
> > a
> > combination of pass-through queries and dao recordsets to load forms and
> > reports. This is not an Access Project. My question is about design.
> >
> > A search populates a table with parent record IDs. I then have a bunch of
> > stored procedures that use an inner join on this table to filter records.
> > In
> > a multiuser environment what is the best practice method to ensure that
> > the
> > table of IDs is not compromised by an user's search results? For example,
> > will using a ##temp table to hold the search result IDs give the required
> > outcome?
> >
> > Any ideas or recommendations appreciated :-)
> >
> > Many thanks,
> > Jonathan
>
>
> .
>
From: Sylvain Lafontaine on
Temporary table #temp are not linked to the current user but to a single
connection and the fact that you using a MDB file with ODBC Linked tables
and passthrough queries complicate the matter; especially if connections
pooling is enabled.

I don't know how you are coding your stuff but unless you can directly
return the result through a � Select * from #temp � at the end of the batch,
I would suggest that you use a permanent table with an identifiant to
separate the multiple results from each other.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"Jonathan" <Jonathan(a)discussions.microsoft.com> wrote in message
news:8A40FBC7-7BF3-4EAD-9970-EB8212A6BE17(a)microsoft.com...
> Thanks Sylvain.
>
> Can you tell me how a local temporary table #temp is "linked" to the
> current
> user? That is, does every process using the #temp table need to be using
> the
> same connection?
>
> Jonathan
>
> "Sylvain Lafontaine" wrote:
>
>> ##temp tables are global temporary tables, so the possibility of having
>> its
>> content getting compromised in a multiuser environement is very high.
>>
>> You should either create a local temporary table #temp outside of any
>> stored
>> procedures - otherwise it will be deleted at the end of the SP - or you
>> should use a permanent table but add to it some sort of id to separate
>> the
>> result of each search. You can also add a time value and use it for say
>> deleting anything that is 48 hours old whatever the user is.
>>
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Windows Live Platform
>> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
>> Independent consultant and remote programming for Access and SQL-Server
>> (French)
>>
>>
>> "Jonathan" <Jonathan(a)discussions.microsoft.com> wrote in message
>> news:D1C4DECF-A7E6-4C2E-BB85-A03F83B77B7A(a)microsoft.com...
>> > Hi, using Access 2003 and SQL 2005. I have an Access application that
>> > uses
>> > a
>> > combination of pass-through queries and dao recordsets to load forms
>> > and
>> > reports. This is not an Access Project. My question is about design.
>> >
>> > A search populates a table with parent record IDs. I then have a bunch
>> > of
>> > stored procedures that use an inner join on this table to filter
>> > records.
>> > In
>> > a multiuser environment what is the best practice method to ensure that
>> > the
>> > table of IDs is not compromised by an user's search results? For
>> > example,
>> > will using a ##temp table to hold the search result IDs give the
>> > required
>> > outcome?
>> >
>> > Any ideas or recommendations appreciated :-)
>> >
>> > Many thanks,
>> > Jonathan
>>
>>
>> .
>>


From: Bob McClellan on
Jonathan,
One method is to use a staging table that is purged and repopulated
as per the end user's search criteria. add a column to this table:
[CurrentUser]
when you fire the sp...
first: delete from YourTable where CurrentUser = @cu
then, Insert into YourTable ( col1, col2, CurrentUser , etc.. )
select ( col1, col1, @cu, etc... )

hth,
...bob



"Jonathan" <Jonathan(a)discussions.microsoft.com> wrote in message
news:D1C4DECF-A7E6-4C2E-BB85-A03F83B77B7A(a)microsoft.com...
> Hi, using Access 2003 and SQL 2005. I have an Access application that uses
> a
> combination of pass-through queries and dao recordsets to load forms and
> reports. This is not an Access Project. My question is about design.
>
> A search populates a table with parent record IDs. I then have a bunch of
> stored procedures that use an inner join on this table to filter records.
> In
> a multiuser environment what is the best practice method to ensure that
> the
> table of IDs is not compromised by an user's search results? For example,
> will using a ##temp table to hold the search result IDs give the required
> outcome?
>
> Any ideas or recommendations appreciated :-)
>
> Many thanks,
> Jonathan

 |  Next  |  Last
Pages: 1 2
Prev: Multiple Items in Listbox
Next: Covering index