Manufacturing Information Solutions Forum Index Manufacturing Information Solutions
Your Place for Support and Discussions
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Working with Databases (Many code samples)

 
Post new topic   Reply to topic    Manufacturing Information Solutions Forum Index -> VB Script
View previous topic :: View next topic  
Author Message
mistux
Site Admin


Joined: 25 Jun 2004
Posts: 1042
Location: South Bend, Indiana USA

PostPosted: Fri May 02, 2008 1:00 pm    Post subject: Working with Databases (Many code samples) Reply with quote

Source:
http://www.vbsedit.com/scripts/misc/database/default.asp

(They have a very good VB Script Editor, check it out.)
=============================================

' Add a New Record to a Table


Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " &; _
"Data Source = inventory.mdb"

objRecordSet.Open "SELECT * FROM GeneralProperties" , _
objConnection, adOpenStatic, adLockOptimistic

objRecordSet.AddNew
objRecordSet("ComputerName") = "atl-ws-01"
objRecordSet("Department") = "Human Resources"
objRecordSet("OSName") = "Microsoft Windows XP Professional"
objRecordSet("OSVersion") = "5.1.2600"
objRecordSet("OSManufacturer") = "Microsoft Corporation"
objRecordSet.Update

objRecordSet.Close
objConnection.Close
======================================

' Clear a Database Table


Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DSN=Inventory;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "Delete * FROM Hardware" , objConnection, _
adOpenStatic, adLockOptimistic
objConnection.Close
============================

' Connect to a SQL Server Database


Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider=SQLOLEDB;Data Source=atl-sql-01;" &; _
"Trusted_Connection=Yes;Initial Catalog=Northwind;" &; _
"User ID=fabrikam\kenmyer;Password=34DE6t4G!;"

objRecordSet.Open "SELECT * FROM Customers", _
objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Wscript.Echo objRecordSet.RecordCount
=============================

' Connect to an ADO Database


Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

objConnection.Open "DSN=Inventory;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM Hardware" , objConnection, _
adOpenStatic, adLockOptimistic
objRecordset.Close
objConnection.Close
============================

' Create a JET Database


Set objConnection = CreateObject("ADOX.Catalog")

objConnection.Create _
"Provider = Microsoft.Jet.OLEDB.4.0; " &; _
"Data Source = new_db.mdb"
===========================

' Create a Table in a JET Database


Set objConnection = CreateObject("ADODB.Connection")

objConnection.Open _
"Provider= Microsoft.Jet.OLEDB.4.0; " &; _
"Data Source=new_db.mdb"

objConnection.Execute "CREATE TABLE EventTable(" &; _
"EventKey COUNTER ," &; _
"Category TEXT(50) ," &; _
"ComputerName TEXT(50) ," &; _
"EventCode INTEGER ," &; _
"RecordNumber INTEGER ," &; _
"SourceName TEXT(50) ," &; _
"TimeWritten DATETIME ," &; _
"UserName TEXT(50) ," &; _
"EventType TEXT(50) ," &; _
"Logfile TEXT(50) ," &; _
"Message MEMO)"

objConnection.Close
===========================

' Delete a Record from a Recordset


Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

objConnection.Open "DSN=Inventory;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM Hardware" , objConnection, _
adOpenStatic, adLockOptimistic
strSearchCriteria = "ComputerName = 'WebServer'"
objRecordSet.Find strSearchCriteria
objRecordset.Delete
objRecordset.Close
objConnection.Close
=========================

' Delete Multiple Records from a Table


Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " &; _
"Data Source = inventory.mdb"

objRecordSet.Open "DELETE * FROM GeneralProperties WHERE " &; _
"Department = 'Human Resources'", _
objConnection, adOpenStatic, adLockOptimistic

objConnection.Close
=======================

' List Basic Statistics Derived from a Recordset


Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " &; _
"Data Source = inventory.mdb"

objRecordSet.Open "SELECT OSName, Count(OSName) AS CountOfOSName" &; _
" FROM GeneralProperties GROUP BY OSName ORDER BY Count(OSName) DESC", _
objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordset.EOF
Wscript.Echo objRecordset.Fields.Item("OSName") &; _
vbTab &; objRecordset.Fields.Item("CountOfOSName")
objRecordset.MoveNext
Loop

objRecordSet.Close
objConnection.Close
=============================

' List the Number of Records in a Recordset


Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " &; _
"Data Source = eventlogs.mdb"

objRecordSet.Open "SELECT * FROM EventTable" , _
objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Wscript.Echo "Number of records: " &; objRecordset.RecordCount

objRecordSet.Close
objConnection.Close
===========================

' List the Top 25 Records in a Recordset


Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " &; _
"Data Source = inventory.mdb"

objRecordSet.Open "SELECT TOP 25 * FROM GeneralProperties " &; _
"ORDER BY TotalPhysicalMemory DESC", _
objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordset.EOF
Wscript.Echo objRecordset.Fields.Item("ComputerName") &; _
vbTab &; objRecordset.Fields.Item("TotalPhysicalMemory")
objRecordset.MoveNext
Loop

objRecordSet.Close
objConnection.Close
==========================

' List Unique Records in a Recordset


Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " &; _
"Data Source = inventory.mdb"

objRecordSet.Open "SELECT DISTINCT OSName FROM " &; _
"GeneralProperties ORDER BY OSName", _
objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordset.EOF
Wscript.Echo objRecordset.Fields.Item("OSName")
objRecordset.MoveNext
Loop

objRecordSet.Close
objConnection.Close
==========================

' Open a Database Using a DSN


Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Northwind;fabrikam\kenmyer;34ghfn&!j"

