Using Access As a web database

432

Expert 256MB

I have an Access database that I want to connect to some forms that i created in Frontpage. I dont have a clue of where to start. Can anybody help or point me to a simple tutorial.
Thank You

Classic ASP

You need to set up a connection string, and use asp variables to load data into your form.

You will also need to use an SQL querystring to pull data from your database.

Try some things out. If you have specific problems post them in the ASP forum as you run into them and I can keep helping you out.

May 12 '08 #2

Ok i search and found some tutorials, got some stuff going but is stuck at the following error

Microsoft JET Database Engine error '80040e14'

Syntax error. in query expression 'SELECT [Maintenance_History].HardwareAssetID'.

/DMS/Update.asp, line 25

I am calling the following codes from a form that i entered information

<HTML><HEAD><TITLE>Added Page</TITLE></HEAD>
<BODY BGCOLOR=#ffffff MARGINWIDTH="0" MARGINHEIGHT="0" LEFTMARGIN=0 TOPMARGIN=0>
<%
Dim DataConnection
Dim RsAddRecords
Dim strSQL

'Open the database connection
Set DataConnection = Server.CreateObject("ADODB.Connection")
DataConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source="& Server.MapPath("/DMS/testBase.mdb"))

'Create an ADO recordset object
Set RsAddRecords = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT SELECT [Maintenance_History].HardwareAssetID, [Maintenance_History].DateReported, [Maintenance_History].ProblemDescription, [Maintenance_History].Cause, [Maintenance_History].SolutionDescription, [Maintenance_History].PerformedBy, [Maintenance_History].DateCompleted, [Maintenance_History].Comments, [Maintenance_History].Department, [Maintenance_History].Status FROM [Maintenance_History];"

'Set the cursor type we are using so we can navigate through the recordset
RsAddRecords.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
RsAddRecords.LockType = 3

'Open the recordset with the SQL query
RsAddRecords.Open strSQL, DataConnection

'Tell the recordset we are adding a new record to it
RsAddRecords.AddNew

'Add a new record to the recordset
RsAddRecords.Fields("HardwareAssetID") = Request.Form("Asset_ID")
RsAddRecords.Fields("DateReported") = Request.Form("Date_Reported")
RsAddRecords.Fields("ProblemDescription") = Request.Form("Issue_Reported")
RsAddRecords.Fields("Cause") = Request.Form("Cause")
RsAddRecords.Fields("SolutionDescription") = Request.Form("Action_Taken")
RsAddRecords.Fields("PerformedBy") = Request.Form("Serviced_By")
RsAddRecords.Fields("DateCompleted") = Request.Form("Date_Completed")
RsAddRecords.Fields("Comments") = Request.Form("Comments")
RsAddRecords.Fields("Department") = Request.Form("Department")
RsAddRecords.Fields("Status") = Request.Form("Status")

'Write the updated recordset to the database
RsAddRecords.Update

'Reset server objects
RsAddRecords.Close
Set RsAddRecords = Nothing
Set DataConnection = Nothing
%>

<p align="center"> <p align="center">Data was successfully added<p align="center"> <p align="center">
</BODY>
</HTML>

May 15 '08 #3

432

Expert 256MB

You have 2 SELECTS. You only need 1. There could be more issues with your SQL but try that first

Expand|Select|Wrap|Line Numbers

 
'take out one of the SELECT

strSQL = "SELECT [Maintenance_History].HardwareAssetID, [Maintenance_History].DateReported, [Maintenance_History].ProblemDescription, [Maintenance_History].Cause, [Maintenance_History].SolutionDescription, [Maintenance_History].PerformedBy, [Maintenance_History].DateCompleted, [Maintenance_History].Comments, [Maintenance_History].Department, [Maintenance_History].Status FROM [Maintenance_History];"

Also since you are only querying 1 table you really dont need to specify the table in the SQL. In fact you might even be able to just do this:

Expand|Select|Wrap|Line Numbers

 
'take out one of the SELECT

strSQL = "SELECT * FROM Maintenance_History"

May 15 '08 #4

hey i found the error... Look at the SQL statement and you'll see two SELECT. there should only be one.
I do hope that this post help someone.
I'll probably be calling for some more help soon so please stay tune

