July 28, 2002 at 2:01 pm
I need to revamp our Access front-end/ SQL Server back-end apps since moving them to Terminal Server. This means converting former client-server apps into file-server apps. Help on the questions and input would be appreciated. The scenario is listed below:
What used to be client-server apps (each user having their own Access front-end on their client machine) has now been moved to Terminal Server. In other words, the .mdb files now sit on Term Server and
nobody has their own front end on their client machines. To get all
their databases running on Term Server as quick as possible with no reprogramming, I used automation to copy the Access mdb file into their session's temp folder (sort of creating an illusion of it being still a client-server app). (They access a Menu database, which checks permissions, then copies a chosen .mdb into their sessions' temp folder on Term Server). This is working fine for now, but for a company that will grow to thousands of users, I don't think it would be optimal, since disk space would be a primary concern with all the mdb's being copied on the Term Server. The reason I didn't turn some of the mdb's into multi-user databases is because they use local temp tables in
Access and/or load filtered linked tables from odbc on startup. I'd
like to convert these mdb's into multi-user front-ends but am not sure how what one user does will affect another. And bet it would require tons of reprogramming.
1) On term server, if a user opens an Access database (.mdb) and another user opens it, it's being shared. Therefore, filtered tables cannot be loaded on startup because every time a user connected, it would change the linked tables. Instead, recordsets and queries should be used to filter the records that the app runs processes on. Question: If User #1 run's a stored proc (dao or ado or Sql) or a query filtering records, and User #2 runs the same SP or query at the same time, would they conflict in an Access front-end with a SQL backend? Or do their instances of running it become user-specific? I've found very few articles about shared temp tables , recordsets, queries, etc and turning a client-server app into a file-server app.
2) Are local temp tables in an Access database unique to each user's
session? I don't think so, but could be wrong. I'm thinking that the
way to fix the use of local temp tables is to reprogram the database app to use SQL SERVER #temp tables instead of Access temp tables, through stored procedures. I believe the SQL #temp tables are unique to each user's session, but I've heard that connecting to sql local temp tables can be a nightmare in Access. Hence, the next question:
3)How should Access 2000 (.mdb) connect to a SQL #Temp table?
4) If User #1 in Access is running a process using a sql #temp table, and User#2 is running the same process, will it conflict? Or are they running their own sessions' process?
July 31, 2002 at 6:30 pm
Thats a lot to answer. Let me throw out a few things, maybe we can break the rest up into bite size pieces.
Temp tables in SQL are private to the session as long as they are declared using one # sign. Temp tables with two ##'s are global and can be visible across sessions. Note also that you dont have to worry about giving them special names, you can just use #temp, #temp2, etc - though for clarity of reading a more descriptive name is helpful. Internally SQL will give it a totally unique identifier so there are no conflicts.
Access should be able to connect to a temp table just like any other table.
For more on the Access side of things I'd recommend trying to find a Access-centric site. We'll try to help, just not our primary focus.
I have my doubts about the value of running an Access app on a terminal box. Access has fairly hefty run time requirements and you have to deal with the shared mdb issue (or change it so that the mdb would be put in a folder under their documents & settings folder so it would related to the user. Seems like not much more work to rewrite as a true client server app and drop Access all together. You've got the hard part done - figuring out what you need, the business logic, etc. You could do a fairly straight forward port in VB, perhaps leveraging triggers or stored procs a bit as you.
Hope that helps.
Andy
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply