This is my tutorial on how to build a query that you can use in VBScript to extract information from a database.
The level of difficulty for this tutorial is beginner to intermediate.
You need to know basic VBScript syntax and need to have a copy of MS Access.
Pay attention to the SQL_query parts in the following blocks of code.
This is the code syntax for ASP Note this code is striped down to the most basic HTML for demonstration purposes.
<!--demo.asp-->
<html>
<Title> Read Database Tutorial - Using a query you built yourself </Title>
<body>
<h2>Total Jobs Each User - Sorted by Largest Total First.</h2><br>
<TABLE border="1">
<TR>
<TD><B>User</a></B></TD>
<TD><B>Total</a></B></TD>
</TR>
<%
Set MyConn = Server.CreateObject("ADODB.Connection")
MdbFilePath = Server.MapPath("PrinterCounts.mdb")
MyConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath & ";"
SQL_query = "SELECT First([PrnCounts].[DomainUser]) AS FirstOfDomainUser, Sum([PrnCounts].[NumberPages]) AS SumOfNumberPages "&_
"FROM PrnCounts "&_
"GROUP BY [PrnCounts].[DomainUser] "&_
"HAVING (((Count(PrnCounts.DomainUser))>1 Or (Count(PrnCounts.DomainUser))=1)) "&_
"ORDER BY Sum([PrnCounts].[NumberPages]) DESC;"
Set RS = MyConn.Execute(SQL_query)
WHILE NOT RS.EOF
%>
<TR>
<TD><%=RS("FirstOfDomainUser")%></TD>
<TD><%=RS("SumOfNumberPages")%></TD>
</TR>
<%
RS.MoveNext
WEND
RS.Close
set RS = nothing
MyConn.close
set MyConn = nothing
%>
</TABLE>
</body>
</html>
This is the Code Syntax for WSH. Notice I have removed instances of "Server." and "MapPath" from the script.
I have also removed the HTML stuff.
'demo.vbs
wscript.echo "Total Jobs Each User - Sorted by Largest Total First"
Set MyConn = CreateObject("ADODB.Connection")
MdbFilePath = "PrinterCounts.mdb"
MyConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath & ";"
SQL_query = "SELECT First([PrnCounts].[DomainUser]) AS FirstOfDomainUser, Sum([PrnCounts].[NumberPages]) AS SumOfNumberPages "&_
"FROM PrnCounts "&_
"GROUP BY [PrnCounts].[DomainUser] "&_
"HAVING (((Count(PrnCounts.DomainUser))>1 Or (Count(PrnCounts.DomainUser))=1)) "&_
"ORDER BY Sum([PrnCounts].[NumberPages]) DESC;"
Set RS = MyConn.Execute(SQL_query)
WHILE NOT RS.EOF
Wscript.echo "User" & RS("FirstOfDomainUser") &_
" Count" & RS("SumOfNumberPages")
RS.MoveNext
WEND
RS.Close
set RS = nothing
MyConn.close
set MyConn = nothing
Did you understand all that?? Don't worry if you didn't, because you don't need to understand it.
These blocks of code seem complex but all you have to understand is that they are templates with an SQL query.
There are many different examples of creating databases and tables, and reading, writing and deleting records on the Internet.
The only hard thing is the SQL query itself but I will now show you how I easily get the query statement from MS Access.
(These screen shots were taken from Access 2000)
For a full example of the possibilities of this script, plus code that creates the database and populates_
it with data from the print server's event logs can be found here.
http://visualbasicscript.com/m_58935/tm.htm
<message edited by TomRiddle on Friday, April 11, 2008 11:02 PM>