May 15 '08 #5

I want to create a form to search the database for any record the user might need to see. the idea here is that the user would select the table he want to search then based on that selection the field combo box would be filled with the appropriate fields. I haven't a clue on how to accomplish this but i know that i need to do it to make make searching a breeze.

Help please.

Expand|Select|Wrap|Line Numbers

 <form method="POST" action=search.asp>

            <p align="center"><b>

            <font color="#000080" face="Poor Richard" size="5">Table</font></b>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

            <select size="1" name="Table">

            <option>Adapters/Cards</option>

            <option>AdapterCards/Subform</option>

            <option>Hardware Assets</option>

            <option>Internal Drives</option>

            <option>internaldrives/subform</option>

            <option>Maintenance History</option>

            <option>Ssupportlogs</option>

            </select></p>

            <p align="center"><b><font size="5" color="#000080">Fields<select size="1" name="Field">

            </select></font></b></p>

            <p align="center"><b>

            <font color="#000080" face="Poor Richard" size="5">Content</font></b>

            <input type="text" name="Search" size="38" tabindex="1"></p>

            <p align="center">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

            <font size="3">

            <input type="submit" value="Search" name="Submit" tabindex="2" style="font-weight: 700"></font></p>

        </form>

May 19 '08 #6

432

Expert 256MB

Are you talking about doing this in the local users browser?

For example they select something from a drop down and the next drop down is instantly populated ? You may need to use javascript to have that happen and the values of the drop downs would have to be hard coded into the HTML.

