HOWTO:Use Temporary Tables in SQL Server from Visual Basic 3.0

The information in this article applies to:

• Professional Edition of Microsoft Visual Basic for Windows, version 3.0

SUMMARY

This article explains how to use Visual Basic code to create and use temporary tables. It uses the Pubs sample database that comes with Microsoft SQL Server, but the issues and pitfalls are similar for other client/server databases.

Programmers taking advantage of the performance of a client/server database, such as a Microsoft SQL Server database, occasionally need to use temporary tables on the server to address situations like these:

• Complex queries that if executed in one SQL Select statement would reduce performance because it would join too many tables or be of too large a scope.
• Complex queries with a syntax that is so complex that there will likely be errors in the formulation of the query. Instead, you could place the results of preliminary queries in temporary tables, and use a simpler query to join the preliminary results into a final result.

MORE INFORMATION

Microsoft SQL Server maintains temporary tables created by queries only as long as the SQL session or connection that created them exists. When a Visual Basic program opens a database, the SQL Server session or connection is maintained as long as that database object, representing that database connection, is in scope and valid.

Maintaining the Connection to the Server

The Microsoft Access database engine that is built into Visual Basic creates a separate connection whenever an ExecuteSQL statement is used. Therefore, to have a persistent connection, you must use the CreateDynaset or CreateSnapshot methods with the DB_SQLPASSTHROUGH option to execute both Select and Action queries. The CreateDynaset or CreateSnapshot methods provide a persistent connection to the server and DB_SQLPASSTHROUGH allows you to execute a Select Into query against an ODBC data source.

Dealing with the Microsoft Access Database Engine's Connection Manager

If you close the database by using the Close method or by allowing the database object variable to go out of scope, the connection to the server is broken. This causes the server to erase the temporary tables. Then when you open the database again, the new connection will not see the temporary tables because they no longer exist, so you will get ODBC or SQL Server errors if you try to use the temporary tables.

However, because of the way the connection manager works, this is only true if the connection has actually been dropped by the Microsoft Access database engine. The database engine's connection manager maintains connections for an interval based on the ConnectionTimeout setting in the VB.INI for VB.EXE or the <appname>.INI file in the case of an <appname>.EXE file.

Example of .INI file contents:

 [ODBC]
 ConnectionTimeout = 1  ; in seconds, default=600

  db1.close
  start = Timer
  While Timer < start + 2
  Wend
  FreeLocks

For additional information on [ODBC] section settings, please see the following article in the Microsoft Knowledge Base:

ARTICLE-ID: Q110227

TITLE     : PRB:Closed ODBC Database Stays Open Until Time-Out or VB Ends

Dealing with a Bug in the ODBC Driver for SQL Server (SQLSRVR.DLL)

The version of the ODBC driver for SQL Server (SQLSRVR.DLL) that is located in the \WINDOWS\SYSTEM directory can have a significant impact on the technique described below. The version of SQLSRVR.DLL dated 28-April-1993 that shipped with Visual Basic version 3.0 works correctly as does the version dated 24-May-1993 that shipped with Microsoft Access version 1.1.

However, newer versions of SQLSRVR.DLL, like the one that shipped with Microsoft Excel version 5.0 and the one that shipped with Microsoft Access version 2.0, have a bug that causes a trappable error when using the technique described in this article. It is a trappable error, and error traps are indispensable when dealing with databases, so the bug should not cause a problem. The bug will be addressed in future versions of SQLSRVR.DLL.

If you choose, you can download the older version of SQLSRVR.DLL that shipped with Microsoft Access 1.1.

The following file is available for download from the Microsoft Software Library:

 ~ Sqlupdt.exe (size: 636209 bytes) 

   ARTICLE-ID: Q119591
   TITLE     : How to Obtain Microsoft Support Files from
               Online Services

