Allow users without Access 97 ability to view data and search data in tables.

I'm in a bit of a pickle.

My employer, a health insurance firm, had me design a small database to
track benefit issues. The intended users were technical specialists and
some managers (about 90 people, no more than 10 concurrent users at any
one time) all of which had Access 97 loaded on their machines.
Everything was going smoothly, and I delivered them their VBA
application that does everything they initially wanted it to.

However, yesterday they dropped a bomb on me. They now want all claims
examiners and service specialists to have access to the data, and this
task MUST be completed by 12/16/2005. These users must have access to 3
of my tables, and be able to effectively search the database (about
35,000 records in the primary table) with two different criteria. These
new users will add up to well over 2,000 people (albeit, low
concurrency. No more than 30 users at any one time), and only one third
of them have Access 97 on their machines. I cannot require all users to
have A97 on their machines as the company must pay for that software on
a per license basis. Furthermore, they will be replacing their old
office package with office 2003 in 2nd qtr of 2006. I do not have the
Developers version of A97, so I cannot just make a runtime for
everybody to use. This leads me to believe I need to make some sort of
web page based off the database. (I have no idea how to do that.
ASP.Net?)

I have virtually no other development tools at my disposal other than
vanilla A97, and no experience dealing with ANY kind of web
development. All of the limited experience I have is with Visual Basic.
I don't even know enough about web page development to know what I
don't know, and I have to develop, test, and implement a dynamic web
page in two months. I can possibly request budget to buy some
development tools for the job, but I have to know exactly what I'm
going to need. (And they can't cost too much. Visual Studio is
probably too much money.)

So, my question is this: What is the easiest, cheapest, least involved
method to open up your Access database data to a large audience who do
not have MS Access on their machines? Barring easy and cheap, IS there
a way to even do what I need to do without any additional tools?

What I was thinking of doing is duplicating the current database to a
new .mde file (the new users do not need instantly updated data), then
basing a web page in some way off of this table. However, I have no
idea how to run a query with parameter inputs from a web page, and have
those results display. Further complicating things is the fact that I
have three tables; one primary table for the issues, and two secondary
tables which hold the comments and location time stamps for that
particular issue, and I need the secondary tables viewable via links
from the main table.

I realize a detailed answer to my question could easily fill a book.
What I'm looking for is a general idea of a method to use as well as
what tools I'll need to complete it.

Thanks in advance for any advice you guys may have!

re: Allow users without Access 97 ability to view data and search data in tables.

Andy_Khosravi@bcbsmn.com wrote:[color=blue]
> I'm in a bit of a pickle.
>
> My employer, a health insurance firm, had me design a small database to
> track benefit issues. The intended users were technical specialists and
> some managers (about 90 people, no more than 10 concurrent users at any
> one time) all of which had Access 97 loaded on their machines.
> Everything was going smoothly, and I delivered them their VBA
> application that does everything they initially wanted it to.
>
> However, yesterday they dropped a bomb on me. They now want all claims
> examiners and service specialists to have access to the data, and this
> task MUST be completed by 12/16/2005. These users must have access to 3
> of my tables, and be able to effectively search the database (about
> 35,000 records in the primary table) with two different criteria. These
> new users will add up to well over 2,000 people (albeit, low
> concurrency. No more than 30 users at any one time), and only one third
> of them have Access 97 on their machines. I cannot require all users to
> have A97 on their machines as the company must pay for that software on
> a per license basis. Furthermore, they will be replacing their old
> office package with office 2003 in 2nd qtr of 2006. I do not have the
> Developers version of A97, so I cannot just make a runtime for
> everybody to use. This leads me to believe I need to make some sort of
> web page based off the database. (I have no idea how to do that.
> ASP.Net?)
>
> I have virtually no other development tools at my disposal other than
> vanilla A97, and no experience dealing with ANY kind of web
> development. All of the limited experience I have is with Visual Basic.
> I don't even know enough about web page development to know what I
> don't know, and I have to develop, test, and implement a dynamic web
> page in two months. I can possibly request budget to buy some
> development tools for the job, but I have to know exactly what I'm
> going to need. (And they can't cost too much. Visual Studio is
> probably too much money.)
>
> So, my question is this: What is the easiest, cheapest, least involved
> method to open up your Access database data to a large audience who do
> not have MS Access on their machines? Barring easy and cheap, IS there
> a way to even do what I need to do without any additional tools?
>
> What I was thinking of doing is duplicating the current database to a
> new .mde file (the new users do not need instantly updated data), then
> basing a web page in some way off of this table. However, I have no
> idea how to run a query with parameter inputs from a web page, and have
> those results display. Further complicating things is the fact that I
> have three tables; one primary table for the issues, and two secondary
> tables which hold the comments and location time stamps for that
> particular issue, and I need the secondary tables viewable via links
> from the main table.
>
> I realize a detailed answer to my question could easily fill a book.
> What I'm looking for is a general idea of a method to use as well as
> what tools I'll need to complete it.
>
> Thanks in advance for any advice you guys may have!
>[/color]
I think your company needs to hire someone with web expertise.

