Check if a field is in a recordset

Databases
1

I need to see is a field does or does not exist in a MS Access database using the ODBC plugin.
I need to see is a field does or does not exist in a MS Access database using the ODBC plugin.
I have tried using the following code which is based on how i used to do it in VB6.

Dim fld As OLEObject
Dim rs as new OLEObject(“ADODB.Recordset”)
For Each fld In rs.Fields
If fld.Name = fieldName Then
…
…
End If
next
This produces and error message in Real Studio 2012r2.1 saying rs.Fields in not an array.

2

You should be using idxfield which uses an index rather than the field name.

3

Thank you. I have tried using the idxfield but it gives an olerun time exception as the ms access driver does not recognize the field.

4

Sorry David I quickly looked at what you are trying to achieve & didn’t interpret correctly.

In Xojo there are databases & recordsets.

You should look at http://documentation.xojo.com/index.php/ODBCDatabase the examples show how to connect to an Access db and http://documentation.xojo.com/index.php/RecordSet to create your recordset.

HTH

Wayne

5

I see what you want to do David. But your example lacks some parts. Don’t you need to connect to a Access DB first, and get a proper recordset before trying enumerating the fields of this recordset?

6

[quote=22429:@David Simpson]I need to see is a field does or does not exist in a MS Access database using the ODBC plugin.
…
If fld.Name = fieldName Then[/quote]
I’m not completely sure I understand what you are trying to do, but I believe what you are looking for may be FieldSchema

7

Wayne, Rick & Mark

Thanks for your replies.

I have an open connection to a access mdb on W7 and quite happily open recordsets and read , find, delete and update records without and problems using OLEObject(“ADODB.Connection”) and OLEObject(“ADODB.Recordset”).

What I know want do is access the associated “field Object” in the recordset so that I can check to see if fields exists in the recordset and also look at some of the field properties e.g. the field size.

In VB6 there was also an ADODB.Field object that does not appear to be supported in Xojo.
I have yet find and information about the field object.

I will take a look at Fieldschema but I have not come across it before with the plugin

8

Maybe

if myRecordSet.Field(“fieldNameIWantToCheck”) isa databasefield then
//it’s there!

is all you are looking for?

9

Check if it returns rs.Fields.Count or you receive some error.

10

If you get the Count, check if rs.Fields.Item(1).Name gives you the name of the field 1.

If you get those working use a For Loop counting.

11

Rick,

Thanks, That has done the trick.
I had already got as far as rs.Fields.Count giving me the number fields in the record.
I use it to access the field contents sometimes.
I was the rs.Fields.Item(1).Name that did the trick .
It also gives me the other properties that I need like actualsize.

Thanks once again

12

The way to obtain a named Field directly, traversing the ADO structure, is rs.Fields.Item(“MyNamedField”)

I would try to access the field by it’s name and see what occurs then using a non existent name (return nil, causes an exception, …) and created my small [ fieldExist(name, rs) as Boolean ] function using what I discovered.

To obtain or set it’s value rs.Fields.Item(“MyNamedField”).Value

13

“I should try to access the field by it’s name and see what occurs then using a non existent name (retun nil, causes an exception, …) and created my small [ fieldExist(name, rs) as Boolean ] function using what I discovered.”

Trying to access the field by it’s name is exactly what started this problem. The user had set up his own db and had left out one of the fields in a table. So when I try to reference it by name the app falls over with an ole exception. So in future, before trying to reference the field, I will check it exists before hand.

I also need to do the same for the tables in the database as users sometimes try and open the wrong mdb.

14

In MYSQL, I read in the field schema, then I know what fields are in that table. Hopefully, you can do the same thing with Access…I think you can.