Visual Basic Tips #18


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

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

Proudly presents:
Visual Basic

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

*1. GENERATING A JULIAN DATE                  
         
In case you haven't heard that term, the Julian date is the day of the 
year, starting with January 1. This was a common way to store dates on 
mainframe computers. Since we now have more powerful date data types, 
it's not so common as it once was. However, if you do need to generate 
the Julian date for a date, here's a quick way to do it: 

 
strDate = Format(Date, "y") 
 

This tip was supplied by David Herron. Thanks, David.


*2. ALWAYS INCLUDE A CASE ELSE                  
         
When using the Select Case statement, always have a Case Else that 
will pick up any cases not matched in your list. Even if you're sure 
that extraneous values can't be processed by your code, it's a good 
idea to include a Case Else, as shown here: 

Select Case intTest 
Case 1: 
      ' do something 
Case 2: 
     ' do something else 
Case Else: 
      ' this is probably an error, so 
      ' display an appropriate message 
End Select


*3. CONNECTING TO ACCESS 2000 DATABASES                  
         
If you're trying to connect to your Access 2000 databases from Visual 
Basic, it's pretty easy to do using ADO. Besides needing to have 
Access 2000 (or just the Jet runtime DLLs) installed on the machine 
where your program is running, you 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


*4. BUILDING SQL STATEMENTS                  
         
If you're building SQL statements within your Visual Basic code that 
have data that might have single quote characters in it, be sure to 
replace every single quote with two single quotes. This is really easy 
to do with the new Replace function. Simply run the Replace function 
on each data value you're appending to your SQL query, as shown in 
this example: 

 
strName = "Mrs. O'Leary" 
strSQL = "SELECT * FROM Emp WHERE Name = " & Replace(strName, "'", 
"''") 
 

This replaces each single quote in strName with two single quotes. 
Both Access and SQL Server will properly handle the single quote as 
long as it is marked in this manner. You can also create a shortcut 
function, called CleanString, that looks like this: 

 
Function CleanString(strInput As String) As String 
      CleanString = Replace(strInput, "'", "''") 
End Function 
 

Be sure NOT to run this function on your entire SQL string, because 
the function will also replace the single quotes surrounding your data 
values. This will cause the SQL statement to raise an error when you 
use it.


*5. WRAP LONG LINES OF CODE                  
         
If you're building a long string, remember that you can't simply wrap 
your string onto multiple lines. You can, however, use the line 
continuation character to break a long line into multiple, smaller 
lines, as shown here: 

strLongString = "This is a long string that goes on and on without any 
end in sight." 

strShorterPieces = "This is a long string" _ 
           & " that goes on and on" _ 
           & " without any end in sight." 

Be sure to include spaces either at the end or the beginning of the 
sections--the continuation character won't automatically add them for 
you.


*6. GET THE LATEST SERVICE PACKS                  
         
Visual Basic, like most Microsoft products, is periodically updated 
through service packs. Subsequent service packs include all the fixes 
from the previous service packs, so if you need to update to Service 
Pack 3, you have to apply only Service Pack 3. Visual Basic 6's latest 
service pack is SP 3 and is available from the Microsoft Web site: 

http://www.microsoft.com/vbasic 

With Visual Studio, Microsoft typically packs all the applications' 
service packs into a single service pack download, which makes it easy 
to update all your Visual Studio applications at the same time.


*7. USING DATA CONTROL WITH LARGE TABLE                  
         
A reader wrote me asking about using a Data control with a large table 
(250 fields, in his case). He states that with a small number of rows, 
the control works fine. But with more rows, the control blows up. 

My initial guess would be that the database table is not properly 
designed. There are very few applications that require a single table 
with 250 fields in it. My guess is that there are lots of duplicated 
fields that should be separate tables. For instance, he might have two 
or three sets of address fields. One of the rules of database design 
is that repeated groups like this should be moved into separate 
tables. You can then join the tables using a primary and foreign key 
relationship. 

In addition, there may be fields that are irrelevant to the main 
entity. For instance, on a customer table, you wouldn't put in the 
price they paid on their last order. That is best left to either a 
table that contains the order totals for all orders in the system or a 
table where the total can be calculated dynamically. 

