Creating a web based db 1

[lkennemur]

Hello, I have a db in Access that needs to be put on the web. I am uncertain of what course I should take because I have no web programming experience. I have not made the decision about working with Access or completely rebuilding the database using different software that would be more compatible with web apps. I need as many suggestions as possible about both options.

Thanks,
Lesley

 

[Ed2020]

MS Access 2000 and onwards provides Data Access Pages which may help with what you want.

I haven't done much work in them at all, but what I've seen appears very basic.

If you're redeveloping your system from scratch there are more options than could possibly be listed here, but a few would be:

Database engine:
* SQL Server
* Oracle
* MySQL

Frontend application:
* Windows forms application in any of the .Net languages
* ASP.Net?

Ed Metcalfe.

Please do not feed the trolls.....

 

[Ed2020]

Forgot to mention:

There's always the compromise of leaving your database in MS Access and redeveloping a new frontend. I'm not sure it's an option I'd take, but it could be worth considering...

Ed Metcalfe.

Please do not feed the trolls.....

 

[lkennemur]

I will look into data access pages and play around with that. Do you have any experience in:
* SQL Server
* Oracle
* MySQL

I am trying to gauge the difficulty since I am not a fantastic programmer. I am not able to download software at work without proper consent so I am trying to get as many opinions as possible. I did get a suggestion to look at Openoffice.org and convert my Access db and go to hotscripts.com for scripts. Would you mind taking a look at these sites in your spare time and let me know what you think?

Thanks,
Lesley

 

[Ed2020]

I have some SQL Server experience, but I'm no expert.

There's a free trial version of the latest version of Oracle available for personal use (not time-expired and none of the funtionality is restricted). This may be of some use.

I also believe the MSDE version of SQL Server is free for personal use.

No experience at all of MySQL I'm afraid, but I've heard good reports of it.

I'll have a look at the other sites you've mentioned.

Ed Metcalfe

Please do not feed the trolls.....

 

[fneily]

