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