Not sure how you plan to do this as far as process. (Could be javascript, could be submission to the "next" page via asp. Going to depend alot on that.

Here is some ASP on listing column names in a record set though (That you could get by querying one of your tables).

Expand|Select|Wrap|Line Numbers

 
for each f in RS.Fields

   Response.Write(f.Name & "<br>")

next

May 19 '08 #7

I thought of it as being done in the user browser where i hardcode the field names after which i'll pass the request to the an asp page which would search the specified table field for the relavant data.

May 19 '08 #8

432

Expert 256MB

I thought of it as being done in the user browser where i hardcode the field names after which i'll pass the request to the an asp page which would search the specified table field for the relavant data.

What I was just trying to say was that if you want to choose a table, then have a Second drop down list populate with the columns from that table without a reload of the page you would need to use javascript.

Ok. However if you are not using the database to pull your field names (using below code from a recordset) from if anything ever changes, keep in mind you will also then have to change your asp page <option> values.

Expand|Select|Wrap|Line Numbers

 
for each f in RS.Fields

   Response.Write("<option>" & f.Name & "</option>")

next

In that case though since you just want to grab a value from a form this should be relatively simple.

On your search.asp page simply get the values of the combo box's into an SQL string that produces the search results. The below code assumes you have a connection string already set up.

Expand|Select|Wrap|Line Numbers

 
'for search.asp

dim MyTable,StrSQL

dim MyRS

Set MyRS = server.CreateObject("Adodb.Recordset")

MyTable = request.form("Table") 'this is your combo box (drop down)

StrSQL = "Select * from " & MyTable & ""

MyRS.Open strSQL, DataConnection

Is this what you mean?

May 19 '08 #9

I do appriciate you responding promptly to my post with insightfull help and ideas.

The java script function is what i think i need help with. If you noticed in the html codes i sent, there are two combo boxes; one named table which holds the tables in the db and the other named fields which should hold the fields in the selected table only. i want to select a table and have a script deliver the fields straight to combo box where i can then select the field, type the search text and click submit to call the search.asp page to do it's thing.
the function should do client side processing rather than server side

May 19 '08 #10

Using Access+DBMS with >30 users

432

Expert 256MB

Ok. Well that is a javascript function that is available in a few places. This is one I have used alot that is an excellent example of how to do something like that.

Javascript Kit Drop Down Example

Here is the modified version of that showing how you would use it. Be aware however that this example is set up to redirect the user on the last selection to a website value. Instead just have your button click submit the form.

Expand|Select|Wrap|Line Numbers

 
<form name="MyForm">

<select name="MyTables" size="1" onChange="displaysub()">

<option value="#">This is a place Holder text </option>

</select>

<input type="button" name="test" value="Go!"

onClick="gothere()">

</form>
 
<script>

<!--
 
//MAIN DROP DOWN
 
var category=new Array()

category[0]=new Option("SELECT A TABLE ", "") 

category[1]=new Option("TABLE1 NAME", "combo1")

category[2]=new Option("TABLE2 NAME", "combo2")

category[3]=new Option("TABLE3 NAME", "combo3")
 
//SUB DROP DOWNS
 
var combo1=new Array()

combo1[0]=new Option("COLUMN NAME FROM TABLE 1","WHATEVERVALUE")

combo1[1]=new Option("BACK TO TABLES","")   
 
var combo2=new Array()

combo2[0]=new Option("COLUMN NAME FROM TABLE 2","WHATEVERVALUE")

combo2[1]=new Option("BACK TO TABLES","")  
 
var combo3=new Array()

combo3[0]=new Option("COLUMN NAME FROM TABLE 3","WHATEVERVALUE")

combo3[1]=new Option("BACK TO TABLES","")  
 
var curlevel=1

var cacheobj=document.MyForm.MyTables
 
function populate(x){

for (m=cacheobj.options.length-1;m>0;m--)

cacheobj.options[m]=null

selectedarray=eval(x)

for (i=0;i<selectedarray.length;i++)

cacheobj.options[i]=new Option(selectedarray[i].text,selectedarray[i].value)

cacheobj.options[0].selected=true
 
}
 
function displaysub(){

if (curlevel==1){

populate(cacheobj.options[cacheobj.selectedIndex].value)

curlevel=2

}

else

gothere()

}
 
function gothere(){

if (curlevel==2){

if (cacheobj.selectedIndex==cacheobj.options.length-1){

curlevel=1

populate(category)

}

else

location=cacheobj.options[cacheobj.selectedIndex].value

}

}
 
populate(category)
 
//-->

</script>

May 19 '08 #11

Similar topics

by: Remco Groot Beumer | last post by:

Hello, I'm trying to decide if the following situation would be workable: Generate an MS Access Front End (which will run localy on client computers), which will link to a DBMS (SQL server or...

.NET Framework

Problem using Access 2000 as a front-end to SQL Server 2000 tables

by: dananrg | last post by:

I've created a small company database where the tables reside in a SQL Server database. I'm using Access 2000 forms for a front end. I've got a System DSN set-up to SQL Server and am using links...

Microsoft SQL Server

Limits of Access 2003 using ADP on SQL Server Express BE?

by: Matt Alanzo | last post by:

On another newsgroup an Access knowledgable party posted: >You should be able to connect an Access ADP to an existing SQLExpress >database running in SQLS 2000 compatibility mode. The only thing...

Access 2k3 using SQL Server 2000: will go very slow after several hours

by: B Moor | last post by:

Host PC: Win SBS 2003 with 2.6 GHz processor and 1GB RAM SQL Server 2000 sp3 (or latest).MS Office 2k3 I have the access 2k3 front end running on server (this may get changed) and all was well...

SQL using IF THEN ELSE

by: Carl | last post by:

Hi, Using MS Access 2000, is it possible to run a UPDATE or INSERT SQL query using some form of conditional IF THEN ?? for example: SELECT * FROM Books IF EXISTS(Select Books.ID = 1)

Using Access/Jet on Web site

by: Serious_Practitioner | last post by:

Good day all, and thank you in advance for your help. No - MANY thanks in advance for your help - I know nothing about using databases on Web servers. I am about to discuss a project with a...

Windows Forms No-Touch Deployment problems using DAO on User-Level Secured Access DB to access groups

by: James | last post by:

I have a VB windows forms application that accesses a Microsoft Access database that has been secured using user-level security. The application is being deployed using No-Touch deployment. The...

Visual Basic .NET

How to get recordcount or number of records when using linked tables

by: Robert | last post by:

How do you get an accurate count of the number of records returned from a query when using linked tables. I have an access 2003 database as a front end to another access 2003 database that...

Access 2003 database will not open using Access 2002

by: Bugs | last post by:

Hi everyone. I am trying to open a database which works fine using Access 2003, but when trying to open it on another PC that has Access 2002 I get the following error "This database is...