I've placed an Access databases on the Web for the Maryland Department of Transportation and others. First, forget what you know about how an access form transfers data to another form or query. With Data Access Pages (DAP), you're creating WEB PAGES. Thus to transfer data from a page to another you need to write cookies. The language you'll be using will be VBScript or JavaScript. Remember, you're coding in the Internet Explorer environment, NOT ACCESS. Also, I've found most IT departments aren't familiar with the XML produced by DAP's. So they'll tell you to rewrite it in ASP or something. DAP's been around for about 6 years, yet few IT people have learned it. There are other pain in the neck nuances about coding Access for the web - it's a loooong learning curve.
Someone in another forum made a good suggestion. Put up a Windows Citrix server (I think that's what it is, I played with one 10 years ago so I forgot). Then your clients can just login remotely and your Access database can just be stand alone.

 

[lkennemur]

fneily,

So do you suggest completely rebuilding the db in MySql/comparable software or using my Access db and using PHP/ASP/ASP.Net???

Thanks,
Lesley

 

[fneily]

If you don't like the Citrix server idea, I would say you could learn SQL or MySql quickly (relatively speaking) and use it to access your Access. Remember, when you create a query in Access, you're actually creating a SQL request (to see the code, click on the down arrow next to the View button and select SQL View). Rewriting will take a long time. Since I teach in a college, maybe you could go to a local college and hire some advanced computer degree students to help you. They work cheap and they can put it on their resume. But read the opinion of others in this forum and others.

 

[JoeAtWork]

Is it an intranet application (internal company only) or for anyone connected to the internet?

Should the users be able to update information in the database, or is it for reporting only?

If it's for the internet I don't think you can use DAPs. If somebody wants to contradict/correct me on that, I would be very interested in learning what processes a request from a client. For example, if a user wants to request a list of customers from New York, how could a DAP process this request? With ASP or ASP.NET, you would write server-side code to retrieve the data and return the results as HTML. Since DAPs can only run on the client-side, I don't see how they can have dynamic access to the database (over the internet). I am also under the impression that the ActiveX controls in DAPs require an Office license on the client machines.

If the web application is for reporting purposes only, you might be able to keep your Access database. If you need to update as well, you will probably want to upgrade to one of the options you've already mentioned. This is especially true if this is for the internet, as you won't know how many users might be connected at the same time.

Access has an SQL Server upsizing wizard. It isn't perfect, but it will do at least 90% of the work for you. You will then need to do some tweaking to find the things that didn't upsize correctly.

I think you will probably be doing this in ASP or ASP.NET with an Access or SQL Server database (but you could use Oracle or MySQL as well).

 

[lkennemur]

JoeAtWork,

You asked some key questions that I haven't even thought of yet. I work on a military arsenal so they are tight on security. I am guessing intranet. The users that will be accessing the db could be overseas however, I'm not sure how many of them actually have access to a computer. It might be for reporting only.

I have gotten a referance to a couple of sites: hotscripts.com and Openoffice.org. If you have time, please look at these and let me know if this could be a possible course of action.

Thanks for your input,
Lesley

 

[JoeAtWork]

The first step is to clearly define your requirements, as that will determine what platforms you can use. Interview the people making the request to find out who needs to use it, what systems they have, and what they need the application to do.

 

[JediBMC]

lkennemur,

The first place to start is with a proper understanding of the

http://WWW application.

The type of

http://WWW application

you are looking for is a 3 tiered system. It is made up of the following components: (Note that items 1 and 2 can reside on one physical server.)

1. Data Storage Server
This component is the repository for the information the dynamic content shown to the client. This can be MS SQL Server, MySQL, Oracle, MS Access, or just about any other database.

2. Transactional Server
This component is responsible for communications between the client and the Data Storage Server. The client's requests are parsed by the Transactional Server, which then retrieves data from the Data Storage Server, based on the request. In the case of a

http://WWW application,

this is a

http://WWW server

that supports server side scripting, such as MS IIS and Apache.

3. Client
The client is the interface of the users. It provides them with a graphical means to extract/input the necessary data. In the case of a

http://WWW application,

the client is the web page the user sees. Depending on the type of Transactional Server (

http://WWW Server)

you are using, the client can be programmed using ASP, ASP.NET, PHP, etc...

Each of these 3 components has it's own set of criteria to determine when to use one or the other.

At the Data Storage Server level, you will need to determine how much information will be stored in the database (MS Access has limitations with larger amounts of data), your familiarity with structured query language (a necessary skill for either MS SQL Server, Oracle, or MySQL), and any political issues (some companies may not allow an open source database like MySQL).

At the Transactional Server level, you will need to determine what resources are available to you (do you only have Microsoft servers?) and any political issues (some companies may require that you use either IIS or Apache.)

At the Client level, you will need to know what type of

http://WWW browser

your clients will be using, what scripting languages does the Transactional Server support, and your comfort with different scripting languages (VBScript, Javascript, etc.?)

So, there really is no simple answer. What one person will answer only applies from their point of view. I could easily say "Use MS SQL and ASP pages!" because that is what I know and what I use. Others may lean more towards open source products because they are working with little or no budget, or they just feel more comfortable with those environments.

Determine what it is you need, and what your skills are. From there, you can make a pretty good determination of what products you should use to achieve your goal.


-Brian-
I'm not an actor, but I play one on TV.

 

[Pampers]

Very good explanation!

Pampers

you're never too young to learn

 

[OnLiner]

If I may, I have a question...

Just what kinds of capabilities to DAP's have? I'd like to create a DAP that resembles one of my forms. I'd like clients to be able to add records to this form from the DAP. Its all on the same network, but I can't install Access on all of the clients. All clients have IE though.

I used the wizard to create a DAP but it only shows in single form - I need it to show in continuous form and couldn't find any info on how to do that.

So, if everything is on the same internal network, do you need all that SQL stuff?

 

[Pampers]

Hi Onliner,
1. Use the Create database in designview instead
2. Drag and drop a whole table in the DAP-grid

Pampers

you're never too young to learn

 

[OnLiner]

Ah thanks pampers! I've been playing with it but can't find how to lock it so it is for viewing only and not adding records? (I decided to lock it for other reasons)

 

[OnLiner]

One more question,

What is the best book/site for learning how to do DAP's/ASP's and Access websites? I've read some and I am no closer to understanding the whole picture.

 

[JoeAtWork]

The absolute best reference is the Access Developer's handbook:

http://www.amazon.com/gp/product/0782140114/103-7038549-4127849?v=glance&n=283155

The link above is for the 2002 version, there is probably a 2003 version as well.

There are actually two volumes, one for desktop and one for enterprise, I recommend getting both. I would almost say this is the only reference you need for Access development.

 

[fneily]

To JoeAtWork, first do an extensive study on a subject before you go public with an "opinion". DAP's ARE used for the internet. When you create a DAP, the necessary XML, ASP for communication between client and server is created. You can simulate a form/subform situation. You can run background queries and then populate the web page. Web pages can be updatable or locked. ETC. ETC. Your coding is in the INTERNET EXPLORER environment (You can't display DAP's in Netscape), thus you use VBScript or JavaScript.
Here are a couple of sites to go research:

msdn.microsoft.com\library - Then on the left, expand Office Solution Development, Microsoft Office XP, Access 2002, Technical Articles

http://www.kayodeok.btinternet.co.uk/favorites/kbofficeaccesshowto.htm

To OnLiner - since you're on an INTRAnet, why don't you split your database? The use replication and syncro. to maintain it?

 

[OnLiner]

Thanks fineily! Hmmm that brings up a good point. What I did is created a database on each client, linked the tables and imported the rest. I created a macro to do all that. Is Replication better? And what is "splitting" a database?