Access 2K3 and Terminal Server

[reno]

1st post, forgive the length. Hope I'm in the right group. Scenario: One
W2K3 R2 SP2 w/ TS enabled, residing in a workgroup (no AD), 4 remote desktop
users on per device licensing using XP Pro SP2, all using the same user name
login. Simple AK3 MDB (not Split), w/ linked tables, append/delete queries,
switchboard and minor macros. I want to enable one or all remote users
session access to this MDB. AK3 is installed on remote desktops.

1. Must AK3 reside on TS Server. If so . . . licensing req's.
2. Must AK3 MDB reside on TS Server.
3. Pros and cons of splitting MDB, preferable anyway?
4. Linked tables reside on W2K file server w/in the workgroup. Used in
conjunction
with queries to delete/append tables in MDB. Remote users will be
deleting/appending 30K+ records several times a day. Will this work or
work
well from within a TS session? Are there latency issues and risk of
corrupting
linked tables?

Newbie to TS Server and multi-user office apps. Suggestions, links, answers
greatly appreciated.

 

[Tony Toews [MVP]]

Simple AK3 MDB (not Split), w/ linked tables,

Just curious. How can you have a MDB that is not split and yet it uses
linked tables?

append/delete queries,
switchboard and minor macros. I want to enable one or all remote users
session access to this MDB. AK3 is installed on remote desktops.

1. Must AK3 reside on TS Server. If so . . . licensing req's.

Yes. Licensing I would assume so but I have no idea.

2. Must AK3 MDB reside on TS Server.

No, but for best performance it should. And each user should get
their own copy. I did have a client that chose to keep the copy of
the FE on the file server and that did work.

3. Pros and cons of splitting MDB, preferable anyway?

No real cons. Many pros.

See the "Splitting your app into a front end and back end Tips" page
at http://www.granite.ab.ca/access/splitapp/ for more info. See the
Auto FE Updater downloads page
http://www.granite.ab.ca/access/autofe.htm to make this relatively
painless.. The utility also supports Terminal Server/Citrix quite
nicely.

4. Linked tables reside on W2K file server w/in the workgroup. Used in
conjunction
with queries to delete/append tables in MDB. Remote users will be
deleting/appending 30K+ records several times a day. Will this work or
work
well from within a TS session? Are there latency issues and risk of
corrupting
linked tables?

So long as the TS users, local users and Win 2000 file server are on
the same high speed LAN that will be fine.

Now deleting/appending 30K records per day is quite high. Just
curious. What kind of data?

Newbie to TS Server and multi-user office apps. Suggestions, links, answers
greatly appreciated.

My random thoughts on Microsoft Access, Terminal Server and/or Citrix
http://www.granite.ab.ca/access/terminalserver.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[reno]

Thank you for the speedy response Tony. I will check out the links shortly.
You asked: "Just curious. How can you have a MDB that is not split and yet it
uses
linked tables?" Answer: Might be my terminolgy. What I meant was that I
haven't used the database splitter to create a FE and BE, but have taken some
initial steps in that direction. MDB is still in testing phase. You asked:
"Now deleting/appending 30K records per day is quite high. Just curious.
What kind of data?" Answer: I am using a MDB table as my source for printing
barcode labels. As inventory info changes frequently (ie, new P/N's,
descriptions, price changes, etc.), I use the linked tables (in my inventory
software) to update my MDB table from which I print barcodes. Clear as mud?
Again, thanks. You affirmed what i was thinking, but wasn't sure. I will
update post as neccessary if needed.

 

[Tony Toews [MVP]]

Thank you for the speedy response Tony. I will check out the links shortly.
You asked: "Just curious. How can you have a MDB that is not split and yet it
uses
linked tables?" Answer: Might be my terminolgy. What I meant was that I
haven't used the database splitter to create a FE and BE, but have taken some
initial steps in that direction. MDB is still in testing phase. You asked:
"Now deleting/appending 30K records per day is quite high. Just curious.
What kind of data?" Answer: I am using a MDB table as my source for printing
barcode labels. As inventory info changes frequently (ie, new P/N's,
descriptions, price changes, etc.), I use the linked tables (in my inventory
software) to update my MDB table from which I print barcodes.

Ok, so almost all tables are linked but the working table for printing
bar codes is local? This will lead to significant needless bloating
in the FE so I'd suggest using a temporary MDB.

The TempTables.MDB illustrates how to use a temporary MDB in your
Microsoft Access app.
http://www.granite.ab.ca/access/temptables.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[reno]

Ok, so almost all tables are linked but the working table for printing
bar codes is local? This will lead to significant needless bloating
in the FE so I'd suggest using a temporary MDB.

