how to change a query to use a different table through code

  • Thread starter Thread starter DawnTreader
  • Start date Start date
D

DawnTreader

Hello All

as the title asks. i have a rather complicated situation, but it amounts to
i need to be able to, in code, change one of the tables that a query looks at
in order to properly get results from the query.

any and all help appreciated
 
Use the table name in a variable and reference the variable when building
your sql string


Dim strTable as String
Dim strSQL as string
strTable = "YourTableName"
strSQL = "SELECT * FROM " & strTable & " WHERE..."

hth
--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill
 
Hello Dymondjack

thanks for the response. it is exactly what i expected, but not what i am
looking for.

thinking about what i am attempting i realize that even my solution to the
problem wont work because the query is an object just like the table that i
am using.

i have an app where multiple users use the same front end, literally. only 5
users can be in at once, but the file that they are using is the same one. so
even if i do what i want, change the table name the query object uses, i will
also have to use a separate query for each user, which only compounds the
problem so that i have to alter a form, and if 2 users are using an instance
of the form, and if, and if...

dang. it might be simpler using thier profiles to store the file in.

thanks anyways for the help.
 
i have an app where multiple users use the same front end, literally.

There's the problem. I wish I had a dime for every person who thought it
was a good idea to share a front end, and then come here for problems because
of it.

Distributing seperate front ends to each user is easy and there's no reason
not to and every reason to.

Anyway, if you hardcode the sql string rather than saving it as a query,
it's more easily accomplished. There's no query that can be saved as an
object that can't be used as an sql string.


--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill
 
Hello again

this shared frontend is on a terminal server.

i have a new question, and i dont know if this is the right place, but the
question is this:

if i have 2 users in the frontend on the terminal server are they using the
same file or is there some special system for them to be using seperate
copies of that file in the terminal servers sessions memory?

if they are using copies then my problem isnt really a problem. but if they
input information to a temporary table local to the frontend and can
overwrite each other then i have a problem.

whats the answer?
 
Sorry if I sounded snotty in my last post, I guess it's a nerd-type pet peeve
of mine that people don't split their db's.

Unfortunately, I've never had to work with a terminal server and don't know
the rules but maybe this will help some.

If you have a limited number of on the server I think I would try storing a
seperate front end for each user. This will keep chances of corruption down
without multiple people using the same objects at the same time, and at that
point you can fairly easily manage a specific table in the query based on
which user is active for that frontend.

If I understand the issue correctly, what you are looking for is this: a
front end that is universal for each user, and the ability to base a query
off a certain table depending on which user is running the query.

If that's right, you can try doing something along these lines. You would
have to use a sql string in code to power the query, rather than a saved one
(at least I'm not sure how to use a dynamic table in the access query
builder). As far as I know, saved queries do have a slight advantage due to
the fact that access integrates a bit better with them (expression
service/jet plans and preloading maybe?). Anyway, I gather that they are a
little bit quicker than running a dynamic sql string.

But, using a coded string, you can determine which table to pull from based
on either the active user, if you have a user/login/out system setup, or
perhaps from the unique filename that each user will be using.

Personally, I use a user system that flags the active user when they log in,
and reads various settings in a users table. But if you are naming files
uniquely for placement on a terminal server, you could read the filename in
use and base the table from that. I'm not sure off the top of my head how to
get the active file name, but I'm almost positive I've seen posts on it
before. In that case, it could be something like

Dim strTable As String
Dim strSQL As String
Select Case fnGetActiveFileName()
Case "FE_UserA.mde"
strTable = "tblThisOne"
Case "FE_UserB.mde"
strTable = "tblThatOne"
Case Else
'Raise Error
End Select
strSQL = ......



Hopefully this covers some of what you were looking for an answer on.

--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill
 
Hello again

no appologies necessary. i know what you mean. :)

anyways the whole point is moot. i did a test. by logging in as 2 different
users to the terminal server and utilising the same system with in the
database that i was concerned about i have proved something to myself. each
terminal session creates a seperate memory instance of the front end. there
is no overlap. because each is connected to the same backend they all have
the same data. but any frontend temporary tables are seperate from any other
users that may be logged in to the terminal server and using the frontend.

i used the form that ran the queries that i was concerned about and found
that each of the 2 users i was logged in could use it and not "collide". so
all the work i was doing on creating a seperating user mechanizm was useless
and the only thing it did do was teach me how to do it. it was a good thing i
made a new copy of the frontend to do the new coding in, all i have to do is
delete the new file.

