Query Access Database with VBScript

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Query Access Database with VBScript

Logged in as: Guest

arrSession:exec spGetSession 2,18,59038

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript Tutorial >> Query Access Database with VBScript
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

Query Access Database with VBScript - 4/11/2008 1:35:52 PM

TomRiddle

Posts: 165
Score: 4
Joined: 2/7/2008
Status: offline

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.

<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
   %>

<%
   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.

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

Attachment (1)

< Message edited by TomRiddle -- 4/11/2008 10:02:28 PM >

Post #: 1

RE: Query Access Database with VBScript - 4/11/2008 5:37:27 PM

ehvbs

Posts: 2064
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi TomRiddle,

thanks for the nice tutorial. One nit to pick:

This:

WEND
MyConn.close
set RS = nothing

should be:                                                          or:

WEND                                                              WEND
RS.Close                                                          RS.Close
   MyConn.close                                                  set RS = nothing ' optional
set RS = nothing ' optional                             MyConn.close
   set MyConn = nothing ' optional                    set MyConn = nothing ' optional

Regards

ehvbs

(in reply to TomRiddle)