Multiple users - temp table 2

[RichM1]

I am working in Access 2003 (ADP) with SQL Server 2000. Before upsizing to an ADP I used a local table on each user's front end mdb to perform some minor record changes. Question: In an ADP how do I create a temporary table and what effects would a temp table have regarding size. Also, how do I call or create a temp table - dim rs as new adodb.recordset?

thanks

 

[cmmrfrds]

Describe how you are going to use the temporary table. It is possible to create temp tables in sql server or create an in memory recordset with ADO. The best option depends on how you are going to use the data.

 

[RichM1]

What I'm trying to do is this. I have a number of users that are creating part entries in which a sequential serial number is generated. When a user creates a number of new serial numbers I immediately place these serial numbers into an inventory table so that another user doesn't use the same serial number. The serial numbers are also copied to a temporary local table. The user then modifies the local table and inserts data into a number of other fields. When done the data is updated to the inventory table. Now that I've switched the database to an ADP, local tables are no longer available. I was looking for a simple solution for a local table. The previous mdb was from access 97 and I used the local table idea to solve record locking issues. I know that with ADP I don't have many of the problems that I once had. I can solve the problem by changing a great deal of code but I figued there was something simple that got me around the local table that my front end mdb once had.

thanks

 

[cmmrfrds]

If you are putting the serial numbers into the inventory table, why can't you just requery the inventory table on those serial numbers and bind the Form to that query.

 

[jgoodman00]

I agree with cmmrfrds.

In addition to this, you can use the function SUSER_SNAME() to get a users NT login & by using this you can easily present different data to different users. This is also useful for creating audit trails by logging who & when any data is added/modified/deleted.

James Goodman MCSE, MCDBA

http://www.j.goodman.dsl.pipex.com

 

[RichM1]

cmmrfrds, I planned on using your concept but was just inquiring if a "local" or temp table existed in an ADP. A bit more work but it will work well. Thanks

 

[cmmrfrds]

You can make in memory recordsets, but that is a little different than a local table. Also, temp tables can be made in sql server. There are 2 types. One that is normally used and is scoped to the user and the other type can be scoped wider than the individual user. A table starting with # is to the user and starting with ## is wider scope. You can create an sql server temp table on the ADO connection and is available until you close the connection. For example, on the connection run some sql that creates the #yourtable then run more sql over the connection that read your temp table then close the connection.

 

[RichM1]

cmmrfrds, two questions"
1) How do I create a temp table on the server using ##?
2) What happens if multiple users try to use or create a temp table with the same name?

THANKS

 

[cmmrfrds]

That is the difference between the # and the ##. If it is a # temp then only the user that created it (in their scope) will see it. A ## temp can be seen by all users. I don't see a reason to use the ##.

Temp tables can be created by the regular SQL DDL statements or through a Select/Insert into SQL statement.

Create Table #mytemptable etc.........

or
Insert * into #mytemptable From
Select some data to insert

 

[RichM1]

Is there anyway to set the temp table as the recordsource of a form?

 

[cmmrfrds]

I haven't done that, but it may be possible. A bound Form has a recordset that is the result of the query or table in the rowsource. I imagine you would need some reference to setup the field names on the bound Form. You could probably intially bind it to an empty query that defined the field that the temp table would produce. At that point the Form would have a recordset object, then make an ADO query on the same connection as the Form connection and then retrieve the temp table into an ADO recordset and then Set the Form Recordset to the ADO recordset. That may work.