Hi, Peter:
Let's dive into building a sample VB6 application without forms that
connects to a SQL Server database using ADO. Create a new "Standard
EXE" project in the VB6 IDE. A form called "Form1" is added to the
project by default. You can remove this, e.g. by right-clicking on
Form1 in the Project Explorer and selecting "Remove" from the menu.
Now go to the Project menu and choose "References...". Add a
reference to the ADO 2.5 library (to duplicate my actions):
Microsoft ActiveX Data Objects 2.5 Library
For the sake of the code below, ADO 2.1 would probably do as well, but
your "object browser" will probably show you as available more recent
references for 2.6 (and perhaps 2.7). More about the various versions
later.
Then go to the Project menu and select "Add Module", which by default
produces a module named "Module1". Change the name if you like, but
enter the following code to duplicate my test program:
> > > > > > > BEGIN CODE > > > > > > > > > > > > > > >
Public Sub Main()
' We don't use this here, but command line arguments
' could be obtained by using this function:
Dim cmdStr As String
cmdStr = Interaction.Command$()
' Define two ADO object variables
Dim adoConn As New ADODB.Connection
Dim adoRset As New ADODB.Recordset
' Open a connection to the Northwind database using
' the native SQL Server OLEDB Provider
adoConn.Open "Provider=SQLOLEDB; Data Source=localhost;" & _
" Initial Catalog=Northwind; User Id=nema; Password=test"
' Use the connection to open a recordset on Employees table
adoRset.Open "Employees", adoConn, adOpenForwardOnly, _
adLockReadOnly, adCmdTable
' Loop through the forward-only recordset to reach the end
While Not adoRset.EOF
' Wait a second for each record and beep
Delay (2) ' a local subroutine using Timer function
Beep
' Advance to the next record
adoRset.MoveNext
Wend
' Close the objects
adoRset.Close
adoConn.Close
' Release the objects' memory
Set adoRset = Nothing
Set adoConn = Nothing
End Sub
Private Sub Delay(lngSec As Long)
Dim lngBgn As Long
lngBgn = Timer
Do While Timer <= lngBgn + lngSec
DoEvents ' avoids freezing up the computer
Loop
End Sub
< < < < < < END OF CODE < < < < < < < < < < < < < < <
Change the name of the project, if you like, by selecting the top
level project node in the Project Explorer window, then editing the
project (Name) in the Properties window. I called my project "nema".
Save the project files and, from the Project menu again, select "nema
Properties" to verify how the project is configured. You should see a
textbox near the top of the dialog box's General tab that indicates
"Sub Main" as the project's Startup Object. There's also an option on
the same tab to select "Unattended Execution". I didn't find this
necessary, but it sounds like a good thing to apply.
Now go to the File menu and select "Make nema.exe..." or the
equivalent based on your project's name.
The program is now built and ready for testing. Before I describe my
own testing, let's do a brisk walkthrough of the code so you can make
any changes necessary for your own setup.
I threw in the first couple of lines of code as a "tip" for how you
may want to evolve the program. From the Main( ) routine one can
access any command-line arguments by parsing out the string returned
by Command$( ). We can forget about this for the time being.
Next I created a couple of ADO objects. The ADO library is intended
to be an efficient wrapper for OLEDB interfaces. Although the ADO API
has undergone extensive changes in fairly rapid order, the library
rests on three fundamental COM object interfaces:
Connection : a connection to a data source
Recordset : a specialized collection of data records
Command : designed to run commands (like SQL), esp. with parameters
Our test program uses only the first two of these kinds of objects,
and there are indeed other ADO interfaces involved which are less
prominent.
The Connection object variable adoConn has an Open method which
requires a "connection string". Of the various items specified in our
connection string:
"Provider=SQLOLEDB; Data Source=localhost;" & _
" Initial Catalog=Northwind; User Id=nema; Password=test"
only the first item (Provider=SQLOLEDB) is actually processed by the
ADO wrapper. The remaining items are passed through to the underlying
OLEDB provider, in this case the native SQL Server provider.
Although you asked for demo code using "SA" as the user code, I
thought it better for my own testing to create a new SQL Server login
"nema" so that I could assign the password "test" without affecting
existing databases. If you prefer to use the SA account, you can
simply change that in the connection string in your code:
"User Id=sa;"
The initial catalog item is of some interest. If this is omitted,
then the connection will default to a database assigned to the user as
their "Default database" within SQL Server. Just to illustrate the
item, I made the default database for the nema account to be the pubs
database, so that we make essential use of the initial catalog item in
directing the connection to the Northwind database instead.
Instead of "localhost" you'll probably want to insert the machine name
for your test or production SQL Server host as the data source.
You'll see in a minute why "localhost" was a good choice for me as I
discuss my testing plan.
Once the connection adoConn has been created and opened, it is ready
for use in extracting a recordset from the corresponding database. In
the case of the Northwind database, there's a table called Employees
with a nice variety of field types, including image and text (the
sorts of things that give earlier versions of ODBC fits). Also the
sample data for the Employees table contains nine records.
The Recordset object variable adoRset has an Open method also. There
are several variations of it, but in this one we provide a tablename,
a connection object (adoConn), and a few options to specify the cursor
type (forward only), the lock type (just a read lock), and a "command"
type (which here means that we've specified a table by name to get all
its records). Situations that require passing a bunch of arguments,
e.g. into a stored procedure, would be better handled by using a
Command object and its Execute method, which returns a Recordset
object. There's frequently more than one way to do a given task with
ADO.
Since your question specifies "no forms", I tried to be a little
creative in finding a way to demonstrate that the program is actually
doing something without involving any GUI elements.
The idea was to use the EOF property and MoveNext method of the
Recordset object to loop through the set of nine records returned and
make an audible beep for each one. It turned out this worked more or
less as expected on Windows 98, but under Windows XP no sound was
made. Nonetheless I'm convinced the program was working, as I explain
in the discussion below.
Note the use of the Timer function in my subroutine Delay. This is a
cheesy bit of coding, I admit, not suitable for production use. The
Timer function counts seconds since midnight. The logic would
obviously be incorrect for intervals that span midnight. But it
serves the purpose here. Note the DoEvents statement within the
polling loop for Delay.
Finally we reach the clean up phase, prior to exiting the program.
First I closed the Recordset and the Connection. Then I set both
object variables to Nothing to free up the memory allocated for them
on the heap.
Because you want the program to be written in VB6, I turned to my
older laptop which sports W98SE, an MSDE/SQL Server 7 installation,
and Visual Studio 6 sp5.
I did my development and initial testing there, starting with the
creation of the user login "nema" for my MSDE instance. Since the
database is on the same box, I could use Query Analyzer, ISQL/w, and
the ADO connection string all to refer to the instance as "localhost".
I have a HOSTS file in my Windows directory on that box which maps
"localhost" to 127.0.0.1, which I think is often necessary for using
that "alias" under Win98. [Note: ISQL/w uses ODBC, so it complained
about doing a "select * from Employees" in the Northwind database.
However a "select count(*)..." or selection of a few of the initial
fields like id and name worked to demonstrate the SQL Server
authentication and the number of records present in the table.]
The Beep and Timer functions worked well for me under Win98, esp. when
the program nema.exe was launched from a Run... or console command
line. One hears nine distinct tones, one for each record in the
Employees table.
I then moved the program over to my newer laptop, which has WinXP Pro,
a SQL Server 2000 installation, and VS.Net stuff. At first the
program seemed not to be working. The "launch" from a command line
returned immediately, and no tones were heard. I tried to connect
using Query Analyzer and the nema/test credentials; it failed! I had
forgotten that I'd set up the SQL Server 2000 instance for Windows
authentication only. A quick change to the server configuration in
Enterprise Manager, and I was ready to try again (using "mixed
authentication").
Still I could not discern any evidence of success on the WinXP box.
However a little further experimentation proved that it was working.
Although I could not hear the "beep" tones, presumably because of a
difference in how WinXP "shares" the sound card resource, I was able
to monitor the progress of the program through the Task Manager.
What I did was to build a second application, beeps.exe, which just
has a Do loop from 1 to 9, with the Delay(2) call and Beep, to
demonstrate the timing loop without any database dependence. I could
then compare the CPU utilization of the beeps.exe program and the
nema.exe program. Even with the DoEvents statement, the nature of the
polling loop is such that CPU resources get pegged at 100%. Thus I
could "see" that with mixed authentication on SQL Server, the number
of records returned was nine, by comparing the Task Manager's CPU
utilization graph between running nema.exe and beeps.exe. [With only
Windows authentication the beeps.exe CPU utilization remained
unaffected; the nema.exe never got into the 100% CPU regime when only
Windows authentication was set.]
Obviously you'll want the program to actually "do" something, but
without some guidance from you about "what", I hesitate to offer a
suggestion about "how" it should be done. Let me know if what I've
done so far requires clarification before you can take it to the next
level.
Here are some additional resources that I think will be helpful:
[Microsoft ADO Home Page]
http://www.microsoft.com/ado
[DevGuru : ADO]
(on-line version is free)
http://www.devguru.com/Technologies/ado/quickref/ado_intro.html
Recall that I added the ADO 2.5 reference to my project. There are
such a large number of MDAC/ADO/OLEDB components and in such a variety
of versions that Microsoft has provided a "Component Checker" utility
that tries to tell you what you have on your machine:
[MDAC Versions and Component Checker]
http://msdn.microsoft.com/downloads/list/dataaccess.asp
The link to download the current MDAC Component Checker is about 1/3
of the way down the page. When run on my Win98 laptop, I learned that
(apart from four missing registry entries) what I have there is ADO
2.6 RTM (RTM = Release to Manufacturing, a base version). On the
other laptop with WinXP, I have ADO 2.7 sp1 (except for one DLL that
is simply a slightly later build than what the Component Checker
verifies for this release). Thus the ADO 2.5 library is an "ancestor"
to what is installed on either machine at this point.
Not satisfied to sit on the "COM" object interfaces, Microsoft has
begun to port the data access functionality into a ".Net" architecture
with ADO.Net. It is actually possible to use ADO.Net from VB6, but at
this point not all the functionality of ADO is migrated there, esp. in
regard to DDL (data definition statements) and "data-shaping". In the
long run I think the .Net architecture, with its emphasis on XML as
ubiquitous middleman, makes a lot of sense. However for writing a
quick and dirty VB6 program, I cannot recommend it today.
regards, mathtalk-ga |
