shawn haning said:
I am looking for a solution that allows my remote users (on our company
network) to submit data directly to my Access 2007 database.
Solutions I've Considered so far and why each doesn't work:
1. Users open the database remotely and use its forms to enter data.
Why it doesn't work: network lag time. 20-50 seconds of lag time after
every
operation, even moving from one field to the next.
There's no question that using a connection that is 100 times slower, is a
lot slower. If you paid a consultant or devloper(s) good money to have
someone test this and set up this scenario, you should ask for refund on the
money. It is simply dishonest for someone not to look at the speed of the
network when attempting to do such a setup. As you found out, a WAN is
too slow.
2. Citrix server
Why it doesn't work: our network admin says our Citrix server is 2000 and
won't support 2007 Office applications.
I would double check with those people, because it's not citrix that won't
support office, is the actual server that the software is running on. So, I
think the informaton you been given here is a bit missleading.
It's not the fact that citrix won't do this, it's the fact you're running an
older version of *windows* software on your server. I don't think it takes a
lot of
mental effort to figure out that office 2007 will not run on a old PC
running DOS, or windows 95 either.
So it's not the fact that you can't use the above setup, it means for some
strange unbeknown reason your company's not willing to upgrade to a later
edition of windows. I was actually under the impression that office 2007
would install on windows 2000, but it not really a big deal.
Citrix is a good solution here, and you could simply upgrade
your server to a later edtion of windows. If your compaany unwilling to
purchase a upgrade and get your the hardware+software you need, then there's
really nothing anybody can do for you in this kind of situation.
3. HTML-based form working with an ASP file to send data to the database.
Why it doesn't work: Access 2007 apparently doesn't allow this, no longer
supporting 'data access pages'. My test page simply displayed the asp
script
after pushing submit, without updating the database or giving me an error.
Your information is completely wrong here.
Data access pages are something that has been depreciated in MS
access, but they have absolutely ***no*** relationship to you using ASP to
collect the data. So, building a web form usign asp, or better now asp.net
has **NO** relationship to data access pages.
I repeat:
DAP and ASP are completely unrelated technologies, and completely have
nothing to do with each other in any way shape or form.
Of course when you are using somthing like ASP, then you not really using MS
access at all anway. You can store the data collected from these web pages
in
an access back end file, but the front end part will not be ms-access, and
ms-access is simply not a web based tool.
So using ASP is still a good possbility here. To me the only issues that
come up in this case is do you have a web server (or space) that's willing
to be exposed to the very nasty Internet that's full of hackers and people
who like to damage these types of systems. So the setup and deployment of
system like this is not that hard, but the security issues, and exposing
your company network to an external Internet connection is a significant
risk, and one that means you better have significant expertise on hand that
knows exactly what they're doing in this case. You have to think long and
hard before opening up and exposing your company network to an external
outside Internet connection that is inward looking.
However, for gathering informaton, I think ASP or now ASP.net is a great
soltion here.
4. Using Access's 'Create Email' tool that inserts a form into an email
that
the remote users can fill out and respond to; automatically updating the
database.
I never had a whole lot of luck with this solution. However, it is nice in
that your end users don't have to use the web, or have MS access on thier
comptuers.
5. I'm out of ideas... anyone got any suggestions? Thanks in advance..
We should really get some guidelines as to what kind of solution you're
looking for invoilves.
It is not clear if this solution to be used by uses that will have a access
installed? (kind of a HUGE HUGE detail to leave out???).
Two types of systems here, you have to clear up which type you looking for:
1)
You have to distinguish between deploying an applications for your users
that allows remote use. This kind of application can have install issues,
support issues, training issues, and all kinds of workflow issues that you
want integrate into your company. Remember, for the most part when you build
an application MS access, you have to put on a software developers hat, and
you are building an information system for your company. So in this
scenario, we're talking about an application that you built, that you need
your users on the road or away from the office can continue to use the
application.
or
2)
Are we talking about a generalized request for information type form that
end users (non emplyess etc.) will fill out and submit to you? In this case
you have to ask yourself is it practical to install a very large and complex
program like MS access for a simple data entry form? In this case you're not
really deploying an application to users, but you are simply looking for a
form with information that is submitted to you. There is a huge grand canyon
of difference between a simple form the gathers data, and that of an
application that does a lot of your business processes at work.
So which is your solution to looking for, 1, or 2?
If you're looking for number "2" type solution, then I do strongly suggest
some type of web form soltion (asp.net).
If you're looking for a "1" type solution in which are people can have the
software installed on their laptops, and be anywhere on the planet with a
internet connecton, then there's the number of solutions that are really
good right now, and in some cases is even free solutions that work with
ms-access.
A great way to have multple users, and solve most of the security issues
is to simply use the free edition of sharepoint (office live).
This means you'll simply move your tables up to what are called share point
lists. You then continue to use your MS access applicaiton installed on each
computer. The beauty of this solution is that you don't have to purchase any
hardware, you don't have to purchase SQL server, and you don't even have to
setup sql server or know what the thing is!! furthermore it you don't have
to set up a server at your company, and open it up and expose it to the
Internet either.
I've been testing SharePoint (free office live) this week, and it rocks, and
the performances been really great too.
You can also read the following article of mine were I suggest some
additional solutions that will work for you (we talking type "1" scenarios
where you have an application). Note that I wrote the article a few years
ago, and that was before the office live (free on-line sharepoint) was being
offered by Microsoft.
http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html