 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
VB Forum / Database Access / June 2006How to read field properties in ADO RS
I am trying to populate a combo box with the names of all fields in a table except non-autoincrement and memo fields. I am having trouble determining the field properties in ADO. The following procedure shows the ADO code and the DAO code. Thanks, Rich ' Fill a control with all of the FieldNames in a table. Public Sub FillControlWithFieldNames(Obj As Object, TableName As String) Dim i As Long Dim tmpTD As TableDef Obj.Clear Dim rs As ADODB.Recordset Set rs = connMain.OpenSchema(adSchemaColumns) rs.Filter = "TABLE_NAME = '" & TableName & "'" With rs While Not .EOF ' HOW DO I DETERMINE IF THE FIELD IS AN AUTOINCREMENT OR ' MEMO FIELD SO I DON'T ADD IT TO THE CBO? Obj.AddItem UCase(!COLUMN_NAME) .MoveNext Wend End With rs.Close: Set rs = Nothing ' THIS WORKS CORRECTLY IN DAO. ' Set tmpTD = connMain.TableDefs(TableName) ' For i = 0 To tmpTD.Fields.Count - 1 ' With tmpTD.Fields(i) ' If (.Type = dbText Or .Type = dbNumeric Or .Type = dbBoolean Or .Type = dbDate Or .Type = dbSingle Or .Type = dbInteger Or .Type = dbCurrency) And (.Attributes And dbSystemField) = 0 Then ' Obj.AddItem UCase(tmpTD.Fields(i).name) ' End If ' End With ' Next i End Sub ¤ I am trying to populate a combo box with the names of all fields in a table ¤ except non-autoincrement and memo fields. I am having trouble determining the ¤ field properties in ADO. The following procedure shows the ADO code and the ¤ DAO code. ¤ ¤ Thanks, ¤ Rich ¤ ¤ ¤ ' Fill a control with all of the FieldNames in a table. ¤ Public Sub FillControlWithFieldNames(Obj As Object, TableName As String) ¤ ¤ Dim i As Long ¤ Dim tmpTD As TableDef ¤ ¤ Obj.Clear ¤ ¤ Dim rs As ADODB.Recordset ¤ Set rs = connMain.OpenSchema(adSchemaColumns) ¤ rs.Filter = "TABLE_NAME = '" & TableName & "'" ¤ With rs ¤ While Not .EOF ¤ ¤ ' HOW DO I DETERMINE IF THE FIELD IS AN AUTOINCREMENT OR ¤ ' MEMO FIELD SO I DON'T ADD IT TO THE CBO? ¤ ¤ Obj.AddItem UCase(!COLUMN_NAME) ¤ .MoveNext ¤ Wend ¤ End With ¤ rs.Close: Set rs = Nothing ¤ ¤ ¤ ' THIS WORKS CORRECTLY IN DAO. ¤ ¤ ' Set tmpTD = connMain.TableDefs(TableName) ¤ ' For i = 0 To tmpTD.Fields.Count - 1 ¤ ' With tmpTD.Fields(i) ¤ ' If (.Type = dbText Or .Type = dbNumeric Or .Type = dbBoolean Or ¤ .Type = dbDate Or .Type = dbSingle Or .Type = dbInteger Or .Type = ¤ dbCurrency) And (.Attributes And dbSystemField) = 0 Then ¤ ' Obj.AddItem UCase(tmpTD.Fields(i).name) ¤ ' End If ¤ ' End With ¤ ' Next i ¤ ¤ End Sub I don't believe that OpenSchema supports provider specific features. You will probably need to use ADOX instead. Paul ~~~~ Microsoft MVP (Visual Basic) >I am trying to populate a combo box with the names of all fields in a table > except non-autoincrement and memo fields. I am having trouble determining [quoted text clipped - 5 lines] > Thanks, > Rich You don't actually need OpenSchema or ADOX, a plain old recordset has the information you're looking for. Note this code is untested, apologies in advance if there are any typos... ' cn is assumed to be an open connection object ' rs.Open "SELECT * FROM " & TableName & " WHERE (1 = 0)", cn, adOpenStatic Dim f As ADODB.Field For Each f in rs.Fields If (f.Properties("ISAUTOINCREMENT") = False) And (f.Type <> adLongVarChar) And (f.Type <> adLongVarWChar) Then Obj.AddItem f.Name End If Next -Mark > ' Fill a control with all of the FieldNames in a table. > Public Sub FillControlWithFieldNames(Obj As Object, TableName As String) [quoted text clipped - 34 lines] > > End Sub |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.