Visual Basic Tips #16


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

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

Proudly presents:
Visual Basic

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

*1. CONNECTING TO ACCESS 2000 DATABASES                 
        
If you're trying to connect to your Access 2000 databases, you have a 
few steps to complete first. You first have to have the Jet 4.0 engine 
installed on your machine. The best way to do this is to install at 
least Access 2000 on your machine. You then need to change your 
connection string to use the Jet 4.0 provider instead of the Jet 3.51 
provider. Here's some sample code you can use: 

Dim dcnDB As New ADODB.Connection 

dcnDB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _ 
      & " Data Source=C:\Visual Studio\nwind.mdb" 
dcnDB.Open  

The added bonus is that the Jet 4.0 provider can read and write to 
Access 97 databases. This will save you time because you don't have to 
convert the databases to take advantage of the performance 
improvements in the Jet 4.0 engine. If you want to make changes in an 
Access 97 database using Access 2000, you will have to convert it to 
the new format.  

Good luck!


*2. MANAGING IMAGES WITH A DATABASE                 
        
For those of you interested in storing images in a database, we have 
two words for you: Don't bother. While you can do it, it's much easier 
to do the following: 

First, create a field in your table to store a filename. Store the 
image on disk somewhere with that filename. Link to the image from 
your web application or your client/server application.  

In our experience, it's much easier to work with images in this 
fashion unless you have some high-performance systems specifically 
designed for working with large batches of images. For more users, 
storing the files on a shared disk makes them easier to get to for 
both Web and client/server applications.


*3. 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 for you if 
you're in the same situation. The first thing I'd try would be SQL 
Server Data Transformation Services. 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) way 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.


*4. CHECK FOR NULL VALUES                 
        
If you've done much work with databases, you will already understand 
the need to check for null values. A null value is a special value 
that indicates that no value has been stored in a particular field in 
a database table. Nulls cannot be manipulated in the same way empty 
strings can. Because of this problem, you have to do one of two 
things. For fields that are holding string data, you can convert a 
NULL to an empty string with this code: 

<pre> 
strNonNull = strNullable & "" 
</pre> 

By concatenating an empty string to a possibly null field, you change 
it from a null field to an empty string, which you can work with.  

For fields that hold numeric data, it's not so easy. You'll have to 
use the IsNull function to determine whether a field is null before 
attempting to manipulate it. If you don't, you will eventually be 
rewarded with the runtime error 

Invalid Use of Null.


*5. USE OPTION EXPLICIT                 
        
If you are a new programmer, one of the things you probably aren't 
doing is declaring your variables. Because of VB's "feature" of 
declaring variables as it encounters them in code, a typo can turn 
into a second variable that you weren't expecting to get. For this 
reason, as well as our own sanity, we always use Option Explicit at 
the top of every file in our VB projects. We also instruct Visual 
Basic to automatically add this statement to new files by changing the 
Require Variable Declaration option in the Tools, Options dialog box. 
This has saved us hours of debugging over the years and is worth doing 
in every project.


*6. RESTRICTIONS ON MDI CHILD FORMS                 
        
If you're using MDI forms (child and parent) in your application, you 
should be aware of some restrictions on them. First, you can't switch 
whether a form is a child or not at runtime. This question has come up 
once or twice, and the answer is that the architecture prevents this 
from happening. Another restriction is that MDI child forms cannot be 
shown modally--they can be shown only within the MDI parent form as 
modeless forms. A final rule is that you can have only one MDI form 
per application. If you feel that you need more than one, I'd suggest 
looking at the overlap between the functions and combine the functions 
from your two MDI forms into a single MDI form.


*7. GETSETTING AND SAVESETTING RESTRICTIONS                 
        
One of the more professional touches you can add to your application 
is the ability to use the Registry. Unfortunately, the built-in 
GetSetting and SaveSetting functions don't quite do the Registry 
justice. These routines can look in only one particular section of the 
Registry (Software\VB and VBA Program Settings) and can't look at any 
other application's settings. While this is helpful for new users, it 
really limits what power users can do. If you need access to the rest 
of the Registry, you can read an article at the VB Techniques Web site 
that shows how to build a class to read the Registry using the 
appropriate API calls.


*8. LOAD TREEVIEW AS YOU NEED DATA                 
        
If you're using a TreeView control in your application, you've 
probably noticed that it takes a while to load data into it. For this 
reason, we load only a single level of data at a time. The control has 
the Expand and Collapse events, which indicate that a node needs to 
have its data loaded. Using these events with an intelligent window 
makes it much easier to manage large amounts of data in this flexible 
control.


*9. DISCONNECTED RECORDSETS                 
        
One of the best ways to pass batches of data back from COM components 
is through a disconnected ADO recordset. A disconnected recordset is 
created by first specifying the CursorLocation property as 
adUseClient. You then create the recordset as usual. When you've 
created the recordset, you set the ActiveConnection to Nothing, which 
disconnects it from the server. You can then pass the whole recordset 
to the destination application without having to worry about 
maintaining the connection.


*10. 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 in parameters you might 
have to supply to 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 any 
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 in 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.
