By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,154 Members | 1,024 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,154 IT Pros & Developers. It's quick & easy.

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

P: 2
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:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO StudentProgress ( StudentID, StudentName, eMailAddr, TicketNo, Passwd, StartTime, CompletedTime, Status, CourseNum, CourseName, CourseShortName, NoOfSections, CourseSectionName, CourseSectionShortName, CourseSectionLength, VidFilename, VidName, SectionNum )
  2. 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
  3. FROM VidStudents INNER JOIN (Courses INNER JOIN CourseSections ON Courses.CourseNum = CourseSections.CourseNum) ON VidStudents.CourseNum = CourseSections.CourseNum
  4. WHERE (((VidStudents.Setup)=No));
Most of the fields are text fields. The exceptions:

Date fields: StartTime, CompletedTime, and CourseSectionLength
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!
Jan 16 '08 #1
Share this Question
Share on Google+
2 Replies


codegecko
Expert 100+
P: 533
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):
Expand|Select|Wrap|Line Numbers
  1. Set myconn = Server.CreateObject("ADODB.Connection")
  2. strconn="Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("/your_web_directory_to_your/database.mdb")
  3. myconn.open strconn
  4.  
  5. Set mycomm = Server.CreateObject("ADODB.Recordset")
  6. strSQL = "SELECT WhateverFields, * FROM YourTable WHERE Criteria = 'Whatever';"
  7. mycomm.open strSQL, myconn, 1, 1
  8.  
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:
Expand|Select|Wrap|Line Numbers
  1. Set mycomm = Server.CreateObject("ADODB.Command")
  2. mycomm.CommandText="name_of_your_query"
  3. mycomm.CommandType = adCmdStoredProc
  4. mycomm.Execute
  5.  
And that should run the query in your database!

Best regards,

medicineworker
Jan 16 '08 #2

P: 2
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,
troutmansregistrar
Jan 16 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.