How can I query a MS Access database in VBScript? 1

[JohnnyBGoode]

Is it possible to read from an MS Access 2000 database from VBScript? I have created an Access Query ("TestQuery"), but how do I call that query? Is there a better way to read from an Access database?

Below is how I would call a SQL stored proc from VBScript:

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.ConnectionString "dsn=dsnName;uid=userID;pwd=userPWD"
oConn.Open
Set oCmd = Server.CreateObject("ADODB.Command")
Set oRs = Server.CreateObject("ADODB.Recordset")
oCmd.ActiveConnection = oConn
oCmd.CommandType = adCmdStoredProc
oCmd.CommandText = "sp_return_dates"
Set oRs = oCmd.Execute()
...
...
...
oRs.Close()
set oRs = nothing
oConn.close
set oConn = nothing

 

[PHV]

Do a keyword search in this forum for stored procedure

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mapman04]

Here's a piece of code I use to query an Access 2000 database.

Set db = WScript.CreateObject("ADODB.Connection")
dbStr = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & strDB & ";Uid=Admin;Pwd=;"
db.Open dbStr

strSQL = "Select Distinct Shipping.Shpmnt_No "
strSQL = strSQL & "From Shipping Where (((SHIPPING.CUST_ID)='CUSTOMER') And "
strSQL = strSQL & "((SHIPPING.SHPMT_DATE)=#" & SOMEDATE & "#));"

Set rst = db.Execute(strSQL, ,adCmdText)


Hope this helps,

mapman04

 

[JohnnyBGoode]

Is the "Driver" the database Name? What is the "Dbq" then?

 

[mapman04]

I'm not sure what the Dbq is but it is part of the whole connection string. I set the strDB to the database I want to open like "\\Server\Databases\Customer\adbCustomer.mdb".

Sorry for any confusion.

mapman04

 

[MakeItSo]

Hi Mapman04

There is a free tool I couldn't do without:

http://www.mztools.com/

It consists of two independent add-ons. One for VisualBasic, one for VBA. If you install it, you can open any VBA-Editor, click on Tools - MZ-Tools - Other auxiliaries - ADO Connection String.

You just choose Provider and Database and the ready ConnectionString is copied to Clipboard from where you can paste it to your VBScript Code.

P.S: The other feats are of course excellent, too.. )

Cheers,
MakeItSo

[blue]The last voice we will hear before the world explodes will be that of an expert saying:
"This is technically impossible!" - Sir Peter Ustinov[/blue]
HP:

http://andygalambos.de

 

[JohnnyBGoode]

One last question, mapman04...what kind of object did you define "WScript" as?

 

[JohnnyBGoode]

I spoke too soon. I've figured it out. Thanx a lot you guys.

 

[mapman04]

Here's the initial part of my code. I'm also writing steps to a text file.

Regards,

mapman04

========= Code ==================

pLog = DateStamp()
strDT = Date()
qDT = Date()-1
strDB = "\\Server\Database\Customer\adbCustomer.mdb"
strLog = "\\Server\Database\Logs\Customer_Log_" & pLog & ".Txt"
strDir = "\\Server\Database\Customer\"
shFlag = "F"

Set wSH = WScript.CreateObject("WScript.Shell")
Set fso=WScript.CreateObject("Scripting.FileSystemObject")
Set txt1 = fso.CreateTextFile(strLog,True)

txt1.WriteLine("Customer Update Log")
txt1.WriteLine("Date: " & Date())
txt1.WriteLine(" ")
txt1.WriteLine("Process started at " & Time())
txt1.WriteLine(" ")

Set db = WScript.CreateObject("ADODB.Connection")
dbStr = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & strDB & ";Uid=Admin;Pwd=;"
db.Open dbStr

txt1.WriteLine("Checking for new shipments...")
txt1.WriteLine(" ")

strSQL = "Select Distinct Shipping.Shpmnt_No "
strSQL = strSQL & "From Shipping Where (((SHIPPING.CUST_ID)='CUSTS') And "
strSQL = strSQL & "((SHIPPING.SHPMT_DATE)=#" & qDT & "#));"

Set rst = db.Execute(strSQL, ,adCmdText)