thanks for you input, it wasnt totaly invain, i learned stuff. thats what is
important. :D
 
anyways the whole point is moot. i did a test. by logging in as 2 different
users to the terminal server and utilising the same system with in the
database that i was concerned about i have proved something to myself. each
terminal session creates a seperate memory instance of the front end. there
is no overlap. because each is connected to the same backend they all have
the same data. but any frontend temporary tables are seperate from any other
users that may be logged in to the terminal server and using the frontend.

i used the form that ran the queries that i was concerned about and found
that each of the 2 users i was logged in could use it and not "collide". so
all the work i was doing on creating a seperating user mechanizm was useless
and the only thing it did do was teach me how to do it. it was a good thing i
made a new copy of the frontend to do the new coding in, all i have to do is
delete the new file.
Click to expand...

Hmm. Multiple people can use an Access front-end at the same time.
That's true on a physical box as well as Terminal Server. Yes, a
separate 'memory space' is created. But I'm not sure how each user
would run in a completely separate MDB file, unless you're saying that
TS physically copies the MDB when the user logs in?

If you make any persistent change to an object (like loading a local
work table or changing a querydef - just two common examples), then
the users will 'see' each others changes and therefore collide.

It's always better to have each user have their own copy of the
front-end application. It can improve performance, reduce the
likelihood of corruption, and avoid collisions as mentioned above. It
also enables you to deploy a new master copy of the application
without waiting for everyone to exit their copies.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Hello Armen

then i should have collided. i know exactly what the screen i was using to
test does. it uses a table to append records to after deleting what was there
before. in the test i did the table that is affected is first deleted, then a
list of records is added and then queried and the query is the source data
for a sub form the user sees. if terminal server didnt put the users access
frontend in to a "memory space" it would have done all that for the first
user and then when i used a second user login to go look at the same form, it
would have ruined the first users list of records in the sub form. but it
didnt.

i even used my filtering mechanizims to see if that would show a problem. it
didnt. in the end both of the user login instances showed the one record that
i filtered to, which was a different record for each user. there was no
appearant problem with them using the same frontend file. this leads me to
believe that the file is in a seperate memory space.
 
I really should just turn my head and walk away, but anyway...

You apparently seem to believe that because you ran a simple test on working
from the same db you will never have any issues with it, and you apparently
seem to believe that you know exactly what access is doing when you log into
the same db from two different computers. I can see where you might know
what your end of things are doing, but I have a hard time believing that you
know exactly what Access is doing with it. In fact, I don't believe that
there is any single person that knows every corner of how access works behind
the scenes.

So, you are willing to risk a fair number of things going wrong by using the
same db for different people, based on a one-off test where you simulated two
users using the same db at the same time. Keep in mind that any real
application testing is a rigorous task where every possible situation that
may arise is tested, and it is usually not possible to get every single
scenario tested, so we then need to make sure we are covered via other
methods. Such as using seperate frontends because it is nearly impossible to
test what will happen when a request is sent to access is recieved at the
same exact time, and the single instance of access is how trying to use the
same thing at the same time. This is one of many examples.

A good developer realizes that he/she can never test every single thing that
may ever go wrong, and bears it in mind when designing the application, to
make sure that what can not be tested can be avoided.

As I've mentioned before, this happens to be a pet peeve of mine. I think
it's amatuerish and unprofessional for any self-respecting developer to share
a front end. I do think it is forgivable for someone who is not aware of the
issues related to it, but this is generally a pretty basic concept that
anyone except a novice should practice.

Another pet peeve I have is people who come here for questions, and then try
and justify their methods because they did not get the answer they wanted.
Quite frankly, I don't care how many tests you do, it is not a good idea to
share a front end. This is not a difficult subject matter. I have advised
against it, Armen (an MVP) has advised against it, and you could easily find
online information for all the reasons why not, as well as hundreds of other
people who have advised against it (many MVP's as well).

I could be wrong here, but I don't think any competent developer ever said
"sure, its a good idea to share the front end". I have yet to see any
scenario where one would be better off using a shared front end.

Anyway, just because you ran a test on it, don't plan on it working all the
time. As long as you are sharing a front end, there is a possibility of
things going wrong, no matter how hard you try to avoid it. True, access
will do it, but not always. The only "true" solution is seperate files.

I shall add another $0.10 to my tally...

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
 
Back
Top