Access as FE on SQL Server

[acss]

I have already created a working Access DB (2003) with a FE/BE design
currently being utilized over the corporate network using active directory.
Is there a benefit in upgrading to SQL Server Express 2005 and can it be
done by using the FE of Access?

 

[Arvin Meyer [MVP]]

I have already created a working Access DB (2003) with a FE/BE design
currently being utilized over the corporate network using active
directory.
Is there a benefit in upgrading to SQL Server Express 2005 and can it be
done by using the FE of Access?

There may be some benefits for security reasons, and SSE has a larger
maximum size ability and some additional datatypes. The disadvantage is it
will no longer be as portable, so you won't be able to take a copy of the
data BE home as easily. Access works great as a FE for SQL-Server, and you
have a choice of MDB or ADP. I prefer MDBs because they can connect to a
multitude of BEs, whereas ADPs only connect to SQL-Server.

 

[acss]

Thanks Arvin,
The current set up is the FE resides on the desktops of users located in
south and central America in which they enter invoice data into the BE which
resides in a net work folder. Eventually, i would like to use the corporate
intranet so entries can be done through a web page for more control and
remove the lag from vpn connectivity. Does the combination of Access FE and
SQL server express make sense for what i want to do?

 

[Rick Brandt]

Thanks Arvin,
The current set up is the FE resides on the desktops of users
located in south and central America in which they enter invoice data
into the BE which resides in a net work folder. Eventually, i would
like to use the corporate intranet so entries can be done through a
web page for more control and remove the lag from vpn connectivity.
Does the combination of Access FE and SQL server express make sense
for what i want to do?

If you have an Access FE now and want a web page as your front end, then
that would mean Access is no longer in the picture. The web page FE that
you would have to build to replace your Access FE could use either an MDB as
the back end or SQL Server, but the latter is more common and would handle
more traffic.

 

[Arvin Meyer [MVP]]

Depending upon how many concurrent users, I'd use Terminal Services. Either
Windows, WinConnect, or if you like to spend money, Citrix.

Your speeds will be roughly comparable through the VPN to what they would be
on a LAN. The FE are kept in separate folders, 1 for each user, at the main
network server.

Merely converting to SQL-Server will give you a little to none performance
gain unless you rewrite the application to use totally unbound forms and
very small datasets. For your situation, there are 2 solutions, rewrite the
FE as an asp application, or Terminal Services. A SQL-Server back end allows
more users, 24/7 operation, and some more security, although a VPN is
probably the main element in security.

The cheapest and easiest solution is Terminal Services if your situation
permits it.

BTW, it is way too easy top drop packets or corrupt a database using bound
forms over an Internet connection. I'd make your decisions quickly.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[acss]

One last note in that does not SQL Server Express include visual
studioexpress and though not familiar with the workings the application
implys a more seamless transition and control over the web. Could i not use
this as an option for users to make data entry?

 

[Arvin Meyer [MVP]]

I have no idea. I only use the full standard version of SQL-Server because
the apps I write that really require SQL-Server, generally would require it.
I don't do many web apps, and the ones I do were designed that way from
scratch. I have never even once found it necessary to move an app from
Access to the web, although sometimes a WAN is necessary for a new
requirement. In that case I either use a Terminal Server, or if there will
be numerous concurrent users, I'll write a web app front-end which is
connected to my back-end. Also, I suggest that you may want to look into
SQL-Server compact, which includes more tools than SQL-Server Express, and
is also free.

I am not sure if the licensing of either of them permits using on a web
server. You'll need to check that.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Tony Toews [MVP]]

Merely converting to SQL-Server will give you a little to none performance
gain

If acss is currently using FEs widely distributed throughout Central
and South America then switching to SQL Server could give substantial
performance improvement.

And he'd be at much less risk of corruption.

For your situation, there are 2 solutions, rewrite the
FE as an asp application, or Terminal Services.

Not quite. There could be lots of other solutions.

The cheapest and easiest solution is Terminal Services if your situation
permits it.

Not necessarily. The cheapest solution, disregarding labour and time,
might be going to SQL Server. Although TS is definitely easier.

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/

 

[Arvin Meyer [MVP]]

Not quite. There could be lots of other solutions.


Not necessarily. The cheapest solution, disregarding labour and time,
might be going to SQL Server. Although TS is definitely easier.

I was talking about the total cost, you can't simply change a bound
front-end to a JET backend to a bound SQL-backend and get a performance
improvement. You would need to rewrite the front-end and backend to do that,
and that most definitely wouldn't be cheaper. Labor/time is definitely a
consideration since it will be the major factor in almost any change.

 

[Tony Toews [MVP]]

I was talking about the total cost, you can't simply change a bound
front-end to a JET backend to a bound SQL-backend and get a performance
improvement. You would need to rewrite the front-end and backend to do that,
and that most definitely wouldn't be cheaper. Labor/time is definitely a
consideration since it will be the major factor in almost any change.

Yes but. <smile> In the situation where an employee might have to
do a lot of bureaucratic BS to get a TS system setup and get budgets
approved, etc, it might be easier for that employee to move the system
to SQL Server.

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/