bob

re: Allow users without Access 97 ability to view data and search data in tables.

They have people with web expertise who could do this easily. However,
they will not have time to turn their attention to this particular
problem in time to deal with it by the deadline. In other words, I'm it.

re: Allow users without Access 97 ability to view data and search data in tables.

Andy_Khosravi@bcbsmn.com wrote:[color=blue]
> They have people with web expertise who could do this easily. However,
> they will not have time to turn their attention to this particular
> problem in time to deal with it by the deadline. In other words, I'm
> it.[/color]

An experienced "web guy" could bang out something that meets the minimum
requirements in a few hours. Management needs to have one of those guys
allocate the time or drop the requirement.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

re: Allow users without Access 97 ability to view data and search data in tables.

Perhaps my original post was a bit misleading. This is not a company
critical application; it is one research tool amongst dozens given to
examiners and service representatives. It is important only to my small
department and the goals my management has set. It will be a useful,
but not absolutely critical, research tool.

Our IT department does not take small ad-hoc requests like this. (Large
company bureaucracy) Their time is strictly reserved for larger company
projects, or for queued projects that have had budget allocated for
them (Even very small ones). It IS possible to get a request in to
them, but you must have justifications, fight dozens of other
departments for equally important projects, and then wait in a long
line to have it completed if your project was lucky enough to be
selected.

Back to the topic at hand; Rick, you mentioned that an experienced web
guy could pound this out in a couple of hours. This is music to my
ears; if one of those guys can do it in a couple of hours then I should
be able to do it in two months. Is this something that could easily be
accomplished with MS FrontPage or Macromedia Dreamweaver? I wasn't
sure if those software packages contained ready made tools to link to
an Access database.

re: Allow users without Access 97 ability to view data and search data in tables.

Andy_Khosravi@bcbsmn.com wrote:
[color=blue]
> So, my question is this: What is the easiest, cheapest, least involved
> method to open up your Access database data to a large audience who do
> not have MS Access on their machines? Barring easy and cheap, IS there
> a way to even do what I need to do without any additional tools?[/color]

Check Ebay or any place else you can think of for a copy of Visual
Basic 6 (or 5) Professional. The language is basically the same.
You can use DAO or ADO for data access. There are tools to help you
along with creating basic bound forms.

or

Java is free. A mildly intelligent person could whip up a simple
form or two to search a Jet database.

Keep in mind a web application requires a web Server.

re: Allow users without Access 97 ability to view data and search data in tables.