Therefore, the example program shown below has error trapping in place so that it will bypass the error caused by the bug should it occur. The errors will appear in a multiple-line text box with ScrollBars. If the SQLSRVR.DLL that has the bug is used, the following errors will occur when the Select Into query is executed through CreateSnapshot:

   Error number 3146 on line 1001 ODBC--call failed.
   [Microsoft][ODBC SQL Server Driver][SQL Server] There is already an
   object named '#sqltemp1' in the database. (#2714)

   Error number 3146 on line 1003 ODBC--call failed.
   [Microsoft][ODBC SQL Server Driver][SQL Server] There is already an
   object named '#sqltemp2' in the database. (#2714)

   Despite the occurrence of the trappable errors, the program will work
   correctly if you trap the errors.

Setting Tab Stops by Using Dialog Box Base Units

You can set tap stops in the list box to align the fields in a snapshot into columns and add a horizontal ScrollBar to the list box by using dialog box base units. Dialog box base units are used to obtain dialog box units. The code below calls the Windows API GetDialogBaseUnits function to get the dialog box base units used by Windows when creating dialog boxes.

You can use the dialog box base units to calculate the average width of characters in the system font. Then you can use that result as a standard to calculate the sizing of controls, and to set tab stops in the list box in this case. The low-order word of the return value contains the width, in pixels, of the current dialog box base-width unit (this base unit is derived from the system font), and the high-order word of the return value contains the height, in pixels.

The values returned represent dialog box base units before being scaled to dialog box units. The dialog box unit in the x-direction is one-fourth of the width returned by the GetDialogBaseUnits function. The dialog box unit in the y-direction is one-eighth of the height returned.

The example code gets the dialog box units from the Windows API; uses a user-defined function LOWORD to obtain DlgWidthUnits; and then divides by it to get dialog box units:

   DlgWidthUnits = LOWORD(GetDialogBaseUnits()) / 4

Steps to Create the Example Program

1. Start a new project in Visual Basic. Form1 is created by default.

2. Add 3 list boxes (List1, List2, and List3), one text box (Text1) and one command button (Command1) to the form. Size and placement will be set by the code in form load procedure.

3. Set the following properties at design time:

      Control   Property     Setting
      --------------------------------------------------
      Text1     MultiLine    True
      Text1     Scrollbars   3 - Both
      Command1  Caption      "Query Using Temp Tables"
   
   

4. Add the following code to the Form Load event:

   NOTE: Some lines of code are wrapped to fit into the article space. Be sure to correct all lines so that each complete code statement is on one, single line.

      Sub Form_Load ()
   
         Dim fwidth As Integer, fheight As Integer
         ' Position and size the form regardless of screen resolution:
         Me.Move 0, 0, screen.Width, screen.Height * .89
   
         fwidth = Me.ScaleWidth
         fheight = Me.ScaleHeight
         ' Position and size the controls regardless of screen resolution:
         list1.Move 0, 0, fwidth / 3, fheight / 2
         list2.Move fwidth / 3, 0, fwidth / 3, fheight / 2
         list3.Move 2 * fwidth / 3, 0, fwidth / 3, fheight / 2
         text1.Move 0, list1.Height, fwidth
   
         ' Size and center the command button:
         ' Enter the following three lines as one, single line of code:
         command1.Move (fwidth - Me.TextWidth((command1.Caption))) / 2,
            list1.Height + text1.Height, Me.TextWidth((command1.Caption)),
            2 * Me.TextHeight((command1.Caption))
      End Sub
   
   

5. Add the following code to the Command1_Click event:

      Sub Command1_Click ()
         Dim NL As String, sql As String
         ' Line labels are included on data operations to help locate the
         ' statements where errors may occur.
         On Error GoTo localerrhandler
         ' Define newline string:
         NL = Chr$(13) & Chr$(10)
         ' Open a connection to a SQL Server database:
         ' Enter the following two lines as one, single line of code:
         Set db1 =
            OpenDatabase("", 0, 0,"odbc;dsn=texas;database=pubs;uid=sa;pwd=;")
   
         ' Define first preliminary query, putting the result into temp table
         ' #sqltemp1. Return a list of all the titles with their associated
         ' title_id, price last name, and au_id fields. If the title has
         ' multiple authors, return only the first author listed in title
         ' author table:
         ' Enter the following two lines as one, single line of code:
         sql = "select titles.title, price, au_lname, authors.au_id,
            titles.title_id"
   
         sql = sql & " into #sqltemp1 "
         sql = sql & " from authors, titles, titleauthor, titleauthor"
         sql = sql & " where authors.au_id = titleauthor.au_id "
         sql = sql & " and titles.title_id=titleauthor.title_id"
         sql = sql & " and titleauthor.au_ord= 1"
   
         ' Create a snapshot to execute the action query:
      1001 : Set sn = db1.CreateSnapshot(sql, DB_SQLPASSTHROUGH)
   
         ' Create a snapshot to check the results of the query:
         sql = "select * from #sqltemp1"
      1002 : Set sn = db1.CreateSnapshot(sql, DB_SQLPASSTHROUGH)
   
         ' Call Sub procedure to fill list box:
         Fill_List list1
   
         ' Define second preliminary query, putting the result into temp table
         ' #sqltemp2. Return a list of all store names with their associated
         ' stor_address, title, title_id, price, ytd_sales fields, and show
         ' which had year-to-date sales greater than $10,000.00
         ' for particular titles.
   
         sql = "SELECT stor_name, stor_address, sales.title_id,"
         sql = sql & " title, ytd_sales, price"
         sql = sql & " into #sqltemp2 "
         sql = sql & " from sales, stores, titles"
         sql = sql & " where titles.title_id=sales.title_id"
         sql = sql & " and sales.stor_id=stores.stor_id"
         sql = sql & " and titles.ytd_sales > 10000.00"
   
         ' Create a snapshot to execute the action query:
      1003 : Set sn = db1.CreateSnapshot(sql, DB_SQLPASSTHROUGH)
   
         ' Create a snapshot to check the results of the query:
         sql = "select * from #sqltemp2"
      1004 : Set sn = db1.CreateSnapshot(sql, DB_SQLPASSTHROUGH)
   
         ' Call Sub procedure to fill the list box:
         Fill_List list2
   
         ' Define a simple query that joins
         ' the two temp tables, #sqltemp1, #sqltemp2:
         sql = "select #sqltemp1.au_lname, #sqltemp2.ytd_sales,"
         sql = sql & " #sqltemp2.stor_name, #sqltemp2.title"
         sql = sql & " from #sqltemp1 , #sqltemp2"
         sql = sql & " where #sqltemp1.title_id= #sqltemp2.title_id"
   
         ' Create a snapshot to fetch the results of the query:
      1005 : Set sn = db1.CreateSnapshot(sql, DB_SQLPASSTHROUGH)
   
         ' call the Sub procedure to fill the list box:
         Fill_List list3
   
         ' To demonstrate the fact that temp tables
         ' on the SQL Server only persist during the
         ' lifetime of an active session or connection,
         ' execute the following code, and then attempt
         ' to access the temp tables. See the text of
         ' this article for more info.
         '
         '   Set sn = Nothing
         '   1011 : db1.Close
         '   start = Timer
         '   While Timer < start + 2
         '   Wend
         '   FreeLocks
         '
         '   conn$ = "odbc;dsn=texas;database=pubs;uid=sa;pwd=;"
         '1006 : Set db1 = OpenDatabase("", 0, 0, conn$)
   
         ' Clean up temp tables in case you need to re-create
         ' them while this current connection is alive.
         ' If you close the connection, SQL Server will
         ' destroy the temp tables automatically
         sql = "drop table #sqltemp1"
      1007 : Set sn = db1.CreateSnapshot(sql, DB_SQLPASSTHROUGH)
   
         sql = "drop table #sqltemp2"
      1008 : Set sn = db1.CreateSnapshot(sql, DB_SQLPASSTHROUGH)
   
         ' void the pointer
         Set sn = Nothing
      1009 : db1.Close
   
         Exit Sub
         localerrhandler:
         text1 = text1 & "Error number " & Err & " on line labeled " & Erl
         text1 = text1 & " " & Error$ & NL
         Resume Next
   
      End Sub
   
   

6. Add the following code to the general declarations level of the form:

   Option Explicit

      ' Enter the following Declare statement as one, single line:
      Declare Function SendMessage Lib "User" (ByVal hWnd As Integer,
         ByVal wMsg As Integer, ByVal wParam As Integer,
         lparam As Any) As Long
      Declare Function GetDialogBaseUnits Lib "User" () As Long
   
      Const WM_USER = &H400
      Const LB_SETTABSTOPS = WM_USER + 19
      ' Add a horizontal scrollbar to the list boxes:
      Const LB_SETHORIZONTALEXTENT = (WM_USER + 21)
      Const DB_SQLPASSTHROUGH = 64
   
      Dim biggest_value() As Integer
      Dim tabstops() As Integer
      Dim db1 As database
      Dim sn As snapshot
   
      Function LOWORD (lparam As Long) As Integer
         LOWORD = CInt((lparam And &HFFFF&))
      End Function
   
      Sub Fill_List (l As Control)
         Dim i As Integer, listline$
         Dim retval&, DBWidthUnits As Single, fieldpiece As String
         Dim dlgratio As Single, accumpixels As Integer
         Dim TabSpace As Integer, charwidth_pixels  As Integer
         Const numchars = 6   'number of blank characters between columns
   
         ' This Sub accepts a list box control as a parameter
         ' and fills it with the contents of a global snapshot.
   
         ' Set tap stops in the list box to align the fields in the snapshot
         ' into columns by using dialog box units. Then add a horizontal
         ' scrollbar to the list box.
   
         ' The following formula gives DBWidthUnits given pixels
         ' DBWidthUnits= numpixels * 4/LOWORD(GetDialogBaseUnits())
         ' Rearranging the above formula gives pixels given DBWidthUnits
         ' numpixels= DBWidthUnits * LOWORD(GetDialogBaseUnits())/4
         ' For more efficient calculation, calculate a multiplier, based on
         ' the above formulas. Use it to convert pixels to DBWidthUnits:
         dlgratio = 4 / LOWORD(GetDialogBaseUnits())
   
         ' Return the DBunits for width:
         DBWidthUnits = LOWORD(GetDialogBaseUnits()) / 4
   
         ' One character averages 4 * DBWidthUnits in pixels:
         charwidth_pixels = numchars * 4 * DBWidthUnits
   
         ' Calculate the extra space between tabbed fields:
         TabSpace = charwidth_pixels * dlgratio
   
         ' Loop through and display the records:
         l.Clear
         ReDim biggest_value(0 To sn.Fields.Count - 1)
         ReDim tabstops(1 To sn.Fields.Count)
         While Not sn.EOF
            For i = 0 To sn.Fields.Count - 1
               ' In case of nulls in field, promote the NULL to empty string:
               fieldpiece = sn(i) & ""
   
               ' Enter the following two lines as one, single line:
               If Me.TextWidth(fieldpiece) /
                  screen.TwipsPerPixelX > biggest_value(i) Then
   
                  ' Enter the following two lines as one, single line:
                  biggest_value(i) = Me.TextWidth(fieldpiece) /
                     screen.TwipsPerPixelX
   
               End If
               listline$ = listline$ & fieldpiece & Chr$(9)
            Next i
            l.AddItem listline$
            listline$ = ""
            sn.MoveNext
         Wend
   
         For i = 0 To sn.Fields.Count - 1
            accumpixels = accumpixels + biggest_value(i)
            tabstops(i + 1) = accumpixels * dlgratio + (i + 1) * TabSpace
         Next i
   
         ' Send a message to l:
         retval& = SendMessage(l.hWnd, LB_SETTABSTOPS, i, tabstops(1))
   
         ' Add a horizontal scrollbar to single select list box.
         ' The message LB_SETHORIZONTALEXTENT expects the extent to be
         ' specified in pixels. Exploit the fact that the last tabstop
         ' element is not needed (tabs needed are one less than the number
         ' of fields to be tabbed. Store the accumulated length of the entire
         ' line in the last tab stop array element. The length is stored in
         ' DBWidthUnits. Convert it to pixels by dividing with the dlgratio
         ' calculated above.
         ' Enter the following two lines as one, single line of code:
         retval& = SendMessage(l.hWnd, LB_SETHORIZONTALEXTENT, tabstops(i) /
            dlgratio, ByVal 0&)
      End Sub
   
   

7. Save the project and run it. If you are using the SQL Server ODBC driver (SQLSRVR.DLL) that shipped with Visual Basic version 3.0, no error will appear in the text box. Look at the contents of the List1 and List2 boxes to see the contents of the temporary tables. Then look at the List3 box to see the results of the query based on the two temporary tables.