vbscript & database access

I am a newbie when comes to trying vbscript & database access (via
ODBC or ADO).

So where does one start? Does this require HTA scripting, or can this
be done directly with vbs? I am a developer who is profficient with
vbs and understanding code. Where is a good place to start?

Thanks

Richard
Wed Sep 05 15:25:47 PDT 2007


"Cyberdude" <pnjtlxhzhgjb@spammotel.com> wrote in message
news:1189029272.146777.128120@k79g2000hse.googlegroups.com...
>I am a newbie when comes to trying vbscript & database access (via
> ODBC or ADO).
>
> So where does one start? Does this require HTA scripting, or can this
> be done directly with vbs? I am a developer who is profficient with
> vbs and understanding code. Where is a good place to start?
>
> Thanks
>

I do this in VBScript all the time. The trick is the connection string,
which varies with DBMS. This article should help:

http://support.microsoft.com/kb/168336

An example connecting to SQL Server:
========
' Connection SQL Server database.
strConnect = "DRIVER=SQL Server;" _
& "Trusted_Connection=Yes;" _
& "DATABASE=MyDatabaseName;" _
& "SERVER=MyServer\MyInstance"

' Connect to database.
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.ConnectionString = strConnect
adoConnection.Open

Set adoRecordset = CreateObject("ADODB.Recordset")
adoRecordset.ActiveConnection = adoConnection

adoPLRecordset.Source = "SELECT Value1, Value2 " _
& "FROM dbo.MyTable " _
& "WHERE Value3 = 25"
adoRecordset.Open
Do Until adoRecordset.EOF
strValue1 = adoRecordset.Fields("Value1").Value
strValue2 = adoRecordset.Fields("Value2").Value
Wscript.Echo strValue1 & ";" & strValue2
adoRecordset.MoveNext
Loop
adoRecordset.Close
adoConnection.Close
==========
An example connecting to a Microsoft Access database:
==============
strAppPath = "c:\MyFolder\"
strDBName = "MyDatabase.mdb"

' Connection string for Access database.
strConnect = "DRIVER=Microsoft Access Driver (*.mdb);" _
& "FIL=MS Access;DriverId=25;DefaultDir=" _
& strAppPath & ";DBQ=" & strAppPath & strDBName

Set adoJetConnection = CreateObject("ADODB.Connection")
adoJetConnection.ConnectionString = strConnect
adoJetConnection.Open

Set adoRecordset = CreateObject("ADODB.Recordset")
adoRecordset.ActiveConnection = adoJetConnection

strSQL = "SELECT Field1, Field1 " _
& "FROM MyTable"

adoRecordset.Source = strSQL
adoRecordset.Open

Do Until adoRecordset.EOF
strField1 = adoRecordset.Fields("Field1").Value
strField2 = adoRecordset.Fields("Field2").Value
Wscript.Echo strField1 & ";" & strField2
adoRecordset.MoveNext
Loop

adoRecordset.Close
adoJetConnection.Close

--
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
--

Paul
Wed Sep 05 15:35:39 PDT 2007


"Cyberdude" <pnjtlxhzhgjb@spammotel.com> wrote in message
news:1189029272.146777.128120@k79g2000hse.googlegroups.com...
>I am a newbie when comes to trying vbscript & database access (via
> ODBC or ADO).
>
> So where does one start? Does this require HTA scripting, or can this
> be done directly with vbs? I am a developer who is profficient with
> vbs and understanding code. Where is a good place to start?
>
> Thanks

You can do an amazing amount of stuff with ADO, like create access MDB,
excel multi-sheet XLS, and plain text CSV files, and extract and perhaps add
data to these files using SQL statements, all without even having Access or
Excel installed. You can do this with plain old VBS scripts with no user
interface or which use Internet Explorer as the user interface, or you can
script withing an HTA.

If you would like a VBS example, groups.google for a thread titled "Convert
CSV file to multiple worksheets". In one branch of this thread,
ekkehard.horner presented a script that extracts information from the
northwind access database, creating a CSV file, and then extracts info from
the CSV file into an array, and then builds a two-sheet XLS file. Read the
whole branch -- the original script uses the NordWind database which is not
in the English language; subsequent postings show how to correct the
language problems and download the English version, NWind.mdb.

ADO http://msdn2.microsoft.com/en-us/library/ms675944.aspx
ADOX http://msdn2.microsoft.com/en-us/library/ms681520.aspx
Scripting-related downloads:
http://www.microsoft.com/downloads/Browse.aspx?displaylang=en&productID=478EA476-5552-479E-A200-2C33FFD43F24
HTML document object model:
http://msdn2.microsoft.com/en-us/library/ms533050.aspx

-Paul Randall