rkc wrote:[color=blue]
> Andy_Khosravi@bcbsmn.com wrote:
>[color=green]
>> So, my question is this: What is the easiest, cheapest, least involved
>> method to open up your Access database data to a large audience who do
>> not have MS Access on their machines? Barring easy and cheap, IS there
>> a way to even do what I need to do without any additional tools?[/color]
>
>
> Check Ebay or any place else you can think of for a copy of Visual
> Basic 6 (or 5) Professional. The language is basically the same.
> You can use DAO or ADO for data access. There are tools to help you
> along with creating basic bound forms.
>
> or
>
> Java is free. A mildly intelligent person could whip up a simple
> form or two to search a Jet database.
>
> Keep in mind a web application requires a web Server.
>
>[/color]
I would suggest you take a hard look at Visual Web Developer 2005
Express Beta 2. It is from Microsoft and currently is FREE. It
requires .Net on the server. You can add .NET to your XP workstation
and do your development there.

http://lab.msdn.microsoft.com/express/vwd/

It appears to be able to build forms that are linked to data without any
coding. And they do link to sql as well as Access/jet datbases. There
is an online tutorial that is audio/video. Several books are due to
come out in November.

Bob

re: Allow users without Access 97 ability to view data and search data in tables.

Bob Alston wrote:[color=blue]
> rkc wrote:
>[color=green]
>> Andy_Khosravi@bcbsmn.com wrote:
>>[color=darkred]
>>> So, my question is this: What is the easiest, cheapest, least involved
>>> method to open up your Access database data to a large audience who do
>>> not have MS Access on their machines? Barring easy and cheap, IS there
>>> a way to even do what I need to do without any additional tools?[/color]
>>
>>
>>
>> Check Ebay or any place else you can think of for a copy of Visual
>> Basic 6 (or 5) Professional. The language is basically the same.
>> You can use DAO or ADO for data access. There are tools to help you
>> along with creating basic bound forms.
>>
>> or
>>
>> Java is free. A mildly intelligent person could whip up a simple
>> form or two to search a Jet database.
>>
>> Keep in mind a web application requires a web Server.
>>
>>[/color]
> I would suggest you take a hard look at Visual Web Developer 2005
> Express Beta 2. It is from Microsoft and currently is FREE. It
> requires .Net on the server. You can add .NET to your XP workstation
> and do your development there.[/color]

I didn't ask the question. If the op is still interested that's a option
for him as well as the Express betas of VB.net or C#.net. They will
still need a web server if they go with a web app.

re: Allow users without Access 97 ability to view data and search data in tables.

> still need a web server if they go with a web app.

HTML page with embedded (client side) VBS or JS would
work in a browser.

I have a client who is distributing a CD with a large
text document and a Java/JS search engine, accessed
through any browser.

(david)

"rkc" <rkc@rochester.yabba.dabba.do.rr.bomb> wrote in message
news:bmX5f.71049$K91.22946@twister.nyroc.rr.com...[color=blue]
> Bob Alston wrote:[color=green]
>> rkc wrote:
>>[color=darkred]
>>> Andy_Khosravi@bcbsmn.com wrote:
>>>
>>>> So, my question is this: What is the easiest, cheapest, least involved
>>>> method to open up your Access database data to a large audience who do
>>>> not have MS Access on their machines? Barring easy and cheap, IS there
>>>> a way to even do what I need to do without any additional tools?
>>>
>>>
>>>
>>> Check Ebay or any place else you can think of for a copy of Visual
>>> Basic 6 (or 5) Professional. The language is basically the same.
>>> You can use DAO or ADO for data access. There are tools to help you
>>> along with creating basic bound forms.
>>>
>>> or
>>>
>>> Java is free. A mildly intelligent person could whip up a simple
>>> form or two to search a Jet database.
>>>
>>> Keep in mind a web application requires a web Server.
>>>
>>>[/color]
>> I would suggest you take a hard look at Visual Web Developer 2005 Express
>> Beta 2. It is from Microsoft and currently is FREE. It requires .Net on
>> the server. You can add .NET to your XP workstation and do your
>> development there.[/color]
>
> I didn't ask the question. If the op is still interested that's a option
> for him as well as the Express betas of VB.net or C#.net. They will still
> need a web server if they go with a web app.[/color]

re: Allow users without Access 97 ability to view data and search data in tables.

