Recommended? Access + DAO + SQLServer 2000 + ODBC

[Savvoulidis Iordanis]

I currently use a MDE + MDB access 2000 application (which uses the DAO
library only), through Internet VPN (1Mbit Download, 256 Upload). The
limitation is that all data/indexes must travel through the wire (access MDB
is file based) and be filtered on the client. 1 MBit is still slow for
Internet VPN.

1) Of course the performance sucks, so I decided to make a quick shift to an
ODBC connection to SQL Server for the data, but still use the MDE
application with no changes (or only a few). So now the tables are linked to
SQL Server database.

This way, do I avoid the transfer of all the table data/indexes, each time I
open a form, or not?

Also I use User Security Accounts for the MDE. How can I make use of SQL
Server logins when the MDE starts? Should I stop using User Security
Accounts?

2) Another idea that came on my mind is to use Windows Terminal Services, so
I keep the first MDE/MDB scenario.

Can Windows Terminal Services let users open multiple (but private for each
user) desktop sessions on the server, to run the MDE? Is it a safe option
for gaining speed ?

=================================
TIA and I'm looking forward to any replies

Savvoulidis Iordanis
Greece

 

[Rob Oldfield]

Can Windows Terminal Services let users open multiple (but private for
each

user) desktop sessions on the server, to run the MDE? Is it a safe option
for gaining speed ?

Yes. Running ANY kind of db via WAN/VPN is a horrible idea.

 

[John Nurick]

Yes. Running ANY kind of db via WAN/VPN is a horrible idea.

.... but running a client-server (e.g. Access+SQL Server or Access+MSDE)
is a perfectly practical one. See e.g.
http://groups.google.co.uk/groups?q="tom+ellison"+msde+bandwidth+
and
http://www.members.shaw.ca/AlbertKallal/Wan/Wans.html

 

[Guest]

If you have a well designed application, you do not transfer
table data/indexes.

If you have poorly designed application you do.

Most people prefer Windows Integrated Security
rather than SQL Server Accounts.

If you remove the SQL Server permission's from the
integrated logins, you will get a login screen when you
connect, which will enable you to use SQL Server logins.

Terminal Server works well.

(david)

 

[Savvoulidis Iordanis]

Thank you all. Probably I'll try the terminal server solution at first
(which has no app changes)

But anyway, if some of my queries, use the Now() function in the criteria,
how can this be used in Access MDE and ODBC connection to SQL Server? Are
there any changes that I should make to my queries or code, in the SQL
Server scenario?

 

[Rick Brandt]

Thank you all. Probably I'll try the terminal server solution at first (which
has no app changes)

But anyway, if some of my queries, use the Now() function in the criteria, how
can this be used in Access MDE and ODBC connection to SQL Server? Are there
any changes that I should make to my queries or code, in the SQL Server
scenario?

If you create Access queries against links to SQL Server tables then all of the
syntax stays the same. If you create Passthrough queries which are passed
directly to the server for processing then ALL of the syntax has to be SQL
Server's (T-SQL). For example Now() would be replaced with GetDate().

 

[david epsom dot com dot au]

Using Now() as a criteria, if you have a fairly simple query,
Now() should be evaluated by Jet/VBA, and the value sent to
SQL Server, which would then try to match that value (using
an index if available), and if you were lucky, return a
matching record.

If you were unlucky, you would fail, because time values
are stored differently in SQL Server.

Because time values are stored differently, you can't be
sure that time values will match exactly.

More complex queries (particularly those with Left or Right
Joins) may download enough of the records to do the Joins
locally, and criteria may be evaluated locally, but you still
will have trouble with Now(), because when you get a time
value back, it may not be exactly the same as a time value
you sent.

(david)

 

[Savvoulidis Iordanis]

Thank you all again.

After trying Terminal Server, I think it is the best way of continuing the
app usage.
THe only problem I have now, is printing on the remote printers.
The remote users access the server to run the app. So the printouts are sent
to the server
attached printer. Is there any way to send the printout to the remote user's
printer again?
(different printer for each user)

I've seen this scenario in many shops where the employees use a windows
terminal emulator
to access a remote UNIX app, but the receipt is printed in the same shop's
printer.

How is this done?

TIA

 

[Guest]

When I connect to a remote server using TS, the printouts
are sent to my local printer. ('installing' my local printers
on the server is done silently, but the printer driver does need
to have been installed once on the server, so that the files
are there). When I print, I print to the default printer set
in my login profile. Since TS has silently installed my printers,
this works.

As far as I know, this is the default setting for TS, and I am
surprised that TS is set up differently for you.

Of course, for this to work, the server needs to be able
to see the printer. If you have a printer attached to your
PC, you need to share the printer, so that the server
can print to it.

(david)

 

[Savvoulidis Iordanis]

I'll check it out.

Another question that just poped into my head is :

Since all the users run the same MDE file on the server remotely,
what happens if there are transactions started from the MDE?

