Article ID: 129731
Article Last Modified on 12/9/2003
APPLIES TO
- Microsoft Visual Basic 4.0 Professional Edition
- Microsoft Visual Basic 4.0 Professional Edition
- Microsoft Visual Basic 4.0 16-bit Enterprise Edition
- Microsoft Visual Basic 4.0 32-Bit Enterprise Edition
This article was previously published under Q129731
SYMPTOMS
The values of OrdinalPosition for fields in a RecordSet created against an ODBC data source will be different depending on whether dbSQLPassThrough is used or not.
CAUSE
OrdinalPosition is not under the control of Visual Basic DAO for ODBC data sources. This information is retrieved from the back-end database engine.
RESOLUTION
OrdinalPosition on a database should not be expected to be in sync with collection indexes in Visual Basic. To get the OrdinalPosition as defined for the ODBC datasource, dbSQLPassThrough should be used.
STATUS
Regarding: The difference in OrdinalPosition with or without dbSQLPassThrough:
Microsoft has confirmed this to be a bug in the products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.
Regarding: The difference in OrdinalPosition versus the value in collection indexes:
This behavior is by design.
MORE INFORMATION
Steps to Reproduce Problem
- Start a new project in Visual Basic. Form1 is created by default.
Add the following code to the Form1_Load procedure:
NOTE: This example assumes an existing table "userid.table1" with three fields (field1, field2, field3) on an ODBC DSN (mydatasource).Sub Form_Load() Dim Db as Database Dim RS As Recordset, I As Integer Dim TD As TableDef Set DB = Workspaces(0).OpenDatabase("", False, False, _ "ODBC;dsn=mydatasource;UID=userid;pwd=userpassword") Debug.Print Debug.Print "From the TableDef ->" Set TD = DB.TableDefs("userid.table1") For I = 0 To TD.Fields.Count - 1 Debug.Print TD.Fields(I).Name; Debug.Print " Count "; I; Debug.Print " Ordinal Postion "; _ Str$(TD.Fields(I).OrdinalPosition) Next I Debug.Print Set RS = DB.OpenRecordset("SELECT * FROM userid.table1", _ dbOpenSnapshot, dbSQLPassThrough) Debug.Print "From the RecordSet ->" For I = 0 To RS.Fields.Count - 1 Debug.Print RS.Fields(I).Name; Debug.Print " Count "; I; Debug.Print " Ordinal Position ";_ RS.Fields(RS.Fields(I).Name).OrdinalPosition Next I RS.Close DB.Close End Sub
- Start the program by choosing Start from the Run menu or by pressing the F5 key.
The output shows the difference between the enumerated position and OrdinalPosition:>From the TableDef ->
field1 Count 0 Ordinal Postion 0
field2 Count 1 Ordinal Postion 0
field3 Count 2 Ordinal Postion 0
>From the RecordSet ->
field1 Count 0 Ordinal Position 1
field2 Count 1 Ordinal Position 2
field3 Count 2 Ordinal Position 3 Now change the OpenRecordSet line to:
Set RS = DB.OpenRecordset("SELECT * FROM table1", dbOpenSnapshot)
- Restart the program by choosing Restart from the Run menu or by pressing SHIFT+F5.
Now, the output shows the enumerated position and OrdinalPosition in agreement:>From the TableDef:
field1 Count 0 Ordinal Postion 0
field2 Count 1 Ordinal Postion 0
field3 Count 2 Ordinal Postion 0
>From the RecordSet:
field1 Count 0 Ordinal Position 0
field3 Count 2 Ordinal Position 2
field2 Count 1 Ordinal Position 1
Additional query words: 4.00 buglist4.00 vb4win vb4all
Keywords: kbbug kbdatabase KB129731