General discussion

Locked

Access database query w criteria on web

By oidtms ·
I am trying to set up queries on our web page to an Access database. I need to be able to specify criteria. e.g. start date and end date Has anyone out there found a successful way of doing this? If so, please enlighten me.

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access database query w criteria on web

by roc180 In reply to Access database query w c ...

To start I take it you have your Database somewhere where your web server can see it. At that point all you need to do is use ADO to establ;ish a connection to your DB and then query your DB. Some example code may look like:

<%@ LANGUAGE="VBSCRIPT" %>
<%
Dim Conn
Dim StrSQL
Dim dtCrite

Set Conn=Server.CreateObject("ADODB.Connection")

Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Path to DB"

Set rst=Server.CreateObject("ADODB.recordset")

dtCrite=Request("txtDate") 'Where txtDate is input by user on a form

StrSQL="SELECT * FROM TBL WHERE Date1=#"& dtCrite &"# ;"

Rst.Open strSQL,Conn

'Do something with Rst

Rst.close
Set Rst=Nothing
Conn.Close
Set Conn=Nothing
%>

Good Luck

Neal Martinelli

Collapse -

Access database query w criteria on web

by oidtms In reply to Access database query w c ...

Poster rated this answer

Collapse -

Access database query w criteria on web

by Glen_McLeod In reply to Access database query w c ...

The above will work, but there are a couple of more scaleable and faster performing solutions.

1. Instead of hard coding your SQL in the ASP page, create the equivalent query in your database, then use ADO parameters to send just the parameters to the database, instead of the whole SQL string. Pre-compiled queries execute (on average) about 5 times faster than ad-hoc SQL statements.

set Cmd=Server.CreateObject("ADODB.Command")
set Cmd.ActiveConnection = Conn
Cmd.CommandType=adStoredProc
Cmd.CommandText="Your query name here"
Cmd.Parameters.Append Cmd.CreateParameter("Name",Type, Direction, Length)

dtCrite=Request("txtDate") 'Where txtDate is input by user on a form

Cmd.Parameters("Name").Value = dtCrite

Set rst=Server.CreateObject("ADODB.recordset")

rst.Open Cmd

There's a little more code to type, but the performance gain is easily worth it.

2. Put the data access code into a compiled DLL on your Web server, and call methods on the DLL.

Personally I would combine the two, using the pre-compiled query with the Command object Parameters, and compile the whole thing into a DLL.

Glen

Collapse -

Access database query w criteria on web

by oidtms In reply to Access database query w c ...

Poster rated this answer

Collapse -

Access database query w criteria on web

by oidtms In reply to Access database query w c ...

This question was closed by the author

Back to Desktop Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums