Two Hoots Banner



Visual Basic Data Tips

  • To prevent the "Invalid Use of Null" errors append an empty string to your variable i.e. Text1.Text = SomeDynaset("SomeField") & "".

  • The Properties collection of many data access objects is very helpful when debugging. Execute this code from the debug window. 
For i = 0 to Recordset1.Properties.Count - 1 :Debug.Print _
  Recordset1Properties(i).Name & Recordset.Properties(i): _ 
Next
  • Update your error handling to incorporate the DBEngine.Errors collection which provides more information on the error. 
Public Sub ShowError()

Dim sError As String
Dim nI As Integer
Dim sTitle As String

sError = ""
' Determine whether or not this is a database error
If DBEngine.Errors.Count > 0 Then
  If DBEngine.Errors(DBEngine.Errors.Count - 1).Number = Err.Number Then
    sTitle = "Database Error"
    For nI = 0 to DBEngine.Errors.Count - 1
      sError = sError & DBEngine.Errors(nI) & vbCrLf
    Next
    sError = sError & vbCrLf
  End If
End If
If sError = "" Then
  sTitle = "Error"
  ' add the error string
  sError = serer & Err.Description & vbCrLf
End If
' beep and show the error
Beep
MsgBox sError, , sTitle

End Sub
  • A common database task is to loop through all the records in a recordset. Typical code for completing such a task might resemble the following: 
Do While Not rst.EOF 
  ...task 
  rst.MoveNext 
Loop
However, this structure can be slow because the code checks for the end of the recordset at the beginning of each loop. You can speed things up by eliminating this check using the following setup:

 
rst.MoveLast 
iTotal = rst.RecordCount 
rst.MoveFirst 

For iCounter = 1 To iTotal 
  ...task 
  rst.MoveNext 
Next iCounter
Instead of checking to see if you've reached the end of the file, the For loop simply ticks off the appropriate number of cycles. This structure will speed up your search a great deal--as much as 30 percent.

  • Don't access SQL Server using DAO, use RDO or ADO to increase performance.

  • When retrieving data from SQL Server reduce the number of round-trips between the application and SQL. Each round trip is time consuming.

  • Don't create, use and destroy an object within a loop, instead reused the same object within the loop.

  • The following example shows how to handle Custom Errors in a client application.
Use sp_addmessage to add a new custom error message to the sysmessages table, i.e. sp_addmessage 50001,16,'This is test error message for %s'.

To raise the error in a stored procedure use - RAISERROR (50001,16,1,'test user')

Then within your application use the Errors collection to capture the errors returned by SQL Server -

i = 0
For Each error_item in objConnection.Errors
  MsgBox objConnection.Errors(i).Description
  MsgBox objConnection.Errors(i).NativeError
  i = i + 1
Next
  • When your ADO query doesn't return a rowset use the adExecuteNoRecords option to bypass the code required to create and receive a rowset.

  • Don't request the server to sort unless required, as sorting on the client is faster for reasonably sizes recordsets.

  • Make sure you include the CommandType option when opening a recordset or building a command object. It saves ADO working out what you are trying to achieve.

  • Use the GetRows method of the Recordset object to access SQL data. It is used to pull all the records from the recordset into an array. Also consider using GetArray and GetString methods.

  • The CacheSize property of the ADO recordset determines how many records are fetched from a server-side cursor at a time and has a default value of one. This figure needs to be much higher, between 100 and 500 depending on the number of rows returned.

  • Choose DSN-less connections when connecting to SQL Server. If you do use a DSN, use System DSNs over File DSNs as they are faster at making a connection.

  • When creating a Command object to execute a stored procedure against SQL Server, you can use either the adCmdText or adCmdStoredProc property to tell ADO that you want to execute a stored procedure. To increase performance use the adCmdStoredProc which bypasses parameter translation. If you don't use the CommandType property the default adCmdUnknown value will be used and the Data Provider will test the CommandText property to determine if it is a stored procedure, table name or SQL statement before executing it. Additionally if the stored procedure doesn't return any rows set the adExecuteNoRecords option which tells the Command object not to ask for a returning rowset.

  • If you reuse embedded SQL in a VB loop then set the ADO Command object's Prepared property to True. This will compile and save a copy of the SQL statement in SQL Server's cache increasing performance.

  • If you have both DAO and ADO in your application ensure that you qualify object declarations i.e. ado.recordset or dao.recordset. If the qualifier is omitted VB will choose the object model that is referenced first in the project references.

  • When looping large ADO recordsets create field objects before running the loop as shown below - 
Dim rs As New ADODB.Recordset
Dim field1 as ADODB.Field
Dim field2 as ADODB.Field
rs.Open "SELECT account, accountname from ClientAccounts","DSN=Accounts", , , adCmdText
Set field1 = rs.Fields("account")
Set field2 = rs.Fields("accountname")
Do Until rs.EOF
  List1.Additem field1 & ", " & field2
  rs.MoveNext
Loop
rs.Close
Set rs = Nothing
  • There is even a quicker way to populate the above list box which also moves some of the processing onto the server. 
Dim rs as New ADODB.Recordset
Dim vArray() As Variant
Dim l as Long
rs.Open "SELECT account + ', ' + accountname FROM ClientAccounts","DSN=Accounts"
      , , , adCmdText
vArray() = rs.GetRows()
For l = 0 To UBound(vArray,2)
  List1.Additem vArray(0,l)
Next
rs.Close
Set rs = Nothing
  • When creating an ADO connection string ensure you set the Application Name parameter so that it shows up in queries such as sp_who2.

  • Load the cursor as soon as possible by moving to the last row of the result set. This releases the share locks created when the cursor was built, freeing up SQL Server resources.

  • Use either a keyset or static cursor if you need to perform a JOIN as part of your cursor as it's generally faster than dynamic cursors.

  • Use the following dot notation as it's faster. 
With adoConnection
  .ConnectionTimeout = 100
  .ConnectionString = "xyz"
  .CursorLocation = adUseClient
End adoConnection
  • If you repeat a call to s Stored Procedure within a loop set the ADO Command objects's 'Prepared' property to true. This compiles and saves a copy of your SQL statement in SQL Server's cache boosting performance.

  • Use the string form of VB string functions (have an '$' after the function name) as they are faster.

  • Ensure that you fully qualify your call to your stored procedure i.e 'EXEC databasename.dbo.procedurename' as a small performance improvement is gained.

  • Another benefit of using stored procedures over embedded SQL is that SQL caches the stored procedure in memory after it's run for the first time, when it can be reused reducing overhead on the SQL Server.

  • When using ADO ensure that you are using the OLE DB provider rather then the ODBC provider or the ODBC provider for OLE BD as it provides better performance.

  • Never keep a transaction open whilst waiting for user input as this will cause locking problems.

  • To ensure that your sql calls from Visual Basic are not creating, running and deleting a temporary stored procedure use the SQL profiler to check activity between SQL server and your application.

  • Don't use the recordset Movefirst event of a Forward-Only cursor as it re-executes the entire query.

  • You can access data from your recordset in several ways - 
'Method #1
sCustomerName = RS(0)

'Method #2
sCustomerName = RS("CustomerName")

'Method #3
Dim oField As ADODB.Field
Set oField = RS("CustomerName")
sCustomerName = oField.Value
Method #1 is fastest but less maintainable. Method #2 is not necessarily the most efficient as ADO must find the fieldname inside the collection every time you reference it. Method #3 should be used when you are looping through records.

Methods for Transferring Data to Excel from Visual BasictopTop of Page

There are a number of different methods for transferring data to Excel.

  • Transfer Data Cell by Cell 
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

' Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add

' Add data to cells of the first worksheet in the new workbook
Set oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Value = "Customer Number"
oSheet.Range("B1").Value = "Customer Name"
oSheet.Range("A1:B1").Font.Bold = True
oSheet.Range("A2").Value = "1"
oSheet.Range("B2").Value = "Jones Steam Ltd"

' Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
This approach is suited to transferring small amounts of data. You can place data anywhere in the workbook and format the cells at run time. However each Range object results in an interface request slowing down the transfer of data. Additionally Windows 95 and 98 have a 64K limitation on interface requests after which Excel may stop responding. See Q216400 in the Microsoft Knowledge Base.

  • Transfer an Array of Data 
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

' Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add

