Tag Archive: database

Apr 12 2008

Display records where “Departure_Initials” starts with an “L”

This example uses the QTP_Flight32 DSN to database connection. The example is based on the Flight Reservation demo database. [hideit] Dim oConn, oRst, oField Dim sql, nCount nCount = 1 set oConn = CreateObject( “ADODB.Connection” ) oConn.Open “QT_Flight32” set oRst = CreateObject( “ADODB.recordset” ) sql = “SELECT Flight_Number, Airlines FROM Flights WHERE Departure_Initials LIKE ‘L%'” …

Continue reading »

Apr 01 2008

Determine which Version of Access was Used to Create a Database

While looking up something else in the Access documentation we ran across a property named FileFormat. FileFormat is the property that tells us which version of Access was used to create a database. Want to know which version of Access was used to create the database flight32.mdb? ( The flight application’s QTP demo ) Here …

Continue reading »

Apr 01 2008

Create and Delete a DSN

a DSN, is a way for you to connect to a database merely by referencing the DSN name, and without having to specify the entire path to the database. That’s particularly handy if you have a bunch of scripts that access this database. If you hard-code the path into the scripts and then move the …

Continue reading »

Apr 01 2008

Get a list of the ODBC drivers installed

The registry, is the upstairs closet of the operating system: if you’re willing to look, you can find almost anything in the registry. That’s where can be found the list of installed ODBC drivers, in HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers. Const HKLM = &H80000002 Set reg = GetObject( “winmgmts:\\.\root\default:StdRegProv” ) keyPath = “SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers” reg.EnumValues HKLM, keyPath, valueNamesArr, …

Continue reading »

Apr 01 2008

Retrieve a list of the system DSNs installed

If you have no idea what we’re talking about System DSNs are simply a shortcut method for connecting to databases and other data sources. You can view a list of the System DSNs available on a computer by bringing up the ODBC Data Source Administrator dialog box and looking on the System DSN tab That’s …

Continue reading »

Apr 01 2008

Sort data with disconnected record set

Sorts numerical data using a disconnected record set. [hideit] Const adInteger = 3 Const MaxCharacters = 10 Dim sampleArr( 20 ) For i = LBound( sampleArr ) to UBound( sampleArr ) sampleArr( i ) = RandomNumber( 1, 1000 ) Next Print “Array before sorting :” For Each num In sampleArr Print num Next ‘ ** …

Continue reading »

Apr 01 2008

Add a New Record to a Table

The following code demonstrates how to add a new record to a table. The example uses the Flight Reservation Database. Const adOpenStatic = 3 Const adLockOptimistic = 3 Set oConn = CreateObject( “ADODB.Connection” ) Set oRst = CreateObject( “ADODB.Recordset” ) oConn.Open “QT_Flight32” oRst.Open “SELECT * FROM Flights” , oConn, adOpenStatic, adLockOptimistic oRst.AddNew oRst( “Flight_Number” ).Value …

Continue reading »

Apr 01 2008

Create a New JET Database

The following code demonstrates how to create a new database named New_db.mdb. Set oConn = CreateObject( “ADOX.Catalog” ) oConn.Create “Provider = Microsoft.Jet.OLEDB.4.0; ” & _ “Data Source = new_db.mdb”

Apr 01 2008

Create a Table in a JET Database

The following code demonstrates how to create a new table in the Flight32.mdb Set oConn = CreateObject( “ADODB.Connection” ) oConn.Open “QT_Flight32” oConn.Execute “CREATE TABLE EventTable(” & _ “EventKey COUNTER ,” & _ “Category TEXT(50) ,” & _ “ComputerName TEXT(50) ,” & _ “EventCode INTEGER ,” & _ “RecordNumber INTEGER ,” & _ “SourceName TEXT(50) ,” …

Continue reading »

Apr 01 2008

Delete a Record from a Recordset

The following code demonstrates how to delete a record from a table. The example uses the Flight Reservation Database ( non default records )   Const adOpenStatic = 3 Const adLockOptimistic = 3 Const adUseClient = 3 Set oConn = CreateObject( “ADODB.Connection” ) Set oRst = CreateObject( “ADODB.Recordset” ) oConn.Open “QT_Flight32” oRst.CursorLocation = adUseClient sql …

Continue reading »