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

Last reviewed: September 17, 1997
Article ID: Q115237
3.00 WINDOWS kbprg kbfile kbcode kbhowto

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

Even with the setting at the minimum of 1 second, you need to allow the database engine some background processing time to actually drop the connection. The database engine must enter an idle loop to actually close the connection. To ensure that this happens, place the following code after the database close method:

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

The timer loop ensures that enough time has elapsed since the database close statement, and the FreeLocks statement allows the Microsoft Access database engine to enter an idle loop to finish background processing.

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) 

For more information about downloading files from the Microsoft Software Library, please see the following article in the Microsoft Knowledge Base:

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

The installation program will not copy over a newer version of the DLL, so the possibility exists that even if you ship the older driver (the one that works correctly) with your project, the user may have the newer version already installed. For example, if the newer version is 1.02.3231 and the one you ship is 1.01.2920, the installation program will leave the newer version 1.02.3231 on your customer's computer.

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

The tab stops are specified in dialog units. On the average, each character is about four horizontal dialog box units in width. The tab stop values must be in increasing order. The tabs work the way typewriter tab stops work. Once a tab stop is overrun, a tab character moves the cursor to the next tab stop. If the tab stop list is overrun (that is, if the current position is greater than the last tab stop value), the default tab of eight characters is used.

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.


KBCategory: kbprg kbfile kbcode kbhowto
KBSubcategory: APrgDataOther
Additional reference words: 3.00
Keywords : APrgDataOther kbcode kbfile kbhowto kbprg
Version : 3.00
Platform : WINDOWS


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 17, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.