SQLServer #TEMP table for individual users linked to Access 2010
Hello
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks guys, if ok by you I leave this posted this week, maybe the folk over in SQL side of EE have an idea ?
ASKER
PS: one thing I am thinking about is to have SQL 'temp' table but not a true #temp one. The table still gets filled with the same data but I add another filed with the users SQL logon id (it is a Windows logon security ). Then on the Access side in the view I just show that user their records in that temp table, and they use those. The records will get dropped and added for that user, other user record are kept.
i will let you know how that goes later in week
i will let you know how that goes later in week
You could do that as well - whatever works best for your situation. SQL Temp tables are useful if the fulfill your needs, of course. In later versions there are also @table variables, which often serve the same purpose. However, those are very tightly scoped, so be sure to consider this if you look into @table variables.
ASKER
thanks guys, that saved me a bit of looking. Marked as 'partial complete solution' as I will have to find a workaround.
From what I'm aware, temp tables are only available within SQL itself. I don't know of any way to link to one from Access itself.
No points here please.
Jim.