Dynamic Internet Forms Using Access mdb 6

[annsolomon]

Our marketing director wants me to put a sales contact database on the server so she and her employees can access it and update data from remote locations. I don't think I can do it given our current resources:

We don't have SQL Server. Strictly Access/Jet databases. I tried creating pages in Access and publishing them using Front Page but it seems I can only get static forms that way. Isn't it true that I'd need something like SQL Server at the backend in order to have dynamic forms on the website for data entry?

Can anybody think of anyway I might be able to accomodate her request?

Ann

 

[Terpsfan]

When you say pages, are you meaning Active Server Pages or Data Access Pages? What version of Access do you have? What kind of server are you using, IIS? You could create a Data Access Page very easily and then I would assume FTP it to the server, all it is is a DHTML page, no server required. Another option is to create the pages in ASP but then it has to run off of IIS.

 

[Geekmomz]

Ann,
Have you considered setting up a MySQL server? There's no charge for the license, it uses fairly standard SQL, and exchanging data between it and Access ought to be nearly as straightforward as between MS SQL server and Access. Alternatively, you can export your data to flat files and use ASP, PHP, Perl, ColdFusion, or just about anything else to read them and update them.
HTH
Geekmomz

 

[annsolomon]

I meant Data Access Pages. I'm running Access 2000 SR-1. Yes, it's an IIS server.

I just came back with a book from Barnes & Nobel that seems to tell me I can use the Database Results Wizard in Front Page to convert Data Access Pages to web pages as long as we have DHTML "turned on". I'm creating the Data Access Pages right now and our network administrator is trying to locate and turn on DHTML.

I'll definitely look into MySQL Server.

We're both new at this so any further guidance would be wonderfully appreciated. Thanks to you both for the direction.

 

[Geekmomz]

Ann,
Good luck with your project. You might also want to look at

http://www.php.net/manual/en/faq.databases.php

for information on using PHP to access Access databases. You can't use FrontPage to work with PHP, but it's pretty straightforward for anyone who has used HTML to learn, and it was built from the ground up to be simple to use to connect to a number of different database types.
Geekmomz

 

[FancyPrairie]

I've created Data Access Pages for our Intranet Site. And it works really nice. My tables reside on our SQL server. However, tables could also reside in an Access database.

Chapter 10 (Creating Data Access Pages) and Chapter 11 (Scripting Data Access Pages) of "Access 2000 Developers Handbook Volume 2: Enterprise Edition" by Paul Litwin, Ken Getz, and Mike Gilbert gives some pretty good ideas on how to set up a Data Access Page.

Even if you don't use Data Access Pages, volume 1 and volume 2 are the best resource I've found for Access developement.

Note that Data Access Pages is not a good choice for internet sites but works for intranet sites.

 

[Terpsfan]

I'm glad I could be of help. If you're already working with Microsoft I'd stick with it, rather than go to MYSQL, especially since you can make Data Access Pages with Access. Not to add to your book collection, but you might want to look at Access 2000 Developer's Guide...the book has a very good section on DAP's. Since you have IIS, you might want to also further your knowledge with Beginning ASP Databases by WROX. Yes, you can get both at Barnes and Noble.

 

[annsolomon]

FancyPrairie/Omega36, thank you for your comments. I do have Access 2000 Developers Handbook Volume 2: Enterprise Edition" by Paul Litwin, Ken Getz, and Mike Gilbert (volume 1 also). Obviously, I need to revisit it.

FancyPrairie, can you expand a bit on your comment about Data Access Pages being good for Intranet but not Internet. This project needs to be on the Internet; we'll have someone in Boston and someone in Virginia Beach (when she's not traveling) that will need access to this database.

Omega36, ASP sounds like it has lots of possibilities but (as so often happens in MIS) I'm working on a project with a drop dead date of "yesterday". More specifically, she want something up by the end of next week. I'm really not sure I can meet her requirement in that time frame.

I haven't given up yet and appreciate all of your comments. Even if I can't incorporate them into this particular project, the education y'all provide will last me for a very long time.

Thanks again.
Ann

 

[FancyPrairie]

The reason Data Access Pages would not be a solution for the Internet is because they use ActiveX controls which require an Office license. However, if you know the group of people that will be using your page and know that they have Office installed, then it wouldn't be a problem.

 

