INFO: When Table Name Is Available Using ADO, RDO, DAO

ID: Q204848

The information in this article applies to:

ActiveX Data Objects (ADO), versions 2.0, 2.01, 2.1 SP2
Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0

SUMMARY

It is sometimes desirable to retrieve the sourcetable name for a given field in a query. This can be important for a join query when two tables have fields with the same name.

Table name is not always available when using ADO, RDO, or DAO cursors. Availability depends on the cursor type. When using ADO it also depends on the provider.

In RDO, sourcetable name will not be available if the Cursordriver property of the rdoenvironment is set to rdUseOdbc or rdUseNone. Likewise, in DAO when using odbcdirect, the sourcetable name will not be available if the defaultcursordriver is set to dbUseOdbc or dbUseNoCursor. When using DAO with JET workspaces, the sourcetable name always seems to be available for the allowable combinations of type, options, and lockedits.

In ADO, when using the MSDASQL or the SQLOLEDB providers there is no selection for odbc, and the sourcetable called BASETABLENAME is always available except for the adopenforwardonly and adlockreadonly combination when using the serverside cursor.

MORE INFORMATION

Use the following RDO code to test for the availability of sourcetable name:

Open a Standard EXE project and select Microsoft Remote Data Object 2.0 under Project References. Place two CommandButtons on the form.

Paste the following code in the form code window:


Option Explicit
     Dim rdoen As rdoEnvironment
     Dim rdocn As New rdoConnection
     Dim rdors As rdoResultset
Private Sub Command1_Click()
     Set rdors = rdocn.OpenResultset("select * from authors", _
          rdOpenKeyset, rdConcurValues)
     Debug.Print "sourcecolumn:  " & rdors(0).SourceColumn
     Debug.Print "sourcetable:  " & rdors(0).SourceTable 'Nothing Returned
     rdors.Close
     rdocn.Close
End Sub
Private Sub Command2_Click()
   Unload Me
End Sub

Private Sub Form_Load()
     Set rdoen = rdoEngine(0)
     Set rdocn = rdoen.OpenConnection("", rdDriverNoPrompt, False, _
       "Driver={SQL Server};server=margerys4; UID=sa; PWD=;database=pubs")
     Set rdoen = rdoEngine(0)
     rdoen.CursorDriver = rdUseOdbc
     Set rdocn = rdoen.OpenConnection("", rdDriverNoPrompt, False, _
       "Driver={SQL Server};server=margerys4; UID=sa; PWD=;database=pubs")
     Debug.Print rdoen.CursorDriver
End Sub

Run the above code and note that nothing is printed for the sourcetable name. Change the cursor property to rdUseServer or rdUseClientBatch and run the code again.

Use the following DAO code to test for the availability of sourcetable name:

Create a Standard EXE project and select Microsoft DAO 3.51 Object Library under Project References.

Place one CommandButton on the form and paste the following code in the form code window:


Option Explicit
Dim ws As Workspace
Dim cn As Connection
Dim rs As Recordset
Dim strsql As String

'The following code will return the sourcetable name.
Private Sub Command1_Click()
     Set ws = DBEngine.CreateWorkspace("", "", "", dbUseODBC)
    ' ws.DefaultCursorDriver = dbUseClientBatchCursor  '3
    ' ws.DefaultCursorDriver = dbUseServerCursor       '2
    ' ws.DefaultCursorDriver = dbUseDefaultCursor     '-1
      ws.DefaultCursorDriver = dbUseODBCCursor         '1
    ' ws.DefaultCursorDriver = dbUseNoCursor           '4  
     Set cn = ws.OpenConnection("", , , "ODBC;DSN=mymachine;uid=sa;pwd=;database=pubs")
     strsql = "select * from authors"
     Set rs = cn.OpenRecordset(strsql, dbOpenDynaset, 0, dbOptimistic)          'Use the following if testing the dbUseNoCursor:
     'Set rs = cn.OpenRecordset(strsql, dbOpenForwardOnly, 0, dbReadOnly)
     Debug.Print rs.Fields(0).SourceTable
     Debug.Print ws.DefaultCursorDriver
     rs.Close
     cn.Close
End Sub

No sourcetable name prints with rdUseOdbcCursor. If you uncomment some of the other cursor types, the sourcetable name is printed (except for dbUseNoCursor).

The following ADO code shows when the sourcetable name is available for the various combinations of provider, cursorlocation, and cursortype. Sourcetable name is referred to as BASETABLENAME in the recordset properties for ADO.

