Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    9

    Unanswered: Access & ADO/ADOX Problems

    I am trying to compare two different versions of an Access97 database in order to obtain their differences and then update a database from those differences. And I got those problems:

    1.- How can I change the column index? I mean, in ADOX the indexes of a column collection are ordered by name, and they are not the real indexes. In DAO it was possible to set and get the real index of the column in the table, but with ADOX I cannot see how to do it. Is it possible??? How???

    2.- Is it possible to create a relation one-to-one?? I tried with keys. It is supposed that when you create a "foreign key" between two columns that are unique, the relation becomes unique (a "unique key" is created atomatically at least when I do it via Access). When I create that foreign key, strange things happen. I create it with ADOX and when I see the relations with access, it appears as one-to-multiple (drawn like 1 to infinite), but when I edit the relation it says it is ono-to-one!!!!! Does anybody how to do this?? Is this a bug??

    3.- Is it possible to create a relation with no referential integrity? How?

    Thank you

  2. #2
    Join Date
    Nov 2003
    Posts
    9

    Re: Access & ADO/ADOX Problems

    Any suggestion please???

  3. #3
    Join Date
    Aug 2003
    Location
    Belgrade
    Posts
    68

    Re: Access & ADO/ADOX Problems

    Your post is not quite clear about what your problem is.

    1. What do you mean by "change the column index"? You can always delete an existing index and recreate a new one, whatever it is. As for "index order", in the ADOX Indexes collection, the names of the members are not ordered at all. As in any collection, the order of the members is the order of insertion.

    If you need to compare indexes between two databases using ADOX, you can't rely on their order in the ADOX Indexes collection. Use a For ... Each loop to find out whether a particular index exists and if it does, whether it has the same properties as another given index.

    Also, I am not sure what kind of an index could be a "not real" index. Access has just indexes, neither real, nor virtual.


    2. It is probably possible to create a one-to-one relationship with ADOX. But I see nothing wrong in a relationship defined as one-to-many, and in which on the "many" side you actually have just one correponding record for each record on the "one" side. It works all the same. I suggest you have a look and do some research at http://msdn.microsoft.com/library/de...adoddlx2_2.asp and http://www.vbcity.com/forums/faq.asp...ccess#TID41536

    3. I suppose you can do that if you set to adRINone the UpdateRule and DeleteRule properties of the Key object. But why bother creating a relationship if you don't want RI enforced? The tables will appear already related in the Query window, but I see no other advantage.

  4. #4
    Join Date
    Nov 2003
    Posts
    9

    Re: Access & ADO/ADOX Problems

    I am migrating a code from DAO to ADO, and the databases are already created (maybe they have nosense but I cannot change this).

    1) Sorry, when I speak about index is not a table index, but the index of the column in the collection, the number you use to get the column.

    In DAO I have CDaoFieldInfo::m_nOrdinalPosition, and I need to migrate this to ADO. I do not know exactly the meaning of this number but I could read that it is not the same than in the ADOX column collection. I also read that order in a collection are by name, and not the order of insertion. Are you sure about that?

    I need to have the same ordinal position as we also use reports and they take columns by that number (crystal reports print engine).

    2) I'm sure it is posible to create a one-to-one, but I do it and it does not work. Have you tried it? Could you show me some code that works fine creating a one-to-one relation?

    I didn't define that relation in a table, and I cannot change it. I don't know the reason to make a one-to-one relation but it is already done. I try to recreate the same relation and it fails.

    3) The same than before :-) I don't know why, but it is already done. And I need to do it again. And the relation is defined like that. I think adRINone doesn't work because I read from MSDN that it is not possible to do it :-) Do you know another way? How is that possible? Anyway I will try that again.

    Thank you

  5. #5
    Join Date
    Nov 2003
    Posts
    9

    Re: Access & ADO/ADOX Problems

    I checked that for referential integrity and it does not work using adRINone. It just set unchecked the corresponding delte or update rule.

  6. #6
    Join Date
    Aug 2003
    Location
    Belgrade
    Posts
    68

    Re: Access & ADO/ADOX Problems

    Originally posted by pabgarde
    In DAO I have CDaoFieldInfo::m_nOrdinalPosition
    This is not VBA code. What is it? The OrdinalPosition property of a DAO Field object means just the order in which the field will appear in Recordset objects. Initially it is the order of insertion of the field in the table definition, but you can change it to anything you want. I suggest you look under "OrdinalPosition" in Access Help.


    I need to have the same ordinal position as we also use reports and they take columns by that number (crystal reports print engine).
    Yes, you probably do need to set the OrdinalPosition property if the Crystal Reports print engine expects it. Well, that is tough luck. ADOX doesn't have an explicit OrdinalPosition property. This is a Jet specific property and if it can be set, it will be only using the Properties property of the ADOX Column object. Unfortunately, I don't know the syntax. Try to search the Microsoft site under "ADOX".
    Another alternative is to create queries that retrieve fields in the right order and reprogram Crystal Reports to use those queries as data sources. It is a good practice not to read directly from tables but to create views/queries that provide only data necessairy for a particular task.


    2) I'm sure it is posible to create a one-to-one, but I do it and it does not work. Have you tried it? Could you show me some code that works fine creating a one-to-one relation?
    No, I never use them and in about ten years of database programming I have encountered maybe two or three cases of a real one-to-one relation. I treated them as a one-to-many relations without any problem.


    I didn't define that relation in a table, and I cannot change it. I don't know the reason to make a one-to-one relation but it is already done. I try to recreate the same relation and it fails.
    What happens if you replace it with a one-to-many relation?


    3) The same than before :-) I don't know why, but it is already done. And I need to do it again. And the relation is defined like that. I think adRINone doesn't work because I read from MSDN that it is not possible to do it :-) Do you know another way? How is that possible? Anyway I will try that again.
    Try to set only the RelatedColumn property of the ADOX Column object. Where in the MSDN did you read it is impossible to use adRINone?

    Generally, looks like you have ihherited a nice mess :-))

  7. #7
    Join Date
    Nov 2003
    Posts
    9

    Re: Access & ADO/ADOX Problems

    Originally posted by mashinovodja
    This is not VBA code. What is it? The OrdinalPosition property of a DAO Field object means just the order in which the field will appear in Recordset objects. Initially it is the order of insertion of the field in the table definition, but you can change it to anything you want. I suggest you look under "OrdinalPosition" in Access Help.


    Yes, you probably do need to set the OrdinalPosition property if the Crystal Reports print engine expects it. Well, that is tough luck. ADOX doesn't have an explicit OrdinalPosition property. This is a Jet specific property and if it can be set, it will be only using the Properties property of the ADOX Column object. Unfortunately, I don't know the syntax. Try to search the Microsoft site under "ADOX".
    Another alternative is to create queries that retrieve fields in the right order and reprogram Crystal Reports to use those queries as data sources. It is a good practice not to read directly from tables but to create views/queries that provide only data necessairy for a particular task.


    No, I never use them and in about ten years of database programming I have encountered maybe two or three cases of a real one-to-one relation. I treated them as a one-to-many relations without any problem.


    What happens if you replace it with a one-to-many relation?


    Try to set only the RelatedColumn property of the ADOX Column object. Where in the MSDN did you read it is impossible to use adRINone?

    Generally, looks like you have ihherited a nice mess :-))

    There is no property to change that ordinal position through ADOX :---(

    I cannot replace with a one-to-many. I need to recreate the same relation and is one-to.on. If you get the keys for a one-to-one relation with ADOX, you get a foreign key and a unique key with the same name. If I try to created the same relation (and so the same keys) I get an error when creating the unique key. Is it possible to create thos two keys and so make my relation one-to-one?????

    It is not impossible to use adRINone, but it is just to set the delete or updat rule to none, and not to make the relation not RI enforced, and that is what I need to do.

    Maybe it is no a mess, but my english :-P I have been working on this for 1 month and those are the 3 things I could not solve how to do it!!!!

    Sorry for the mess and thenak you for the answers.

    Any other idea????

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •