Using Visual Basic Net as front end - Access as back end

[CAM]

Hello,

I am wondering if someone has come across this before. Currently I am using
Access 2002 I developed an inventory tracking database, which this database
is used in California and in Florida. Both Inventory centers
(California/Florida) have their own database spited (Front-end - queries,
forms, macros, etc. and back end - tables only) in their respective state.
I originally wanted to have the backend located in the California inventory
center, which both California and Florida have access to. Unfortunately, I
could not have just one backend database it took Florida about 40 to 50
seconds or longer to open up a form and about 2 minutes or more to open up
a report, which the backend was in California. Now I have the California
inventory center front end and the backend and Florida has their own
front-end and backend all this is due to slow performance over our WAN
across the country. About 5 years ago I developed a database using Visual
Basic 6 as the front end and Crystal Report as the report writer and Access
(tables and queries) as the backend. It worked great and fast, but this
database was used only in one location in our WAN not over the other side of
the United States. My thoughts are using Visual Basic Net as the front end,
Crystal Reports as the Report Writer and Access as the backend (tables,
queries). I would like to use this combination by having the backend in
California (Access), which both California and Florida have access to. I
want to try to retain saving all of my tables and queries in Access if
possible. Will this work? Can Florida access the database without any
major delays or corruptation problems. Of course each users PC's will
retain their own Visual Basic net Front end to speed up the database. Their
will be about 25 users per inventory center. Or should I get Microsoft Sql
server and use Visual Basic Net. We are on a budget like most IT shops and
I thought using the combination of Visual Basic net, Crystal Reports, Access
will be the least expensive way to go. Can someone give me their input. I
like to have much input as I can get before I can do any major undertaking.
Thank you in advance.

 

[Brendan Reynolds]

Here are some resources you might want to investigate.

Access and JET are a great combination, but if you decide to use .NET rather
than Access on the front-end, then I'm not convinced that there is any great
advantage in sticking with JET on the back-end in the scenario you describe.
You might want to check out SQL 2005 Express Edition ...

http://www.microsoft.com/downloads/details.aspx?FamilyID=4c6ba9fd-319a-4887-bc75-3b02b5e48a40

Also see Albert Kallal's article on Access and WANs ...

http://www.members.shaw.ca/AlbertKallal/Wan/Wans.html

