How do I convert a working MS Access append (action) query to VBscript for ASP

[TroutmansRegistrar]

I have a web programming task that has me stumped. I hope that one of you could give me some insight or might know someone who could guide me in the right direction. This is the final piece of the web application that my employer wanted finished by January 1st.

Perhaps my biggest problem is that I can’t seem to formulate the right search-engine search question to find the utility I need nor can I find any reference to the real issue. In short, I don’t know where to turn for help.

This is what I’ve got:

I have a MS Access 2003 append (action) query that reads data from three related tables and adds the data in rows to a fourth table. There are criteria-based relationship joins on each of the 3 original data tables. The purpose of the query is to insert as few as 3 or as many as 100 records in the StudentProgress table for each qualifying record in the VidStudents table. The query runs perfectly as expected when I click on it in Access. The tables and query are all combined in one Access application.

What I need:

The trouble is that the application runs on an ISS server running ASP. I, obviously, cannot click on the query to activate it, and I don’t know how to call it as a procedure (I gather from the documentation that calling it as a procedure is not possible.) I need something that will make it run on the server or do the same thing on the server that it does when it’s on my machine.

Here is the SQL query from Access:

INSERT INTO StudentProgress ( StudentID, StudentName, eMailAddr, TicketNo, Passwd, StartTime, CompletedTime, Status, CourseNum, CourseName, CourseShortName, NoOfSections, CourseSectionName, CourseSectionShortName, CourseSectionLength, VidFilename, VidName, SectionNum )
SELECT VidStudents.StudentID, VidStudents.StudentName, VidStudents.eMailAddr, VidStudents.TicketNo, VidStudents.Passwd, VidStudents.StartTime, VidStudents.CompletedTime, IIf(CourseSections.InitialSection=Yes,"Ready","Not Ready") AS Status, Courses.CourseNum, Courses.CourseName, Courses.CourseShortName, Courses.NoOfSections, CourseSections.CourseSectionName, CourseSections.CourseSectionShortName, CourseSections.CourseSectionLength, CourseSections.VidFilename, CourseSections.VidName, CourseSections.SectionNum
FROM VidStudents INNER JOIN (Courses INNER JOIN CourseSections ON Courses.CourseNum = CourseSections.CourseNum) ON VidStudents.CourseNum = CourseSections.CourseNum
WHERE (((VidStudents.Setup)=No));

Most of the fields are text fields. The exceptions:

Date fields: StartTime, CompletedTime, and CourseSectionLe ngth
Integer fields: NoOfSections and SectionNum

The solution to this is probably extremely simple. I just don't know where to turn. Any guidance and all suggestions will be appreciated. Thanks!

[JamieHowarth0]

Hi there,

Firstly, welcome to TSDN!

Secondly, calling your query as a procedure, contrary to popular belief, IS possible in MS Access (technically a "query" is a stored procedure)!

There are a couple of ways of connecting to and using SQL with a database in classic ASP - the most common one being the two-pronged connect-and-query approach (demonstrated here):

Set myconn = Server.CreateObject("ADODB.Connection")
strconn="Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("/your_web_directory_to_your/database.mdb")
myconn.open strconn
 
Set mycomm = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT WhateverFields, * FROM YourTable WHERE Criteria = 'Whatever';"
mycomm.open strSQL, myconn, 1, 1
 

Now, to enable ASP to connect to and not just read, but update information within your database, you need to give the IUSR_MACHINE account read and write permissions on your database through IIS.

Then you can either attempt to replace your SQL code in the above bit of code, or, if you can't be bothered to copy and paste the SQL code in, use the same bit of code, but replace lines 5-7 with this:

Set mycomm = Server.CreateObject("ADODB.Command")
mycomm.CommandText="name_of_your_query"
mycomm.CommandType = adCmdStoredProc
mycomm.Execute
 

And that should run the query in your database!

Best regards,

medicineworker

[TroutmansRegistrar]

Thank you, medicineworker!

That worked perfectly! It was fast and simple. I used the first method and it worked. I may try the second method so that I can dynamically change the query if it becomes necessary.

Now I feel as if I know how to run a computer again. Of course, I am sure something will bite me, and I'll be crying again soon. In the meantime, I'll try to see if I can help someone out here as much as you have helped me.

Yours,
troutmansregist rar