Thanks for the link Tony. I'll put it to consideration. Just wondering, at
what point does a MDB become bloated (and is there a pill for that <smile>).
As I stated, this is an extremely simple database. One linked table, one
local table, two queries, two macros and a switchboard. It takes 20 seconds
to run my queries locally. Not tested yet in a TS session. The MDB has not
been compiled, macros not converted to VB code, just raw database at this
point. I should mention that I do not print barcodes directly from the MDB.
The barcode app just references the local table in the MDB. One further
question: As my linked table resides in a critical app, I'm concerned about
possible corruption occurring during the TS session. Is there a high risk of
that in your opinion, and how can I minimize it. And thanks for sticking
with me as I get my feet wet.

 

[Tony Toews [MVP]]

Thanks for the link Tony. I'll put it to consideration. Just wondering, at
what point does a MDB become bloated (and is there a pill for that <smile>).

As my linked table resides in a critical app, I'm concerned about
possible corruption occurring during the TS session. Is there a high risk of
that in your opinion, and how can I minimize it.

So long as your Terminal Server system is in the same LAN as the file
server which contains the BE MDB file I'd say no different than any
other PC on the same network. If the TS server was in another
building across the city or a different province/state then you'd have
a lot of troubles.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[reno]

Thanks for all your help, advice, suggestions Tony. Haven't finalized
implementation just yet, but you've pointed me in the right direction.
Consider this post closed.

 

[David W. Fenton]

So long as your Terminal Server system is in the same LAN as the
file server which contains the BE MDB file I'd say no different
than any other PC on the same network. If the TS server was in
another building across the city or a different province/state
then you'd have a lot of troubles.

Depends on the bandwidth. One of my clients (an office of a very
wealthy school district in NJ) has a gigabit WAN through the whole
school system, connecting all the buildings (that's what the money
on your phone bills goes to, i.e., eRate funding for schools). There
are no problems at all running their linked Access applications
between buildings on that WAN.

But I've seen very few such installations.

They may become more common in the next few years, though.

 

[Tony Toews [MVP]]

Depends on the bandwidth. One of my clients (an office of a very
wealthy school district in NJ) has a gigabit WAN through the whole
school system, connecting all the buildings (that's what the money
on your phone bills goes to, i.e., eRate funding for schools). There
are no problems at all running their linked Access applications
between buildings on that WAN.

But I've seen very few such installations.

Very interesting. And I'd agree with the very few such. But we'll
have to start putting in weasel words now won't we? <smile>

Any idea how come that's such a rich school district.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[patti]

Tony-

What kind of problems would result from having access on wts and db on
another server? I am using ts to schedule & call a batch file on the same ts
to run some vba macros in that non-ts- based database. Otherwise, the db is
opened locally by individual users.

 

[Tony Toews [MVP]]

What kind of problems would result from having access on wts and db on
another server? I am using ts to schedule & call a batch file on the same ts
to run some vba macros in that non-ts- based database. Otherwise, the db is
opened locally by individual users.

Works fine. I have clients running that scenario quite well for
years.

One of the key things though, and this doesn't necessarily apply to
you right now, is that each user on a TS system must get their own
copy of the FE.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[patti]

Tont-

Thanks for the speedy reply and for sharing your expertise.

patti

 

[David W. Fenton]

Very interesting. And I'd agree with the very few such. But
we'll have to start putting in weasel words now won't we? <smile>

Any idea how come that's such a rich school district.

It's a very rich part of New Jersey, to start with, but many schools
have capitalized on the eRate funding available. I didn't know
anything about it until I worked for a company that runs charter
schools and had to manage the eRate funding application process for
5 schools over a period of 3 months. Basically, eRate will pick up
something like 90% of your cost on purchasing an Internet service,
as well as more than half of the cost of equipment like routers and
switches. For a while they wouldn't fund firewalls, but that changed
with the 2005 application year.

Basically, if it's part of their Internet infrastructure, schools
can get lots of funding. I have always assumed that the fiber
network that this school uses was largely financed with eRate funds.

 

[patti]

Hi Tony-

The network dude said this can't be done. (Access & batch on ts, db (& its
vba macros) on non-ts) The batch opens access and then everything stops. I
guess the macros never start. Could you guide me to some trouble-shooting
sites or forums? I'm not even sure where to look for possible causes or
solutions.

Thanks for any help.

 

[Tony Toews [MVP]]

(Access & batch on ts, db (& its
vba macros) on non-ts) The batch opens access and then everything stops. I
guess the macros never start.

So what happens when you run Access on the TS? Do you get some macro
type messages then? Fix those up, using the same account that you
will run the batch process on TS under and you should be fine.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[patti]

Thanks Tony-

The batch file opens the db but then just sits there. No macro message.
Nothing happens.

Here's my batch which resides on the ts.

"\\mss\public\PhlImportDB.mdb" /x mcrPhlImportAll

I will try your code for windows script on your website kater today.

Thank you very much for sharing your knowledge.

patti

patti

 

[Tony Toews [MVP]]

The batch file opens the db but then just sits there. No macro message.
Nothing happens.

But what happens when you run Access itself not via the batch file?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[patti]

Access opens fine. I can open the non-ts db fine. Macros run.