While these things don't solve the problem of the DAO Data control not 
being able to handle large amounts of data, they can be (and should 
be) used to prevent problems in the future. DAO is far from being a 
high-performance database access library. RDO and ADO are better 
optimized for this type of size and traffic. The ADO Data control is 
much more efficient when retrieving data from large row count and 
large field count tables, as well.


*8. RESIZING FORMS AND REPOSITIONING CONTROLS                  
         
One of the most frequently asked questions about Visual Basic is how 
to allow a form to be resized and have all the controls adjust 
themselves automatically. Guess what? You can't do it . . . not 
automatically, anyway. You do, however, have a couple of options: 

The more interesting approach is to do it yourself. Using a simple 
methodology, this is not hard to do on forms where it makes sense. On 
a form designed for writing, such as a "Notes" or "Description" form, 
it makes sense to allow resizing. As the user enlarges the form, you 
change the height and width of the box to fit within the form borders. 
Here's a quick bit of code that changes the size of txtNotes to fit 
within the form edges, minus a small margin: 

txtNotes.Height = Me.ScaleHeight - (2 * txtNotes.Top) 
txtNotes.Width = Me.ScaleHeight - (2 * txtNotes.Left) 

In this code, Me refers to the form on which this control is located. 
This code uses the margin defined by the upper-left corner as the 
margin to use on the other side. This means that if your leftmost 
point of the text box is at 60 and the form is 1200 wide, the text 
will be 1080 wide (1200 - 2 * 60). Same thing goes for the height 
using the Top property as the top margin. 

While this version is pretty simple, it gets tricky if you have other 
controls, such as command buttons, on the form. I like to keep my 
command buttons centered in the bottom part of the form. This means 
that while the width of the text box can use the same formula, the 
height has to account for the height of the command buttons. Here's 
how you could change the code to handle this: 

txtNotes.Height = Me.ScaleHeight - (2 * txtNotes.Top) - cmdOK.Height - 
txtNotes.Top. 

This tells the text box that it has to account for three blank spaces: 
one above the box, one below the box, and one below the command 
buttons. The command buttons also have to be repositioned based on the 
bottom of the text box, like so: 

cmdOK.Top = txtNotes.Top + txtNotes.Height + txtNotes.Top 

The easier way to do all this is to look into a commercial resizer 
control. They're a little tricky to get set up initially, but once 
you're done, they handle all this work for you. Another option is to 
not allow resizing at all. Most windows, such as options dialog boxes 
and most other dialog boxes, don't need to be resized by the user. 
However, for the few that you need the feature available, one of these 
options should take care of the problem for you.


*9. RUNNING FROM THE CD-ROM                  
         
One of the nagging problems with Visual Basic is that there isn't a 
good way to make a completely packaged application. Every VB program 
you write has at least a few runtime DLLs that have to be registered, 
not just copied, to the client machine. You also have to make sure 
that when you copy a file to a client machine you're not overwriting a 
newer version. Most installation programs take care of this 
automatically, but your program might not do it. 

This means that you still can't create a VB application that runs on a 
CD-ROM, for instance. Based on the way VB applications are now 
written, you probably never will be able to do this. If you do need to 
build a self-contained application, you might want to look into 
another tool, such as one of the several that Macromedia produces. 
These programs are designed to create self-contained applications that 
work best for CD-ROM browsers like you might see in an installation 
program. 

http://www.macromedia.com


*10. SHARE YOUR DATABASE CONNECTION                 
        
When writing a program that uses a database connection, be sure to 
minimize the number of database connections you use. Besides the ones 
you explicitly create using the ADO Connection object (or the Database 
object in DAO), remember that each DAO or ADO data control, by 
default, makes its own connection to the database. This is 
particularly critical if you're writing an application using a 
database that allows only a limited number of connections--each 
connection will typically count against the total available. This 
means that if you have 25 client licenses available and you're using 
five per instance of your application, you've reduced to five the 
number of people you can handle. 

To get around this limitation, create a global ADODB.Connection object 
in a code module external to any form. You'll then initialize and open 
your database when the program starts using the Sub Main routine and 
close it when the program exits. Everything in the application can 
share the same connection. It may mean that you have to do a bit of 
your own ADO code, but it will save time during execution since you 
won't have to create each connection every time.