As far as I know, any transaction started from the MDE to the MDB, changes
the size
of the MDE file (too nuts, but it's Access!). So for which user will this be
done, since there are
more than one concurrent users? Is this going to corrupt the MDE at any
time?
Also, is the MDE going to be currupted, if the connection to the Terminal
Server
is dropped?

TIA again...

 

[Rob Oldfield]

If you're using the standard rdp client, then there's a tick box there that
allows you to specify whether to connect locally installed printers.

You need to be careful installing additional printer drivers onto the TS
box - particularly any that, by default, have 'extra' features like popping
up a custom progress report. In fact I have seen newsgroup postings that
condemn it as a VERY bad idea.

One way around is by mapping printer drivers to a standard driver which is
already on the server (see www.printingsupport.com and the printer driver
matrix there). This can be a long process of trial and error, and isn't
guaranteed to work for all printers.

Another way that a collegue of mine did recently was to install the full
drivers (including progress reports etc) onto another machine accessible by
the server, shared it, and then double clicked on it from the TS box in
order to JUST install the bare minimum. That's working for us so far
without any dire consequences.

 

[Rob Oldfield]

I should add that that site is written very much from the Citrix
perspective, but it also applies to rdp connections.

 

[Guest]

Well, you should give each user their own MDE, just as you
should using any other system. You can put the MDE in the
user profile, or on the user share, so that each user has their
own copy.

Transactions work the same way on TS as on any file share.

Some people think sharing an MDE is likely to cause corruption.
I don't, but I use local temp tables that would be overwritten
if two users tried to do the same thing at the same time in the
front end MDE.

(david)

 

[Savvoulidis Iordanis]

Well, the checkbox for printers is by default checked in the Microsoft
Remote desktop, but still the print outs go to the server and not to the
client PC.

Are there any more adjustments to do on the server about it?

 

[Rob Oldfield]

That's the problem. If the driver isn't installed or mapped on the server
then the printer won't be autocreated. So either you install the driver
(with warnings as before) or you map the printers. That's done by editing
c:\windows\inf\printsubs.inf on the TS box. It's just a text file which
will look something like:

;printsubs.inf
;This file contains mappings for client driver to server driver printer
connections
[Printers]
;"Client printer driver name" = "Server printer driver name"
"Lexmark 810 Series" = "Lexmark Optra"
"hp deskjet 630c series" = "hp deskjet 550c"
"HP Color LaserJet 1500"="HP LaserJet Series II"
"Samsung ML-4500 Series"="hp deskjet 550c"

....which will map a client machine with a driver of Lexmark 810 Series to
the Lexmark Optra driver on the server. Suggestions on which driver to use
are in that matrix I mentioned before.

 

[Rob Oldfield]

And there's another complication if the local printer doesn't use the lpt1
port (which will be true for various print/scan/fax boxes - they can use a
custom port). There's an MS document on that which you might have to search
for if you run into that one.

If you go down the mapping route then you obviously have to talk the users
through how to find the *exact* printer driver. Even if you try installing
the driver on the server then you have to be careful that the drivers match.
A driver for Win2K on the client might have a different name than the 2003
driver on the server.

Following on from that, there's one key piece of stuff that you should have
to hand when you're trying to set this all up: a large bottle of hard
liquor.

That's the problem. If the driver isn't installed or mapped on the server
then the printer won't be autocreated. So either you install the driver
(with warnings as before) or you map the printers. That's done by editing
c:\windows\inf\printsubs.inf on the TS box. It's just a text file which
will look something like:

;printsubs.inf
;This file contains mappings for client driver to server driver printer
connections
[Printers]
;"Client printer driver name" = "Server printer driver name"
"Lexmark 810 Series" = "Lexmark Optra"
"hp deskjet 630c series" = "hp deskjet 550c"
"HP Color LaserJet 1500"="HP LaserJet Series II"
"Samsung ML-4500 Series"="hp deskjet 550c"

...which will map a client machine with a driver of Lexmark 810 Series to
the Lexmark Optra driver on the server. Suggestions on which driver to use
are in that matrix I mentioned before.

Well, the checkbox for printers is by default checked in the Microsoft
Remote desktop, but still the print outs go to the server and not to the
client PC.

Are there any more adjustments to do on the server about it?

 

[Savvoulidis Iordanis]

Thanks for the help.

PS. Already drank it...

 

[Nick X]

Hi,
We use a .bat (Batch) file to run updates on our mde files:

@ECHO OFF

IF NOT EXIST "H:\VLM-Archive\" md H:\VLM-Archive
'if the directory for the .mde file does not exist create it.
DIR /B \\server1\apps\pw-VLM-archive\VLM_Props-FE_XP_*.mde >
H:\windows\VLMArchiveVersion.txt
FOR /F %%I IN (H:\windows\VLMArchiveVersion.txt) do SET CurrentUtil=%%I
'look for the newest .mde file
IF NOT EXIST H:\VLM-Archive\%CurrentUtil% (
del H:\VLM-Archive\VLM_Props-FE_XP_*.mde
copy \\coddat1a\apps\PW-VLM-Archive\%CurrentUtil%
H:\VLM-Archive\%CurrentUtil%
)
'delete and replace .mde with newer version
DEL H:\windows\VLMArchiveVersion.txt

start H:\VLM-Archive\%CurrentUtil%
'start application

I do the updates to the front end and then make a new .mde with a different
version number:
VLM_Props-FE_XP_*.mde
VLM_Props-FE_XP_v2-1.mde

The batch file is just a text file with the .bat extension. We have it set
as the desktop shortcut with the application icon hiding the fact that is a
..bat file. It's like magic. Hope it works for you.

NickX