SQLServer #TEMP table for individual users linked to Access 2010
Posted on 2011-10-26
I have an Access 2010 routine that :
- copies from one of my main SQLServer tables all records to a 'local user' MDB (on the local users's C drive) all the records, is a temp MDB. It copies about 800 records , the table has about 20 columns, so is relatively fast copy procedure
- with that local temp MDBs table linked to the front end a user chooses, via an Access form, some or all of those records in that linked temp table, i.e. one of the fields is a YesNo field.
- from there I use some VBA and read that list of records that linked temp table and if a record is marked Yes I do something with that record
But I am wondering instead of using a temp MDB on the users C drive, can I somehow use a SQL Server #TEMP table and link that to the Access Front End (table ? view? USP ? ) and if so is that linked #TEMP table unique to an individual user/logon ? i.e. someone's list of YesNo records in 'their' #TEMP is not going to be overridden with someone elses list in 'their' #TEMP ? The #TEMP SQLServer table would be linked to Access 2010 and used and viewed in a form and used as a recordset (list) in a VBA procedure
the FrontEnd interface is on a terminal server and all users log on to that and fire up the interface and use it on the TS.
thanks, a bit vague but not quite sure how to ask the quesiton on SQL #temp table, unsure of their uniqueness and duration and link-ability to Acc2010.
I have SQLServer v2008, DevEdition