<Andy_Khosravi@bcbsmn.com> wrote
[color=blue]
> Perhaps my original post was a bit
> misleading. This is not a company
> critical application; it is one research
> tool amongst dozens given to
> examiners and service representatives.
> It is important only to my small
> department and the goals my manage-
> ment has set. It will be a useful,
> but not absolutely critical, research tool.[/color]
[color=blue]
> ... Is this something that could easily be
> accomplished with MS FrontPage or
> Macromedia Dreamweaver? I wasn't
> sure if those software packages contained
> ready made tools to link to an Access database.[/color]

It is hard to say with no more information about the application than you
have given, and without knowing more about your skills.

If their use of the application is sufficiently simple, it could be done
with Front Page and the Data Interaction Wizard (FP 2002 or 2003). But
simple interaction is a key phrase here -- nothing very complicated.

As far as I know, Dreamweaver has no intrinsic ability to access an Access
database, but someone who's more familiar with it than I may know of an
approach.

ASP.NET will use one of the Object-Oriented languages, probably either
VB.NET or C#. If you are not already a programmer, then that is likely to be
a "stretch". Traditional .asp pages are likely to be somewhat easier to
learn and implement.

Developer Editions of Access 97 are not easy to find, and not inexpensive
when you find them -- but a visit to the online auction sites would be
worthwhile IF all users will be on a LAN. If you are able to purchase a copy
and you choose that approach, thirty concurrent users is not asking a great
deal of Access, if the application is well-designed and well-implemented.
Tony Toews' website, http://www.granite.ab.ca/accsmstr.htm will be your
FRIEND -- it has the best collection of multiuser performance and
corruption-avoidance links and information I know about.

Interesting that they can't afford to hire someone who could whip out what
you need in a few hours or a few days, but can afford to devote your time to
such a project for nearly two full months. I'm wondering how many copies of
Access 97 they would need so you didn't have to face even the learning curve
of the Developer Edition? I have seen Office Pro 97 for as little as $50 at
computer sales events (and, it certainly appears to be genuine... it is
three-releases-back obsolescent, after all, even if many of us still like it
as a developer tool).

Larry Linson
Microsoft Access MVP

re: Allow users without Access 97 ability to view data and search data in tables.

david epsom dot com dot au wrote:[color=blue][color=green]
>>still need a web server if they go with a web app.[/color]
>
>
> HTML page with embedded (client side) VBS or JS would
> work in a browser.
>
> I have a client who is distributing a CD with a large
> text document and a Java/JS search engine, accessed
> through any browser.[/color]

Well I'll be dipped in rainbow sprinkles. I just gave this
a try using vbscript and an .mdb file and I guess you could
build an web application on a local network without a server.
The more I learn, the more I realise I don't know shit.

re: Allow users without Access 97 ability to view data and search data in tables.

A lot of helpful hints. Thanks for all the tips guys!

re: Allow users without Access 97 ability to view data and search data in tables.

>Bob Alston Wrote:
[color=blue]
>I would suggest you take a hard look at Visual Web Developer 2005
>Express Beta 2. It is from Microsoft and currently is FREE. It
>requires .Net on the server. You can add .NET to your XP workstation
>and do your development there.[/color]

I'm taking a look at this option now, but I have some concerns about
the EULA. The contract language seems to imply there are limits to how,
when, and where the beta version can be used. If that only applies to
the design tool itself and not the apps you build with it then it
should be ok. However, if it also applies to the web pages you
developed with it (or if those web pages stopped working suddenly after
the beta expiration date) then I could be in trouble.

The idea that David Epsom mentioned also has some possibilities. All of
the users will have access to the same network drives on the intranet.
Remote access is NOT a requirement. I hope the learning curve on Java
is not too steep. However, with a magic price of free I'll have to
check it out IF it turns out FrontPage doesn't do the trick.

Again, I'd like to thank you guys for your input. At least now I have
a general direction and some options on the table.

P.S. What's the command to quote another posts text? I'm familiar
with the syntax used on most other chat
forums, but not with those used on this newsgroup.

re: Allow users without Access 97 ability to view data and search data in tables.