And don't be in too much of a hurry to decide on Crystal as your reporting
solution. There's also SQL Server Reporting Services (see first link above),
ActiveReports (http://www.datadynamics.com/default.aspx) and XtraReports
(http://www.devexpress.com/).

 

[Arvin Meyer [MVP]]

You have several good choices. If you are using .NET, use it with ADO.NET
and IIS and access the database with a web server. The method I prefer is to
use a Terminal Server and an Access front-end, because even very expensive
hardware on a sophisticated application will cost less than building the
application with a non-Access front-end. Since it typically takes 3 to 6
times as long to build a database application in VB or in .NET than in
Access. A really good Server with both Hardware and software will cost less
than $6,000. So consider the ROI, when making decisions.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Steve Schapel]

Cam,

In addition to the information given by Brendan and Arvin, you might
also consider sticking with your existing Access application, running in
one of your two Centers, and allowing the other to access it remotely
using
http://www.thinsoftinc.com/product_thin_client_winconnect_server_xp.html
I'm pretty sure that's what I would opt for.

 

[Albert D. Kallal]

Use vb.net, or hand coding the system in c++, or assembler will not speed up
this accessing of the back end ms-access file.

The problem of performance is not ms-access, but that of the WAN.

A VERY good choice here would be to use remote desktop (terminal services,
as then
you don't have to re-write the software).

I explain and talk about using a WAN with ms-access here:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

Regardless, using vb.net is not going to speed up your network here. And,
using sql server for the back end is a very good choice also...especially
since there is several free versions now...

 

[CAM]

Hello,

I want to thank all of you professional for your input it helped alot.
Anybody else who wants to give further input, please do. Thank you I
appreciated all input.

 

[Graham Mandeno]

Hi Cam

I can strongly recommend WinConnect Server XP from
http://www.thinsoftinc.com, as Steve mentioned.

I have clients using this for remote access to Access applications, often
two or more users sharing the same XP workstation on the office network.

 

[CAM]

Thanks Graham

Hi Cam

I can strongly recommend WinConnect Server XP from
http://www.thinsoftinc.com, as Steve mentioned.

I have clients using this for remote access to Access applications, often
two or more users sharing the same XP workstation on the office network.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello,

I want to thank all of you professional for your input it helped alot.
Anybody else who wants to give further input, please do. Thank you I
appreciated all input.

 

[CAM]

Steve/Graham

Can I have my back end database in California and have both Florida and
California users access to it.? California can access quickly, but Florida
it is slow. Also, won't I have the same problem of how slow the performance
in getting or opening a form for the user in Florida who is trying to access
the back end in California using WinConnect Server XP? Previously I had to
abort the back end access in California for the Florida users. We do use XP
professional as our operating system. I don't know too much about this
product and how it can help me. I appreciate if you can give me just a
little more information from a developers view. Thanks I appreciate the
help.

Regards,

 

[Steve Schapel]

Cam,

The difference would be that the Florida user would have their copy of
the frontend application residing on the California computer. They are
running that database via a remote connection, similar idea to Terminal
Server. That means all the processing of data takes place there, i.e.
the same place where the backend is located. Therefore it will be just
as quick for them as it is for the California user. The only traffic
going through the network/internet between California and Florida will
be RDP traffic, i.e. mouse clicks, keystrokes, screen images, which is
insignificant compared to data traffic. It was the data traffic that
was making your previous attemps so slow.

 

[Graham Mandeno]

Hi Cam

Both the back-end AND the front-end will be in California. The only thing
that comes down the line is the picture of the screen, using Remote Desktop
Protocol (RDP).

You can actually have two users on the same Windows XP machine at the same
time, one sitting in front of the screen in CA and one viewing a Remote
Desktop Connection (RDC) in FL.

As in any multi-user situation, it is better for them to have their own
copies of the front-end. For this reason, I usually install the frontend in
a subfolder of %AppData%, such as:
C:\Documents and Settings\<username>\MyApp\MyDatabase.mde

The shortcut can point to:
"%Appdata%\MyApp\MyDatabase.mde"

 

[CAM]

Steve, Graham,

I now have a better picture and I thank you for your time in giving tips and
explaining it to me. I belive this well be the fix I need. Thanks again.


Regards,

 

[Arvin Meyer [MVP]]

I have been using Terminal Service and Citrix which is similar, but far more
expensive than the peoduct mentioned by Steve and Graham. You will be
pleasantly surprised at the performance increase. In fact, if you use a
really powerful machine to run the database front-end, the performance can
easily beat many workstations.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Graham Mandeno]

You tried out the demo then, Arvin?

 

[CAM]

Arvin/Steve/Graham


If I have the back end in California as well as the front end for the
California users and the same front end installed in Florida users PC's. Do
I just need to buy the product mention by Steve and Graham for the Florida
users only? I want to save on buying licenses. Can the California users
go straight to Access thru the WAN without using the product or do the
California users need to use the product as well? Also, Arvin I appreciate
your input regards this. Again I want to thank everyone on their input.

Regards,

 

[Steve Schapel]

Cam,

Here's the thing that is sometimes tricky to get your head around...
You put the Florida user's frontend application on the computer in
Califiornia.

The Florida user's frontend is therefore in the same place as the
backend. The WinConnect product is also installed on this same computer
in California. The Florida user will connect to the California computer
remotely, and use their frontend database application remotely.
Initially I thought you only had one user in California and one user in
Florida, but it now sounds like that may not be the case. If you want
more than three remote connections to the Califirnia computer, you will
need to buy additional connections, otherwise just the one purchase and
installation is all that is necessary.

 

[Arvin Meyer [MVP]]

Using your example with the front-end file(s) a computer in California, you
can run the California users on the LAN. Each California user will have
his/her front-end connected to the back-end on a server (or on a data file
on the same machine as the WinConnect users). The Florida users will need to
use WinConnect. You will only need WinConnect licenses for the Florida
users, assuming that the California users are located on a LAN instead of a
WAN.

In reality, the decision on where to put the files for the WinConnect users
has 2 parts:

1. Which is the most powerful machine.

2. Where are the fewest users.

Ideally, you want WinConnect Server running on the most powerful machine,
and connected to by the fewest users.

Here is my experience using Terminal Server:

I have 2 machines, a file server running the back-end with about 100 MB of
data, and a Terminal Server. The Terminal Server runs up to 14 connections
with an average 9 to 10 active at any time. Connected to the same back-end
database I have another 37 users on the LAN with an average of 42 or 43
active connections. There is no difference in speed with the Terminal Server
and most of the computers, however it is considerably faster than the slower
LAN machines. I've had several requests from slower LAN users to connect to
the Terminal Server to improve performance. 2 of the remote Terminal Server
machines are connected with Sprint AirCards. Performance on those machines
is slow when connecting to the file server for Word and Excel documents, but
fast when connecting to Access forms, because the Access applications are
running over the Terminal Server.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Arvin Meyer [MVP]]

Just barely, but I am impressed. At a mere $300, I might just buy it to
connect my home machine to my office machine, so that I can use my more
powerful machine from the office. The client I have in mind is interested
too.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[CAM]

Thanks Arvin

I appreciate your input now and others I now know what I want to do. Thanks
again.

Regards,

Using your example with the front-end file(s) a computer in California,
you can run the California users on the LAN. Each California user will
have his/her front-end connected to the back-end on a server (or on a data
file on the same machine as the WinConnect users). The Florida users will
need to use WinConnect. You will only need WinConnect licenses for the
Florida users, assuming that the California users are located on a LAN
instead of a WAN.

In reality, the decision on where to put the files for the WinConnect
users has 2 parts:

1. Which is the most powerful machine.

2. Where are the fewest users.

Ideally, you want WinConnect Server running on the most powerful machine,
and connected to by the fewest users.

Here is my experience using Terminal Server:

I have 2 machines, a file server running the back-end with about 100 MB of
data, and a Terminal Server. The Terminal Server runs up to 14 connections
with an average 9 to 10 active at any time. Connected to the same back-end
database I have another 37 users on the LAN with an average of 42 or 43
active connections. There is no difference in speed with the Terminal
Server and most of the computers, however it is considerably faster than
the slower LAN machines. I've had several requests from slower LAN users
to connect to the Terminal Server to improve performance. 2 of the remote
Terminal Server machines are connected with Sprint AirCards. Performance
on those machines is slow when connecting to the file server for Word and
Excel documents, but fast when connecting to Access forms, because the
Access applications are running over the Terminal Server.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access