[AccessD] Message not deliverable

Administrator administrator at it.glasgow.gov.uk
Wed Jan 29 03:50:33 CST 2003


*Yes it's cross Posted*
We all know how beneficial a Temp table can be and how cumbersome it can
also become when trying to either keep a separate session, OR a shared
session open and accurate.  In Access 2000 and Up Microsoft introduced ADP's
which directly hook into SQL Server (or MSDE).  One exciting thing about Sql
Server is that each session has a completely independent temp session from
other sessions, that is if Bob and Mary both log in and create a temp table
"myTemp" each will have a separate instance of "myTEMP" that is 100%
independent from the other.  When working within an ADP, (access 2000) you
get a series of 'in-non-ovations' that get the ADP to be 'too' smart for its
own good and you end up looking at your screen hoping to find where it's
neck is so you can ring it! :D.  None-the-less, workarounds are available.
One such that I've found is that if I create a full connection object and
keep it alive I can pass this object about my app and continue to use a temp
table created many transactions ago by a stored procedure.  While typically
you want to clean up after the use of a temp table, sometimes it is
necessary to keep one alive, that is, until the gui has completed the
transaction of a record.

One such workaround is to start with a Connection object declared at the top
of your form such as

Static Conn as ADODB.Connection

and somewhere in your code you'd introduce the connection string and open
it. and before the form closes you'd expire it (that is close it and set it
to nothing).

I have not had the opportunity to test this second scenario, but perhaps
someone on the list already has?  IF you were to instead of explicitly
opening your connection object through the traditional method of
Conn.connectionstring = "some connection info here"
Instead you would just set the Conn object to your
CurrentProject.Connection, would that effectively 'clone' the entire object
or just the current connection thread that was used at the time it was set?

Thus

Set Conn = CurrentProject.Connection

would be the same as simply opening a single threaded connection  such as

Conn.ConnectionString = "Your Connection Parameters"
Conn.Open

One criteria for this is that the object must remain in the same connection
session so that a temporary table created form a stored procedure where Conn
was used as the connection object would still be visible for manipulation
from consecutive executions through the same Conn object.  I think one major
draw back is that a bound subform would need to be bound manually such as

Conn.stp_MySproc, rs
set subfrmMYsubform.form.recordset = rs

traditional attempts to set the datasource such as
subFrmMySubForm.Form.DataSource = "Exec stp_MySproc"

don't seem to find the temp table, I think this is generally because of the
use of a 2nd thread thus a 2nd session thus the temp table would no longer
be visible.  I know that ADP's use 3 separate threads (connections) into the
Sql Server for performance optimization but for cases where you want to
manipulate temp tables, they are a PITA. :D


thoughts? comments? do I need to be more clear?



-Francisco
http://rcm.netfirms.com


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd



More information about the AccessD mailing list