Accessing Microsoft Access across a remote connection

[tompru1215]

Am working with a friend to use Access on a webserver and have laptops with
wireless cards access the Microsoft Access remotely. The database will be
used for service tickets for a small business. Assume that if Access is
installed on both the Server and the laptops, there is no issue with using
the pllication in this manner. It is really just remote accessibility.
Anyone have any experience with this type of setup?

 

[Tom Wickerath]

I don't have a lot of experience with this, but you will want to set up
Terminal Server, with a separate copy of the split FE application available
on the server for each user. Access MVP Albert Kallal discusses this topic in
his paper, available here:

Using a wan with ms-access? How fast, how far?
http://www.members.shaw.ca/AlbertKallal/Wan/Wans.html

I suppose you could publish the BE of a split application to SharePoint,
however, you then lose referential integrity, as each table is simply stored
as a list. In addition, I have heard that when lists get many records in them
(> 2000 records or so), that performance can really be an issue. Personally,
I'd go with the Terminal Server method in this case.

There is some discussion at Microsoft about computing in the cloud, but that
technology is currently in the planning stages at this point.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Arvin Meyer [MVP]]

I have quite a bit of experience with remote connectivity, and also
experience with air cards. I suggest that using an air card on a bound
connection can only be successfully done with a Terminal Server (RDP)
connection. Do not attempt to connect an Access front-end from your machine
to a remote data connection. Performance will be poor and there is a major
chance of corruption. Do not use an unsplit-database from the server, and do
not have multiple people connect to the same front-end on the server.

The only successful method is to split the front-end from the back-end, and
place a copy of the front-end in a separate folder for each user. I cannot
stress the importance of doing it that way, enough. By doing it that way you
will have a successful application. Any other method will likely cause
corruption eventually.

 

[tompru1215]

I am not familiar with RDP - I assume that is a standard and is not an ad-hoc
solution. I will research that. Also - you are suggesting putting a copy of
the front end in a seperate folder for each user. Are you implying a copy of
the database or a seperate copy of say an input table for each user?

 

[Tom Wickerath]

RDP stands for Remote Desktop Protocol:
http://encyclopedia2.thefreedictionary.com/Remote+Desktop+Protocol

http://en.wikipedia.org/wiki/Remote_Desktop_Protocol

Are you familiar with the concept of splitting an Access application into
two files: a front-end (FE) and a back-end (BE)? If not, here are some links
for you:

Split the database
http://www.access.qbuilt.com/html/gem_tips1.html#SplitDB
http://www.accessmvp.com/JConrad/accessjunkie/splitting.html

I believe what Arvin is saying is that a separate copy of the FE application
should be installed to each user's folder on the Terminal Server (assuming
this is a multiuser application). No two users should ever share the same
copy of the FE application.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Arvin Meyer [MVP]]

In addition to Tom's advice, you can play around with rdp, by either
creating a shortcut or doing:

Start >>> Run

and typing:

mstsc.exe

which will start a terminal server session. You will be allowed 2
connections unless you are using an actual terminal server. That may be
enough, or you may want to use a 3rd party RDP client and server, such as
winconnect:

http://www.thinsoftinc.com/support_winconxp.aspx

As Tom mentioned, do not use multiple users on a front-end. 1 user, 1
folder, 1 front-end.

Of course, all of those folders reside on the terminal server app, and the
front-ends can be connected to the same back-end for everyone, either on the
same server, or on a different one. Our typical setup is to have a dedicated
Terminal Server for all the front-ends, and a separate server for the
back-end, but that's because the back-end was also being shared by local
users on the LAN.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Tony Toews [MVP]]

Am working with a friend to use Access on a webserver and have laptops with
wireless cards access the Microsoft Access remotely. The database will be
used for service tickets for a small business. Assume that if Access is
installed on both the Server and the laptops, there is no issue with using
the pllication in this manner. It is really just remote accessibility.
Anyone have any experience with this type of setup?