The code also prints other recordset properties that are available in the immediate window. Please note the following observations:

A cursortype of adopenforwardonly and adlockreadonly will not allow access to the recordset properties regardless of the settings of the provider and cursorlocation.

When using aduseclient cursorlocation, the cursortype is always adopenstatic regardless of what you set it to be.

Various combinations of cursortype and locktype are accepted. ADO does not allow some combinations or changes the cursortype for some locktypes.

Steps to Demonstrate

Steps 1 through 5 create a very messy looking form. When the form is run, the code arranges all the controls. Do not spend time trying to arrange the controls neatly. You will probably just want to place them so they are not all in one place. This will help you verify that you have the right number of controls on the form.

Create a Standard EXE project and, under Project References, select Microsoft Active Data Objects library 2.0.

Place a textbox on the form. Copy it and paste it on the form eight (8) times. This creates a control array of textboxes that can be referred to as text1(0), text1(1) etc.

Place a label on the form. Copy the label five (5) times. This creates an array of labels that can be referred to as label1(0), label1(1) etc.

Place a listbox on the form and copy it three (3) times.

Place a CommandButton on the form and copy it once.

Paste the following code in the form code window:


      Dim cn As New ADODB.Connection
      Dim rs As New ADODB.Recordset
      Dim cnstr As String
      Dim sqlstr As String
      Dim pr As Property

      Private Sub Form_Load()
      Form1.Height = 6000
      Form1.Width = 8000
      Form1.Top = 0

      Label1(0).Caption = "Make a selection from each listbox. " & _
     "Some combinations will allow you to access the table name " & _
     "of a particular field and other combinations will not. If the " & _
     "table name cannot be determined, a message will print instead " & _
     "of the table name. Notice that the cursortype for aduseclient " & _
     "is always adopenstatic regardless of what is selected."

      Label1(0).Top = 0
      Label1(0).Left = 240
      Label1(0).Width = 6495
      Label1(0).Height = 1335
      Label1(0).Font = "comic sans ms;bold"

      Label1(1).Caption = "Provider"
      Label1(1).Top = 1560
      Label1(1).Left = 120
      Label1(1).Width = 615
      Label1(1).Height = 375

      Label1(2).Caption = "Cursorlocation"
      Label1(2).Top = 1560
      Label1(2).Left = 1800
      Label1(2).Width = 615
      Label1(2).Height = 375

      Label1(3).Caption = "Cursortype"
      Label1(3).Top = 1560
      Label1(3).Left = 3960
      Label1(3).Width = 855
      Label1(3).Height = 375

      Label1(4).Caption = "LockType"
      Label1(4).Top = 1560
      Label1(4).Left = 5640
      Label1(4).Width = 975
      Label1(4).Height = 375

      Label1(5).Caption = "Table Name"
      Label1(5).Top = 4800
      Label1(5).Left = 120
      Label1(5).Width = 975
      Label1(5).Height = 375

      Text1(0).Top = 1560
      Text1(0).Left = 1080
      Text1(0).Width = 495
      Text1(0).Height = 285

      Text1(1).Top = 1560
      Text1(1).Left = 3000
      Text1(1).Width = 495
      Text1(1).Height = 285

      Text1(2).Top = 1560
      Text1(2).Left = 4920
      Text1(2).Width = 495
      Text1(2).Height = 285

      Text1(3).Top = 1560
      Text1(3).Left = 6720
      Text1(3).Width = 495
      Text1(3).Height = 285

      Text1(4).Top = 3240
      Text1(4).Left = 120
      Text1(4).Width = 1355
      Text1(4).Height = 405

      Text1(5).Top = 3240
      Text1(5).Left = 1800
      Text1(5).Width = 1355
      Text1(5).Height = 405

      Text1(6).Top = 3240
      Text1(6).Left = 3720
      Text1(6).Width = 1355
      Text1(6).Height = 405

      Text1(7).Top = 3240
      Text1(7).Left = 5640
      Text1(7).Width = 1355
      Text1(7).Height = 405

      Text1(8).Top = 4800
      Text1(8).Left = 1320
      Text1(8).Width = 2655
      Text1(8).Height = 375

      List1(0).Top = 1920
      List1(0).Height = 1035
      List1(0).Left = 120
      List1(0).Width = 1575

      List1(1).Top = 1920
      List1(1).Height = 1035
      List1(1).Left = 1800
      List1(1).Width = 1575

      List1(2).Top = 1920
      List1(2).Height = 1035
      List1(2).Left = 3720
      List1(2).Width = 1575

      List1(3).Top = 1920
      List1(3).Height = 1035
      List1(3).Left = 5640
      List1(3).Width = 1575

      Command1(0).Top = 3960
      Command1(0).Left = 0
      Command1(0).Width = 7215
      Command1(0).Height = 615
      Command1(0).Caption = "Get Table Name"

      Command1(1).Top = 4800
      Command1(1).Left = 5040
      Command1(1).Width = 1455
      Command1(1).Height = 615
      Command1(1).Caption = "Quit"

      List1(0).AddItem "MSDASQL"
      List1(0).AddItem "SQLOLEDB.1"
      List1(1).AddItem "adUseServer"
      List1(1).AddItem "adUseClient"
      List1(2).AddItem "adopenforwardonly"
      List1(2).AddItem "adopenkeyset"
      List1(2).AddItem "adopendynamic"
      List1(2).AddItem "adopenstatic"
      List1(3).AddItem "adlockreadonly"
      List1(3).AddItem "adlockpessimistic"
      List1(3).AddItem "adlockoptimistic"
      List1(3).AddItem "adbatchlockoptimistic"
      End Sub

      ' The code below places the number of the item selected in the text
      ' box above it.
      ' The number of the item is not always the same as its
      ' place in the list.
      ' The numbers for aduseserver and aduseserver are 2 and 3, respectively.

      Private Sub List1_Click(Index As Integer)
      Select Case Index
       Case 0
        Text1(0) = List1(0).ListIndex
       Case 1
        Text1(1) = List1(1).ListIndex + 2
       Case 2
        Text1(2) = List1(2).ListIndex
       Case 3
        Text1(3) = List1(3).ListIndex + 1
      End Select
      End Sub

      Private Sub Command1_Click(Index As Integer)
       If Index = 1 Then
         Unload Me
         Exit Sub
       End If
       cnstr = "provider=" & List1(0).Text _
       & " ;DRIVER={SQL Server};SERVER=margerys4;UID=sa;PWD=;DATABASE=pubs"
       With cn
        .ConnectionString = cnstr
        .CursorLocation = List1(1).ListIndex + 2
        .Open
       End With

       sqlstr = "Select * from publishers"

       With rs
        .CursorType = List1(2).ListIndex
        .LockType = List1(3).ListIndex + 1
        .ActiveConnection = cn
        .Source = sqlstr
        .Open
       End With

       If rs(0).Properties.Count > 0 Then
       'When using aduseserver with MSDASQL no properties are available.

       If cn.CursorLocation = 2 Then

         'Basetablename is #4 in the list for aduseserver.
         If Not IsNull(rs(0).Properties(3).Value) Then
          Text1(8).Text = rs(0).Properties(3).Value
         Else
          Text1(8).Text = "table name not available"
         End If
       Else

        'Basetablename is #2 in the list for aduseclient.
        If Not IsNull(rs(0).Properties(1).Value) Then
         Text1(8).Text = rs(0).Properties(1).Value
        Else
         Text1(8).Text = "table name not available"
        End If

       End If

       Else
         Text1(8).Text = "no properties for this cursor"
       End If

      'These text boxes show the properties that actually get set.
       Text1(4).Text = cn.Provider
       Text1(5).Text = rs.CursorLocation
       Text1(6).Text = rs.CursorType
       Text1(7).Text = rs.LockType

       Debug.Print cn.CursorLocation & ";" &  _
             rs.CursorType & ";" & rs.LockType

      'The immediate window loops through the properties available.
      'Notice the properties avail for aduseserver and aduseclient are not       'the same.
      Debug.Print "___________________________________"
      For Each pr In rs(0).Properties
        Debug.Print "property is "; pr.Name & "  value is " & pr.Value
      Next pr
      Debug.Print "---------------------end-----------------"

       rs.Close
       cn.Close
       Set rs = Nothing
       Set cn = Nothing
      End Sub

Run the form and make selections in each of the listboxes. Click Get Table Name and note that the table name is displayed in the text box. The immediate window displays other recordset properties that are available. At this point, most of these are empty.

Additional query words: kbDSupport kbdse

Keywords : kbADO200 kbDAO350 kbDatabase kbOLEDB kbRDO200 kbVBp500 kbVBp600 kbGrpVBDB
Version : WINDOWS:2.0,2.01,2.1 SP2,5.0,6.0
Platform : WINDOWS
Issue type : kbinfo