Visual Basic Tips #27


----------------------------------------------

TipWorld - http://www.tipworld.com
The Internet's #1 Source for Computer Tips, News, and Gossip

Proudly presents:
Visual Basic

----------------------------------------------


*1. GETTING A FORM'S HANDLE                   
          
Many API calls require the use of a window handle, or hWnd for short. 
If you need to pass a handle to the current form, use the hWnd 
property of the form. That will link the API call to the current form.


*2. DATA ACCESS LIBRARY CONFLICTS                   
          
In a previous tip, I mentioned that you must be careful about using 
data access objects since some of them have the same name. However, 
you can eliminate the problem by always specifying the library name 
before the object name, such as 

Dim objDB As DAO.Database 
Dim rsData As DAO.Recordset 

As long as you specify the library name, there won't be a question as 
to which library is in use.


*3. VARIABLE USAGE                   
          
In response to a question sent by a reader, here's a quick reminder 
about how you can create variables within Visual Basic. If you create 
a variable within a subroutine or function, it's available only from 
within that function. If you create a variable in a form's or class's 
declarations section, that variable is available throughout all the 
code in that file. If you make the variable in that same section 
Public, it can be accessed by code outside the file.  

If you create a variable in the declarations section of a code module, 
it's available throughout the entire application--as long as you use 
the Public keyword in front of the variable's name.  

In short, declare the variable to be as small in scope as possible. 
For example, if you need a counter for a single procedure only, don't 
declare it as global to the application.


*4. UNREGISTERING A COMPONENT                   
          
It's very easy to load your machine with lots of components and 
controls--but often not so easy to weed out the ones you don't need. 
When time comes to unregister a component, you can do it with the 
regsvr32 utility using the /u argument, as shown here: 

regsvr32.exe /u d:\something.dll 

This will remove the references to the DLL in the registry.  

Note: All of the previous Visual Basic tips written by Eric Smith are 
now archived and searchable at the VB Techniques Web site. Be sure to 
visit soon! 

VBTechniques 
http://vbtechniques.com/ 


*5. TRANSFERRING RECORDS BETWEEN DATABASES                   
          
A user recently asked if there were good ways to move data from one 
database to another. Here are a couple of suggestions: 

The first thing I'd try would be SQL Server Data Transformation 
Services (DTS). These powerful tools can work with a wide variety of 
databases, including Oracle, Access, and, of course, SQL Server. They 
allow a lot of manipulation during the transfer of data, such as the 
remapping of fields from one table to another, reformatting data, and 
so on.  

If this method won't work, the next most flexible (but somewhat more 
time-consuming) technique is to open two database connections and move 
each record individually. You'll have the flexibility to do whatever 
you need to in order to move the record from one table to another, but 
you'll have to do it all yourself.  

A final way to do it is not to move the data at all. Instead, you link 
one database to another by referencing the other's tables. This can be 
done in Access, SQL Server, and other databases. In this way, you 
don't have duplicate data.


*6. BUILDING A TEXT EDITOR                   
          
The inquiry.com Web site is running a series of articles (by yours 
truly) on how to create a text editor from start to finish. So far, 
we've added resizing code, menus, cut/copy/paste, and more. Here are 
the URLs: 

Part 1: Building a Text Editor 
http://www.pcworld.com/r/pcw/1%2C2061%2Cpcw-vb8-1%2C00.html 

Part 2: Building a Text Editor 
http://www.pcworld.com/r/pcw/1%2C2061%2Cpcw-vb8-2%2C00.html 

Future articles will include the use of a spell-check utility and 
more, based on reader suggestions. 


*7. FILESYSTEMOBJECT IN VISUAL BASIC                   
          
Want to take advantage of FileSystemObject and its related objects? 
You can make use of them in Visual Basic simply by referencing the 
Microsoft Scripting Runtime. The file should be listed in your 
References dialog box and is named SCRRUN.DLL. These objects make 
file, folder, and disk manipulation far easier than previously 
implemented.


*8. USING VARIANTS IN COM COMPONENTS                   
          
If you're building COM components for your Web applications, remember 
that VBScript knows only one data type: Variant. This has two 
implications for COM components: The first is that, in parameters, you 
might have to supply a function or subroutine. If your COM component 
is set to accept a Long, for instance, you'll have to use the CLng 
function on the input value before passing it. This is fine, but an 
easier way to handle this is to accept a Variant and do conversions 
after the value is inside the COM component. This has the added 
benefit of keeping all the conversion code in one place. Also remember 
to do any necessary validation on the input.  

The other implication is with return values from functions. While you 
might want to return an ADODB.Recordset object to your code, for 
instance, you should use a return type of Variant. You'll still be 
able to return the object, and VBScript will know how to handle the 
object, since you're using the Set statement to store the result in a 
Variant variable.


*9. CREATING AND USING STORED PROCEDURES                   
          
One of the easiest ways to improve your application's performance is 
to take advantage of precompiled queries, also called stored 
procedures in databases like SQL Server and Oracle. ADO provides a 
mechanism to use these stored procedures with parameters, which allows 
you to create a stored procedure with a hole for a value you supply at 
runtime. To do this, use the code below, which invokes a simple stored 
procedure with a single parameter. 

Note: Be sure you add the Active Data Objects 2.0 library to your 
Visual Basic project before using this code. All values prefixed with 

Ad 

are predefined constants. 

   Dim cmdSP As ADODB.Command 
   Dim parCustomerID As ADODB.Parameter 
   Dim rsData As ADODB.Recordset 

   Set cmdSP = New ADODB.Command 

   ' dcnDatabase is the current database connection 
   ' and is an ADODB.Connection object opened elsewhere 

   Set cmdSP.ActiveConnection = dcnDatabase 
   cmdSP.CommandText = "sp_GetCustomerByID" 
   cmdSP.CommandType = adCmdStoredProc 

   ' Create the parameter and set its data type 
   ' and parameter type 

   Set parCustomerID = cmdSP.CreateParameter("CustomerID") 
   parCustomerID.Type = adInteger 
   parCustomerID.Direction = adParamInput 
   parCustomerID.Value = varID 

   cmdSP.Parameters.Append parCustomerID 

   Set rsData = cmdSP.Open 

   ' At this point, you have an open recordset 
   ' that has data that can be read by your code.


*10. GETTING A USER'S NETWORK NAME                   
          
If you're building an application that uses usernames, it's a nice, 
simple feature to prompt the user for a password after first 
determining the user's network login ID. The code shown here will do 
this for you. Add the Declare statement to the top of the module or 
into a separate code module: 
 
Declare Function WNetGetUser Lib "mpr.dll" Alias "WNetGetUserA" _ 
   (ByVal lpName As String, ByVal lpUserName As String, _ 
    lpnLength As Long) As Long 

   'Your code goes here 

   Dim strUserName As String 

   strUserName = Space(255) 
   WNetGetUser "", strUserName, 255 
   txtUserName = Trim(strUserName) 

   'Your other code goes here