You can try OpenOffice.org Base which is completely Free and Open
Source.
You can download OpenOffice.org free of charge from www.openoffice.org

The new version 2.0 of OpenOffice.org comes with a MS Access equivalent

called OO Base.

You can import MDB file directly into OO Base.
Data and query should be copy directly.

OO Base doesn't use VBA, but it own "OpenOffice Basic" Language.
The structure of OpenOffice Basic is very similiar to VBA
but it has different system API.

VBA will be imported to OO Base but will not run directly.
You have to modify the VBA code before you can use it.
But I'm sure it is much easier to learn than converting
the whole application to JAVA or other free web based setup
like PHP or PERL.

It is GNU GPL LGPL licensed.

Check out www.gnu.org about the "Free" license.
More than 80% of all open source projects like "Linux" and
"Mozilla" are using the GNU license.
Mozilla is the organization create the Firefox browser
Thunderbird Email Client and Mozilla internet Suite(Netscape)

GNU GPL is 100% pure Free and Open Source license,
So you don't have to worry about the license.
You can download OpenOffice and copy it to hundred or
even thousand machines without paying a single cent.

OObase can be setup as a frontend to large scale Database Engine
like Postgresql and Mysql.
www.postgresql.org
www.mysql.com

re: Allow users without Access 97 ability to view data and search data in tables.

On 20 Oct 2005 15:42:38 -0700, Andy_Khosravi@bcbsmn.com wrote:
[color=blue]
>I'm in a bit of a pickle.
>
>My employer, a health insurance firm, had me design a small database to
>track benefit issues. The intended users were technical specialists and
>some managers (about 90 people, no more than 10 concurrent users at any
>one time) all of which had Access 97 loaded on their machines.
>Everything was going smoothly, and I delivered them their VBA
>application that does everything they initially wanted it to.
>
>However, yesterday they dropped a bomb on me. They now want all claims
>examiners and service specialists to have access to the data, and this
>task MUST be completed by 12/16/2005. These users must have access to 3
>of my tables, and be able to effectively search the database (about
>35,000 records in the primary table) with two different criteria. These
>new users will add up to well over 2,000 people (albeit, low
>concurrency. No more than 30 users at any one time), and only one third
>of them have Access 97 on their machines. I cannot require all users to
>have A97 on their machines as the company must pay for that software on
>a per license basis. Furthermore, they will be replacing their old
>office package with office 2003 in 2nd qtr of 2006. I do not have the
>Developers version of A97, so I cannot just make a runtime for
>everybody to use. This leads me to believe I need to make some sort of
>web page based off the database. (I have no idea how to do that.
>ASP.Net?)
>
> I have virtually no other development tools at my disposal other than
>vanilla A97, and no experience dealing with ANY kind of web
>development. All of the limited experience I have is with Visual Basic.
>I don't even know enough about web page development to know what I
>don't know, and I have to develop, test, and implement a dynamic web
>page in two months. I can possibly request budget to buy some
>development tools for the job, but I have to know exactly what I'm
>going to need. (And they can't cost too much. Visual Studio is
>probably too much money.)
>
>So, my question is this: What is the easiest, cheapest, least involved
>method to open up your Access database data to a large audience who do
>not have MS Access on their machines? Barring easy and cheap, IS there
>a way to even do what I need to do without any additional tools?
>
>What I was thinking of doing is duplicating the current database to a
>new .mde file (the new users do not need instantly updated data), then
>basing a web page in some way off of this table. However, I have no
>idea how to run a query with parameter inputs from a web page, and have
>those results display. Further complicating things is the fact that I
>have three tables; one primary table for the issues, and two secondary
>tables which hold the comments and location time stamps for that
>particular issue, and I need the secondary tables viewable via links
>from the main table.
>
>I realize a detailed answer to my question could easily fill a book.
>What I'm looking for is a general idea of a method to use as well as
>what tools I'll need to complete it.
>
>Thanks in advance for any advice you guys may have![/color]

