Remote data entry solution needed for Access 2007

[shawn haning]

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.

2. Citrix server

Why it doesn't work: our network admin says our Citrix server is 2000 and
won't support 2007 Office applications.

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.

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.

Why it doesn't work: Access 2007 says it can't launch Outlook (even though
we're all running Outlook 2007). I'm told this is due to our network running
a Zimbra email server instead of an Exchange server.

5. I'm out of ideas... anyone got any suggestions? Thanks in advance..

 

[Albert D. Kallal]

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

 

[roverlandpark]

You might be able to set-up an Excel file that feeds into your Access
program. The only issues here would be multiple users attempting to
use the form at the same time (you could use one version for each
user) and users altering the form (use protection and validation to
minimize impact). If your company has a SharePoint service, you could
set the file there and have people "check-out" the document while
they're making changes, but this limits use to one person at a time.
If you have SharePoint, you might even be able to link to one its
datasheets directly. I haven't used this before, but I'm pretty sure
Access can connect to it. Hope this helps

 

[shawn haning]

Thanks for taking the time to respond thoroughly, I really appreciate it!

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.

We didn't pay a consultant or developer; this was my own pet project,
and I have only basic programming/network experience.

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.

Thanks for the info! I understand better now.
But I think I'll look into the sharepoint or asp.net solutions first, as
they wouldn't require an upgrade.

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.

Again, thanks for the info. I don't know much about ASP, but I will look
into this again as my preferred option. I had suspected the code I was using
might need to be updated for Access 2007, but then I got it confused with
DAP.

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.

Thanks, I'll place this on the backburner then. They do all have MS Access
on their computers.

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???).

Yes they do, and you're right- that was an oversight on my part, leaving
that out of the initial post.

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?

Not quite sure, but I'm thinking it sounds more like 2. I developed an MS
Access database so that I could run queries and generate reports on the data.

If you're looking for number "2" type solution, then I do strongly suggest
some type of web form soltion (asp.net).

Yes, thanks. That will be what I look into first.

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.

I started to look at SharePoint, but got a bit confused. Is there a tutorial
for SharePoint that you could recommend?
I would like to explore that if I am unable to get an ASP.NET solution to
work.
Thanks again, Albert.

 

[Albert D. Kallal]

Great stuff.

Yes they do, and you're right- that was an oversight on my part, leaving
that out of the initial post.

Ah, excellent! (and, there always the runtime anyway).

This means that you can/will develop an applicaton, and we simply need the
"data" part to be shared by everyone.

I started to look at SharePoint, but got a bit confused. Is there a
tutorial
for SharePoint that you could recommend?
I would like to explore that if I am unable to get an ASP.NET solution to
work.

I would start here:
http://office.microsoft.com/en-us/access/FX100487571033.aspx

http://office.microsoft.com/en-us/access/FX100646911033.aspx

Create Access forms and reports from a SharePoint list
http://office.microsoft.com/en-us/access/HA102093061033.aspx

Import from or link to a SharePoint list
http://office.microsoft.com/en-us/access/HA012303131033.aspx?pid=CH101759701033

The basic idea here is that you're simply going to move your tables from
your application now and move it up to share point.

To me, the beauty of this system is that you don't have to purchase a set up
a server at all, but just use the free on-line edition of sharepoint. You
can find it here:

http://www.officelive.com/
(you want the Office live small bussness edition)

Prior to about a week ago I'd never really played with share point, and in
less than 1 hour of my time I was uploading tables from a2007 to office
live, and it wass all free.

There's no question that there's several limitations and a few issues that
change when you use a list on share point, but at the end of the day, the
learning curve to set up this stuff was very small, and in fact likey less
time then it takes to install and setup sql server.

In your case if this is just a form of information and some table data that
you need to gather from your users, and they'll have Internet connections +
MS access intalled, then sharepoint is an ideal solution for you. In effect,
this solution has zero hardware costs and zero cost to your IT deparment
setup wise time. Thus, you can use the free edition of SharePoint sitting
out there for you to use. (and who knows, maybe you company has share point
running already).

 

[Erika]

Hi, I actually had to deal with a similar problem on the project I'm working on. I'm not sure if it's fast enough for you, but it's working out fairly well for our purposes. I ended up setting up a CVS (controlled version system) repository in a directory that is dedicated to our project. Then I added macros to our database so that users could export data that they added to the database, then import the data saved in the CVS (it has to be a text file to work with CVS) and then append the exported data (data that was just entered) and then export the new and improved set of data. This process is still fairly slow because it requires that before updating the database each time the user must update their copy of the data stored in the CVS repository and that after updating the data, commit changes to the repository. If you want more details, shoot me an e-mail ([email protected]). Good luck!