One alternative might be to use SQL Server as your data storage
system. However you will almost certainly want to use a VPN over
which you move the data.

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/

 

[David W. Fenton]

Am working with a friend to use Access on a webserver and have
laptops with wireless cards access the Microsoft Access remotely.
The database will be used for service tickets for a small
business. Assume that if Access is installed on both the Server
and the laptops, there is no issue with using the pllication in
this manner. It is really just remote accessibility. Anyone have
any experience with this type of setup?

If you're talking about an Access front end to a Jet back end, it's
hopeless -- it will be too slow to work, and with wireless
connections, so unreliable as to frequently corrupt your back end.

The solution that is simplest is to host the app on Windows Terminal
Server.

Another would be Tony's suggestion to migrate the data to SQL
Server, but that will require re-engineering the front end in order
to get most of the benefit.

 

[tompru1215]

Thanks everybody for jumping into this thread and providing some feedback. I
read the links a number of times to help the understanding sink in a bit. So
- if I still want to proceed with this using Microsoft Access - I should
implement with split FE-BE database, and host on Microsoft Terminal Server.
Sounds like everyone believes this will work. Does this effect how to order
the product (webserver and 10 laptops)? I am not clear how splitting the
database plays into the deployment of this software onto 11 total machines.

Also - with SQL Server suggested as an alternative - is that more easily
implemented in this type of scenario? (10 laptops with wireless cards
remotely accessing the webserver...)

I guess my concern would be the reliability and success once we start
implementing the application and using the database, tables, and forms for
real business.......

 

[David W. Fenton]

So
- if I still want to proceed with this using Microsoft Access - I
should implement with split FE-BE database, and host on Microsoft
Terminal Server. Sounds like everyone believes this will work.
Does this effect how to order the product (webserver and 10
laptops)?

Web server? What does a web server have to do with it? Windows
Terminal Server has nothing at all to do with serving web pages.

 

[tompru1215]

The server for this will be located on a web hosting site and the laptops the
users will use will attach via Verizon wireless air cards remotely.

 

[David W. Fenton]

The server for this will be located on a web hosting site and the
laptops the users will use will attach via Verizon wireless air
cards remotely.

In that case, you really can't use Access at all.

 

[tompru1215]

Will SQL server work? Not familiar with that product - can you build Forms
in it like Access?

 

[David W. Fenton]

Will SQL server work? Not familiar with that product - can you
build Forms in it like Access?

SQL Server is only a datbase, i.e., a place to store data, and has
not tools for building a UI.

If you've purchased hosting on a web server, you've purchased
something that's not going to allow you to run your Access
application remotely. You need to have a file server and run Windows
Terminal Server on it. This is a completely different animal, and I
doubt you can buy "hosting" on such a machine. Instead, you'll need
to buy it and configure it yourself.

Maybe your company already has a Windows server that could be
provisioned to be a Windows Terminal Server.

 

[Tony Toews [MVP]]

Will SQL server work? Not familiar with that product - can you build Forms
in it like Access?

You can use Access forms against a SQL Server database on a remote
server. Your hosting company may not allow remote access to the SQL
Server database. Unless possibly via VPN and such.

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/

 

[David W. Fenton]

Will SQL server work? Not familiar with that product - can you
build Forms in it like Access?

No, SQL Server offers no user interface builder.

Access/Jet is not a web-enabled platform, so contemplating hosting
on a website is going to mean abandoning all the work you've done
and building something else entirely.

 

[Agile Consulting]

Am working with a friend to use Access on a webserver and have laptops with
wireless cards access the Microsoft Access remotely.  The database willbe
used for service tickets for a small business.  Assume that if Access is
installed on both the Server and the laptops, there is no issue with using
the pllication in this manner.  It is really just remote accessibility. 
Anyone have any experience with this type of setup?

hello please asl ms person

 

[Agile Consulting]

ADO ==> Active Data Source
RDO ==> Remote Data Source