You could look at the UniversalTable editor project.
http://www.codeproject.com/asp/ute.asp

this can search using filters but it is also designed to allow changing the data.

re: Allow users without Access 97 ability to view data and search data in tables.

Andy_Khosravi@bcbsmn.com wrote:
[color=blue]
> Perhaps my original post was a bit misleading. This is not a company
> critical application; it is one research tool amongst dozens given to
> examiners and service representatives. It is important only to my small
> department and the goals my management has set. It will be a useful,
> but not absolutely critical, research tool.
>
> Our IT department does not take small ad-hoc requests like this. (Large
> company bureaucracy) Their time is strictly reserved for larger company
> projects, or for queued projects that have had budget allocated for
> them (Even very small ones). It IS possible to get a request in to
> them, but you must have justifications, fight dozens of other
> departments for equally important projects, and then wait in a long
> line to have it completed if your project was lucky enough to be
> selected.
>
> Back to the topic at hand; Rick, you mentioned that an experienced web
> guy could pound this out in a couple of hours. This is music to my
> ears; if one of those guys can do it in a couple of hours then I should
> be able to do it in two months. Is this something that could easily be
> accomplished with MS FrontPage or Macromedia Dreamweaver? I wasn't
> sure if those software packages contained ready made tools to link to
> an Access database.
>[/color]

How frequently does the data in the tables change? Do you have an
internal web server?

One cheezoid way is to do something like this:

create a table called "DUAL", with one field, and one value. Doesn't
matter what it is. Then you can do this query:

select "<table>"
from dual
union all
select " <tr><td>" & field1 & "</td>" & ... & "</tr>"
from mytable
union all
select "</table>"

This should give you HTML'd output:

<table>
<tr><td>field1 value</td><td>field2 value</td>...</tr>
</table>

It's not quite complete or valid HTML, so add the missing HTML tags to
the beginning and end.

Set up a form that then exports this data into a fixed HTML file on the
webserver, or a macro that can invoke the queries to do it for you. You
can then set up a shortcut that invokes the macro by name. Check the
help file for "startup flags" or something like that.

If you have IIS, there is a way to sort of create files that IIS can
invoke Jet to actually get the data without using ASP. It's documented
at least in Getz et al.'s "Access Developer's Handbooks". I've never
tried it, though.

re: Allow users without Access 97 ability to view data and search data in tables.

Andy_Khosravi@bcbsmn.com wrote:[color=blue]
> I'm in a bit of a pickle.
>[/color]
I've been reading the responses. Quite a range of solutions! Here's my
gherkin.

Qs:
1. Is the data read-only or do you expect people to modify/append data?
2. What is the corporate policy on installing no SOE software?
3. What is the corporate policy on installing/operating one webserver on
your machine or multiple webservers which respond only to local machine?
4. Are all the intended users on LAN or are they disconnected?
5. How complex if any will are the formatting requirements for output?
6. Are all the PCs licensed for Office Standard (Word, Excel, Powerpoint?)

Possibilities:

a. Web based pages - either running on each local PC or a single PC.
There are several alternatives. Most of them require moderate web skill
as mentioned. Some don't require as much skill, but require some
research and testing on setting up the system and may not have much
range of features. One that has not been mentioned is Badblue -
http://www.badblue.com/helpmdb.htm

If you do web programming, you don't necessarily need to spend an awful
lot on an IDE (e.g. Dreamweaver, Visual Studio etc...) - some dev
languages are editable using Notepad - not that you would want to but...

b. Third party free app. Like one of the other posters, I am having a
look at Open Office 2.0 with Base - it has been so long since Access
had a new competitor. Not that I am impressed with Base, but it's the
first new one in a long while.

c. If the other PCs are licensed for Office Standard but not for Access,
you can AFAIK legitimately use VBA (or VBScript) + DAO/ADO + Jet.
Meaning you can hop, skip and jump with your current knowledge, using
this combination to query and edit data using Word, Excel - both support
VBA Forms. Remember there is also the much forgotten Microsoft Query.

Ananda