Login or Sign Up to become a member!

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

From Wiki

It is possible to create queries in Access and use them in ASP. Here is some sample ASP. You will probably wish to remove the comments.

   <%@ Language=VBScript %>
   <%
   Option Explicit
   
   'A bit of storage space.
   Dim cmd, rs, cn, recs, i
   
   'Some constants to make life easier.
   Const adCmdStoredProc = 4
   Const adParamInput = 1
   Const adVariant = 12
   Const adInteger = 3
   Const adExecuteNoRecords  = 128
   
   'Set up a command object
   Set cmd = Server.CreateObject ("ADODB.Command")
   
   'The connection string
   cn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &_
         Server.MapPath("..\LTD.mdb") & ";Persist Security Info=False"
   
   'It would be better to use a connection object if you have
   'a lot of commands to run, but here is a connection for the command ...
   cmd.ActiveConnection = cn
   
   '... a stored query ...
   
   '==========================
   'SQL for stored query
   ' PARAMETERS [Key] Short, [NewData] Text ( 255 );
   ' UPDATE tblSampleFields t
   ' SET t.aText = [NewData]
   ' WHERE t.aAuto=[Key]
   '==========================
   cmd.CommandText = "updLTD"
   
   ' ... yep, stored query ...
   cmd.CommandType = adCmdStoredProc
   
   ' ... and some parameters.
   'http://www.w3schools.com/ADO/met_comm_createparameter.asp
   'Make sure you get the type right, you will find details here:
   'http://www.w3schools.com/ADO/ado_datatypes.asp
   'You will find direction here:
   'http://www.w3schools.com/ado/prop_para_direction.asp
   'Key, NewData : Name
   'adInteger, adVariant : Type
   'adParamInput : Direction
   '50 : Size
   '2, 'New Data' : Value
   
   cmd.Parameters.Append cmd.CreateParameter("Key", adInteger, adParamInput, , 2)
   cmd.Parameters.Append cmd.CreateParameter("NewData", adVariant, adParamInput, 50, "New data")
   
   'Off we go.
   'recs : return for records affected
   'adExecuteNoRecords : no records are returned by this query, so this increases efficiency
   'http://www.w3schools.com/ADO/ado_ref_command.asp
   cmd.Execute recs,,adExecuteNoRecords
   
   'Did it work?
   Response.Write "Records updated: " & recs & "<br><br>"
   
   
   'Another command, but with a select query
   '==========================
   'SQL for stored query
   ' PARAMETERS [Key] Short;
   ' SELECT t.aAuto, t.aText
   ' FROM tblSampleFields AS t
   ' WHERE t.aAuto=[Key];
   '==========================
   cmd.CommandText = "qryLTD"
   cmd.CommandType = adCmdStoredProc
   cmd.Parameters.Append cmd.CreateParameter ("Key",adInteger,adParamInput ,,1)
   
   'This one goes to a recordset, so we can
   'get the data
   Set rs = Server.CreateObject ("ADODB.Recordset")
   Set rs = cmd.Execute
   
   'List of fields (not for real life)
   For i=0 To rs.Fields.Count-1
   	Response.Write rs.fields(i).Name & " "
   Next
   Response.Write "<br>"
   
   'List of records
   Do While Not rs.EOF
   
       For i=0 To rs.Fields.Count-1
          Response.Write rs.fields(i) & " "
       Next
       rs.MoveNext
   
       Response.Write "<br>"
   
   Loop
   %>