objRecordSet.Open "SELECT * FROM Customers", _
objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Wscript.Echo objRecordSet.RecordCount
============================

' Open Two Recordsets


Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
Set objRecordSet2 = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider= Microsoft.Jet.OLEDB.4.0; " &; _
"Data Source=inventory.mdb"

objRecordSet.Open "SELECT * FROM GeneralProperties Where ComputerName = 'Computer1'", _
objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst


objRecordSet2.Open "SELECT * FROM Storage Where ComputerName = 'Computer1'", _
objConnection, adOpenStatic, adLockOptimistic

objRecordSet2.MoveFirst

Do Until objRecordset.EOF
Wscript.Echo objRecordset.Fields.Item("ComputerName")
Wscript.Echo objRecordset.Fields.Item("OSName")
objRecordSet.MoveNext
Loop

Do Until objRecordset2.EOF
Wscript.Echo objRecordset2.Fields.Item("DriveName"), _
objRecordset2.Fields.Item("DriveDescription")
objRecordSet2.MoveNext
Loop

objRecordSet.Close
objRecordSet2.Close
objConnection.Close
========================

' Save a Recordset in XML format


Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adPersistXML = 1

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider= Microsoft.Jet.OLEDB.4.0; " &; _
"Data Source=eventlogs.mdb"
objRecordSet.Open "SELECT * FROM EventTable" , _
objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

objRecordSet.Save "output.xml", adPersistXML

objRecordSet.Close
objConnection.Close
=========================

'Search a Database Using a LIKE Query

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " &; _
"Data Source = eventlogs.mdb"

objRecordSet.Open "SELECT * FROM EventTable WHERE " &; _
"Message Like '%PowerPoint%'", _
objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Wscript.Echo "Number of records: " &; objRecordset.RecordCount

objRecordSet.Close
objConnection.Close
========================

' Search a Database Using Numeric Criteria


Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " &; _
"Data Source = eventlogs.mdb"

objRecordSet.Open "SELECT * FROM EventTable " &; _
"WHERE EventCode = 1054", _
objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Wscript.Echo "Number of records: " &; objRecordset.RecordCount

objRecordSet.Close
objConnection.Close
=========================

' Search a Database Using Variable Criteria


Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider= Microsoft.Jet.OLEDB.4.0; " &; _
"Data Source=eventlogs.mdb"

dtmDate = "#1/7/2004#"

objRecordSet.Open "SELECT * FROM EventTable Where TimeWritten = " &; dtmDate, objconnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordSet.EOF
Wscript.Echo objRecordset.Fields.Item("EventCode") &; vbTab _
&; objRecordset.Fields.Item("Logfile")
objRecordSet.MoveNext
Loop
=======================

' Search for a Record in a Recordset


Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

objConnection.Open "DSN=Inventory;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM Hardware" , objConnection, _
adOpenStatic, adLockOptimistic
strSearchCriteria = "ComputerName = 'WebServer'"
objRecordSet.Find strSearchCriteria

If objRecordset.EOF Then
Wscript.Echo "Record cannot be found."
Else
Wscript.Echo "Record found."
End If

objRecordset.Close
objConnection.Close
===========================

' Searching a Database Using String Criteria


Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " &; _
"Data Source = eventlogs.mdb"

objRecordSet.Open "SELECT * FROM EventTable " &; _
"WHERE Type = 'Error'", objConnection, adOpenStatic, _
adLockOptimistic

objRecordSet.MoveFirst

Wscript.Echo "Number of records: " &; objRecordset.RecordCount

objRecordSet.Close
objConnection.Close
=============================

' Sort a Recordset


Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " &; _
"Data Source = eventlogs.mdb"

objRecordSet.Open "SELECT * FROM EventTable " &; _
"ORDER BY EventCode ASC", _
objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordSet.EOF
Wscript.Echo objRecordSet.Fields.Item("EventCode"), objRecordSet.Fields.Item("Logfile")
objRecordSet.MoveNext
Loop

objRecordSet.Close
objConnection.Close
===========================

' Sort a Recordset on Multiple Fields


Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " &; _
"Data Source = eventlogs.mdb"

objRecordSet.Open "SELECT * FROM EventTable " &; _
"ORDER BY EventCode ASC, Logfile DESC", _
objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordSet.EOF
Wscript.Echo objRecordSet.Fields.Item("EventCode"), objRecordSet.Fields.Item("Logfile")
objRecordSet.MoveNext
Loop

objRecordSet.Close
objConnection.Close
=========================

' Update a Record in a Recordset


Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

objConnection.Open "DSN=Inventory;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM Hardware" , objConnection, _
adOpenStatic, adLockOptimistic
strSearchCriteria = "ComputerName = 'WebServer'"
objRecordSet.Find strSearchCriteria

Set colSoundCards = GetObject("winmgmts:").ExecQuery _
("Select * from Win32_SoundDevice")

For Each objSoundCard in colSoundCards
objRecordset("ComputerName") = objSoundCard.SystemName
objRecordset("Manufacturer") = objSoundCard.Manufacturer
objRecordset("ProductName") = objSoundCard.ProductName
objRecordset.Update
Next

objRecordset.Close
objConnection.Close
=============================

' Update Multiple Records in a Recordset


Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " &; _
"Data Source = inventory.mdb"

objRecordSet.Open "UPDATE GeneralProperties SET " &; _
"Department = 'Accounting'", _
objConnection, adOpenStatic, adLockOptimistic

objConnection.Close
==============================
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic    Manufacturing Information Solutions Forum Index -> VB Script All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group