[brettz]

I am writing from a MS Access 97 perspective. In Access 97, there is an option to do a File--Save As HTML from the main menu. According to the "Publish to the Web Wizard" that will appear, you can publish any combination of Tables, Queries, Forms, or Reports by simply using the wizard. I haven't been able to pull this off yet, accept for tables, but I was thinking that you might have more luck using MS2000 since it is probably bigger and hopefully better having had some of the software defects worked out.

I didn't see that you or anyone else mentioned this option, so I thought I would put it out there for you.

-Brett

 

[annsolomon]

Brett, I remember the old Publish to the Web Wizard. I think we're supposed to use the Page object in 2000 for publishing to the web instead of the old Wizard. If he's still a part of 2000, he's hiding really well.

Thanks for the comment.
Ann

 

[Terpsfan]

You can save something as an asp or html page within Access by choosing right clicking the object and then pick export, then file as type html or asp. It doesn't seem to work well with asp however. But if you wanted to create a static web page real quick in html that's how i'd do it.

 

[annsolomon]

I've been able to create static web pages already but what I need is a dynamic web page. The remote user absolutely need to be able to update the data.

Ann

 

[Isadore]

Here's an example of a web site I built using NotePad. It uses ASP.NET and OLEDB connectivity to Access. The whole site is driven by Access tables. Very easy to build, just a little vb and the dot net framework. Drop by and take a look:

http://frontpage.auburn.edu/icaae/index.aspx

...I'm getting ready to transfer all of this stuff up to the SQL Server using Visual Studio IDE. However, the response times on the above site are pretty impressive and has given us not a problem; e.g., we have had over 500 records saved in a month or two, lots of charts and graphs.

 

[BillPower]

Hi all,

Is it possible to do something similar to the examples above if, like my site, the site doesn't support Front Page or Access.

If yes, please let me know how to go about it or point me in the right direction.

Isadore, that's a good looking site.

Thanks in advance.

Bill

 

[BillPower]

I forgot to say, I'm using Access 2000 and have Personal Web Server Installed on my PC.

 

[Terpsfan]

Bill, what OS are you using? If you have Office 2000 Professional you can use IIS, which scales a lot better than PWS. PWS only handles a few concurrent users. Isadore, is the man--you are far beyond my ability right now.

However, here are a few pages, done in asp that insert data into a guestbook. Since Ann you're looking to mainly insert or update data, this might help. The first is an html form that displays the fields and the second is an asp page that inserts the data into an Access DB. I'm using SQL statements to insert data.

<HTML>
<HEAD>
<TITLE>Maryland Sports Guestbook</TITLE>
</HEAD>
<BODY>
<P><H1>Sign into the Guestbook!</P>

<FORM ACTION=GuestBook.asp METHOD = POST NAME = frmAdd>
<INPUT TYPE = hidden NAME = Action VALUE = Add>
<TABLE>
<TR>
<TD><B>Last Name</TD>
<TD><INPUT TYPE=text NAME=txtGuestLname SIZE=20></TD>
<TD WIDTH = 10></TD>
<TD><B>First Name</TD>
<TD><INPUT TYPE=text NAME=txtGuestFname SIZE=15></TD>
</TR>
<TR>
<TD><B>Telephone</TD>
<TD><INPUT TYPE=text NAME=txtTelephone SIZE=15></TD>
<TD WIDTH = 10></TD>
<TD><B>Email Address</TD>
<TD><INPUT TYPE=text NAME=txtEmail SIZE=25></TD>
</TR>
<TR>
<TD><B>Comments</TD>
<TD COLSPAN=4><TEXTAREA ROWS=5 COLS=50 NAME=txtComments></TEXTAREA></TD>
</TR>
<TR>
<TD HEIGHT=60><INPUT TYPE=button NAME=btnSubmit VALUE=Send></TD>
</TR>
</TABLE>
</FORM>

<SCRIPT LANGUAGE=vbscript>
Sub btnSubmit_OnClick()
If Len(frmAdd.txtGuestLname.value) = 0 Then
Alert &quot;You must enter your last name&quot;
frmAdd.txtGuestName.focus
Exit Sub
End If

