Microsoft KB Archive/204848

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

Article ID: 204848

Article Last Modified on 11/3/2003

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.01
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q204848



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 you are 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.

Note You must change server=, UID= and PWD= to the appropriate username and password before you run this code. Make sure that the corrected UID has the appropriate permissions to perform the required operations on the specified database. 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= ; UID= ; PWD= ;database=pubs") Set rdoen = rdoEngine(0) rdoen.CursorDriver = rdUseOdbc Set rdocn = rdoen.OpenConnection("", rdDriverNoPrompt, False, _      "Driver={SQL Server};server= ; UID= ; 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= ;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).</li></ol>

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.</li> 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.</li> 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.</li> Place a listbox on the form and copy it three (3) times.</li> Place a CommandButton on the form and copy it once.</li>  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= ;UID= ;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 </li> 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.</li></ol>

Keywords: kbinfo kbdatabase KB204848

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.