Temporary Table Problem

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I am migrating an Access mdb which has the tables in one mdb on our
server and the mdb front end installed on each workstation with the
tables on the server linked to an adp with a sql server 2000 backend.
However, with the mdb I have used a local table to temporarily hold a
list to enable users in this multiuser environment to select items to
insert records into one of the linked tables. I thought I had the
solution when I read up about temporary tables (#table), which seemed
to offer the opportunity to create a unique table for each user.
However, although I have been able to create and view the contents of
my temporary table in Query Analyser, I do not seem to be able to
create a stored procedure to make this table the record source of my
adp form. In addition, does the temporary table last all the time the
user has the adp open, disappearing only when the adp is closed? I need
to be able to present a fresh record set to the use each time the form
is entered so that he/she can run down the items in the continuous form
ticking off the items they want to add to the normal table, so with
temporary tables I would need to drop and recreate the same #table for
that particular user each time the form is opened during the same
session. Can all this be done with temporary tables in an adp? Or is
there some better way of achieving my goal?
 
yes; you're on the right track.. give us more information.

the #table should dissappear when the

in general; i would use a real table and not a temp table.. and filter
for @@SPID (set @@SPID equal to the default for a smallint column
'SPID'
and then 'delete from myNonTempTable where SPID = @@SPID'

this will act almost like a temp table in access-- it might have
performance problems if you had 200 people pushing records into this
table; but if you index it correctly it should work well

-aaron
 
[email protected] said:
yes; you're on the right track.. give us more information.

the #table should dissappear when the

in general; i would use a real table and not a temp table.. and
filter for @@SPID (set @@SPID equal to the default for a
smallint column 'SPID'
and then 'delete from myNonTempTable where SPID = @@SPID'

this will act almost like a temp table in access-- it might have
performance problems if you had 200 people pushing records into
this table; but if you index it correctly it should work well
Click to expand...

That's almost exactly what I would have recommended. Almost only
because I would rather use the UserName or UserName+HostName to
identify records belonging to one specific user. One instance of an
Access-App may use several different connections to SQL-Server, so
there may be more than one SPID involved.

Cheers
Phil
 
Thanks Aaron, it looked like a bit of the message was missing? But I
take yours and Philipp's advice on using a non temp table. I will try
it out!!

Mike Hoar
 
No, you probably cannot do that.

Temporary tables are strongly associated to a connection and not only ADP
use three connections to connect to a sql-server (one for displaying the
list of tables/queries/views, the others to perform data operations) but
these connections are also associated with a database and the change of
database (required for a temporary table because they are stored in the
tempdb database, not the user database) will require the closing/reopening
of one of these connections.

Use the suggestions of the other posts instead.
 
I dont know why ADP would have multiple connections open.

out of the box experience is one connection per open ADP right?
 
Sylvain

i need to know more about what you're talking about.

an ADP uses 3 connections; but one is the ADPs' connection; and the
other 2 are just open when you first open it or hit F5? how does that
work

also; if you're using NT authentication; the connection #2 and
connection #3 might be using connection sharing?

I just haven't ever heard anything like that; and I htnk that MS needs
to be more forthcoming with this type of information (in order to make
ADP functional from a developers perspective)

please give me reams and reams of information about
a) what specifically you mean
b) who you learned it from
c) if you know other poeple that take ADP seriously. I am a member of
the ADP Nationalists; and I am quite militant in the fact that MS is
screwing the pooch. And I have never met anyone with 1/2 of the
interest in ADP that I have.

And I feel like I just came off of a desert island and finally there is
a 2nd person in the whold world
you know what i mean?

I'd just love to get more information about ADP from you.. just in
general; do you know more websites that are ADP-specific?

-aaron
 
[email protected] said:
I dont know why ADP would have multiple connections open.
Click to expand...

I don't know it either, but I guess the intention was to
parallelize (does this word exist?) several operations.
It usually happens when opening a form/report with one
or more subforms/subreports.
out of the box experience is one connection per open ADP right?
Click to expand...

No. The described behavior is out of the box. AFAIK you
can't change that.

Best wishes
Phil
 
[email protected] said:
i need to know more about what you're talking about.
Click to expand...

This behavior is only scarcely documented by offical
sources (e.g. Online Help, MSKB). If you want to know
more about it, it may be a good idea to start up a
Profiler session and then run you favorite ADP-App.

Cheers
Phil
 
Back
Top