'If we make it this far then submit the form
Call frmAdd.Submit()
End Sub
</SCRIPT>

</BODY>
</HTML>

<HTML>
<HEAD>
<TITLE>Guest Book Insert</TITLE>
</HEAD>
<BODY>
<%
'Declare variables
Dim strInsert
Dim strValues
Dim adCmdText


'Set the required variables
adCmdText = 1

If Request.Form(&quot;Action&quot = &quot;Add&quot; Then

'Start building the SQL Strings with the required fields
strInsert = &quot;Insert into tblGuestBook (GuestLname, GuestFname, Telephone, Email, Comments&quot;
strValues = &quot;VALUES('&quot; & Cstr(Request.Form(&quot;txtGuestLname&quot) & _
&quot;','&quot; & Cstr(Request.Form(&quot;txtGuestFname&quot) & _
&quot;','&quot; & Cstr(Request.Form(&quot;txtTelephone&quot) & _
&quot;','&quot; & Cstr(Request.Form(&quot;txtEmail&quot) & _
&quot;','&quot; & Cstr(Request.Form(&quot;txtComments&quot) & &quot;'&quot;

'Create and open the database object

Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot
objConn.Open(&quot;DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=&quot; & Server.MapPath(&quot;\vbprogrammer38\db\MDSports.mdb&quot)


'Create the command object
Set objComm = Server.CreateObject(&quot;ADODB.Command&quot

'Set the command object properties
Set objComm.ActiveConnection = objConn
objComm.CommandText = strInsert & &quot &quot; & strValues & &quot&quot;
objComm.CommandType = adCmdText

'Execute the command
objComm.Execute

'Display a message to the user
Response.Write &quot;<B>Thanks for registering into the Maryland Sports Guestbook!<B><P>&quot;
Response.Write &quot;Your comments are welcome.<P>&quot;


End If

'Close and de-reference database objects
Set objComm = Nothing
objConn.Close
Set objConn = Nothing
%>

</BODY>
</HTML>

 

[BillPower]

Hi Omega36,

My OS is Windows ME and I'm just working on my own PC at home at the moment.

I am completely new to this, how do I get the ASP Page on to my web site, do I just use my web publishing s/w.

Do I Publish my DB on the site. If so how do I retrieve any input into it.

Thanks, very much for responding so quickly, it's much appreciated.

 

[Terpsfan]

Bill, are you hosting your own site with PWS? One other option is to go to Brinkster.com, they have free web hosting up to 30MB of data with ASP support. You can also purchase a domain with them for about 10 bucks. Then you basically FTP all your web files to a folder and your Access db to a database folder. It's great if you are developing a small site. If you really want to continue using PWS for your site then you would put all of your asp files in either the same folder your site is on or in C:\Inetpub\

http://wwwroot.

You don't have to put your database in the same folder, but it's advisable.

 

[Isadore]

Omega et al. Thanks for the stars. Kewl. Well, you're wrong on one point. The entire site, by building it in NotePad, puts both HTML and VB on the same page (some programmers call this spagetti coding, but I'm not sure that term is used for that purpose). At any rate, at some point you want to split the pages up (HTML + VB codebehind).

But let me tell you this, this stuff is pretty darn easy, just vba just like in Access. Using OLE DB Jet 4.0 Access + the dot NET framework is all free stuff. Dot NET you can download. You don't need frontpage, only the extensions.

If I can be of any assistance I would glady Zip 30 or so pages of code from the site so that you can print it out - if you go in this direction (dot NET effectively put ASP and ADP pages out of business). Anyway, you could save quite a few hours with some of the subtle syntax, etc... My code is clean but perhaps not the sharpest and brightest. I've only been on dot NET a couple of months.

My email is: biochem3d@yahoo.com

Feel free to drop a line, as I said, I can ZIP up a lot of aspx pages (2-5K each) and forward for your reference -- minimally it could serve as a starting point. Also, on Tek-Tips there is an ASP.NET forum, lots of discussion over there on the ins and out. Bottom line, if you're going on the web, the longer you avoid dot NET the longer it'll take ya to catch up. dot NET came out this year, its the leader, and its here to stay. Thanks for taking a look!