' Create an array with 3 columns and 100 rows
Dim DataArray(1 To 100), 1 To 3) As Variant
Dim r As Integer
For r = 1 To 100
  DataArray(r, 1) = "ORD" & Format(r, "0000")
  DataArray(r, 2) = Rnd() * 1000
  DataArray(r, 3) = DataArray(r, 2) * 0.7
Next

' Add headers to the worksheet on row 1
Set oSheet = oBook.Worksheets(1)
oSheet.Range("A1:C1").Value = Array("Order ID", "Amount", "Tax")

' Transfer the array to the worksheet starting at Cell A2
oSheet.Range("A2").Resize(100, 3).Value = DataArray
  
' Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
As you can see you can transfer 300 cells data in one call rather than the 300 calls from the previous method. This method only requires two interface methods for the Range object.

  • Transfer an ADO Recordset to a Worksheet Range
Excel 2000 introduced the CopyFromRecordset that allows you to transfer an ADO/DAO recordset to a range on a worksheet. Excel 97 also provides a CopyFromRecordset method, but it can only be used with a DAO recordset.

' Create a Recordset from all the records in the Orders table
Dim sNWind As String
Dim conn As New ADODB.Connection
Dim rs as ADODB.Recordset

sNWind = "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sNWind & ";"
conn.CursorLocation = adUseClient
Set rs = conn.Execute("Orders", , adCmdTable)

' Create a new Workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

' Transfer the data to Excel
oSheet.Range("A1").CopyFromRecordset rs

' Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit

'Close the connection'
rs.Close
conn.Close
  • Create a Query Table on a Worksheet
A QueryTable object represents a table build from data returned from an external data source. It is created by providing a connection string to an OLEDB or ODBC data source along with a SQL string. Excel generates the recordset and inserts that data at the specified location.

' Create a new Workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

' Create the Query Table
Dim sNWind as String
sNWind = "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
Dim oQryTable as Object
Set oQryTable = oSheet.QueryTable.Add( _
  sNWind & ";", oSheet.Range("A1"), "Select * from Orders")
oQryTable.RefreshStyle = xlInsertEntireRow
oQryTable.Refresn False

' Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
  • Use the Clipboard
You can use the Windows clipboard to paste data into multiple cells on a worksheet. You copy a string where columns are delimited by tab characters and rows by carriage returns.

' Copy a string into the clipboard
Dim sData As String
sData = "FirstName" & vbTab & "LastName" & vbTab & "Birthdate" & vbCr _
  & "Bill" & vbTab & "Brown" & vbTab & "02/05/85" & vbCr _
  & "Joe" & vbTab & "Thomas" & vbTab & "01/01/91"
Clipboard.Clear

Clipboard.SetText sData
  
' Create a new Workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add

' Paste the data
oBook.Worksheets(1).Range("A1").Select
oBook.Worksheets(1).Paste

' Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
  • Create a Delimited Text File that can be Parsed into Rows and Columns.
Excel can open a tab or comma delimited file and parse the data into cells. This method can transfer data with little or any Automation.

' Create a Recordset from all the records in the Orders table
Dim sNWind As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim sData As String
sNWind = "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sNWind & ";"
conn.CursorLocation = adUseClient
Set rs = conn.Execute("Orders", , adCmdTable)

' Save the recordset as a tab-delimited file
sData = rs.GetString(adClipString, , vbTab, vbCr, vbNummString)
Open "C:\Text.txt" For Output As #1
Print #1, sData
Close #1

' Close the Connection
rs.Close
conn.Close

' Open the new text file in Excel
shell "C:\Program Files\Microsoft Office\Office\Excel.exe " & _
  Chr(34) & "C:\Text.txt" & Chr(34), vbMaximizedFocus
If your file has a .CSV extension Excel opens the file without displaying the Text Import Wizard assuming that the file is comma-delimited. Similarly if your file has a .TXT extension, Excel automatically parses the file using tab delimiters. You can use Automation to populate the Worksheet.

' Create a new instance of Excel
Dim oExcel as Object
Dim oBook As Object
Dim oSheet as Object
Set oExcel = CreateObject("Excel.Application")

' Open the Text File
Set oBook = oExcel.Workbooks.Open("C:\Text.txt")

' Save As Excel Workbook and Quit
oBook.Save "C:\Book1.xls", xlWorkBookNormal
oExcel.Quit