From: jkeats@melbpcDeleteThis.org.au   
      
   Raoul Watson wrote:   
   > Jason Keats wrote:   
   >>>   
   >  >>    
   >>   
   >> I guess I still don't understand your question. What do you mean by "I   
   >> am looking to find out if the primary key field requires a unique   
   >> value."?   
   >>   
   >> How can it not?   
   >   
   > Over 10 years ago, I have an app, which database was created in the jet   
   > space like so:   
   >   
   > Set MyWs = DBEngine.Workspaces(0)   
   > Set MyDb = MyWs.CreateDatabase(dbFileName, dbLangGeneral)   
   > ' Create new TableDef for extension table.   
   > Set mytd = MyDb.CreateTableDef(gTable)   
   > ' Add fields to MyTableDef.   
   > For x = 1 To maxfield   
   >       Set AuFlds(x) = mytd.CreateField(fieldname(x)),fieldtype   
   >     ' don't allow null   
   >       AuFlds(x).AllowZeroLength = false   
   > Next   
   >   
   > For x = 0 To maxfield - 1   
   >     mytd.Fields.Append AuFlds(x)   
   > Next x   
   >   
   > ' Now add an Index.   
   > Set AuIdx = mytd.CreateIndex(gKey)   
   >     AuIdx.Primary = True  '***PRIMARYKEY   
   >     AuIdx.Unique = False  '***NOT unique allowed   
   >   
   > Set IxFlds(0) = AuIdx.CreateField(gKey)   
   > ' Append Field to Fields collection of Index object.   
   > AuIdx.Fields.Append IxFlds(0)   
   > ' Append Index to Indexes collection.   
   > mytd.Indexes.Append AuIdx   
   > ' Append TableDef to TableDefs collection.   
   > MyDb.TableDefs.Append mytd   
   > MyDb.Close   
   > '======================================================   
   >   
   > Years later, I converted the user database to require a unique key.   
   >   
   > So now basically, I have two customer bases, those with the older   
   > database and those with the newer (they are all over ten years old).   
   >   
   > I am about to revamp my app but unfortunately, using my ADODC control, I   
   > just don't know how to test the index field to find out whether it is   
   > the oldest database or a newer one.   
   >   
   > I have a way around it by doing things unsupported in the older database   
   > and catch the errors, but I figured there has to be an easier way to   
   > test the index keyfield.   
   >   
   > Like I said, if I use Jet for the data control, I can test it by using:   
   > If DBControl.Database.TableDefs("tblname").Indexes("fieldname").Unique   
   > Then 'it is a real old DB   
   >   
   > Hope that explains it.   
      
   Yes, that helps. You're using a Jet database, but have switched from DAO   
   to ADO. Your original database somehow had a primary key index that   
   wasn't unique, which you later changed to be so. You now want to be able   
   to distinguish between the two.   
      
   Unfortunately, I don't have Access installed - so I can't play around   
   with Jet databases and various indexes, etc. That just means I can't   
   properly test the following code with a Jet database. As I said before,   
   it works for me with SQL Server. However, I use Identity (ie, database   
   generated) primary keys.   
      
   You still haven't said whether you're using Autonumber PKs, or are   
   creating the PK yourself.   
      
   You're correct in that DAO offers more than ADO in terms of providing   
   information about a database and the means to modify it.   
      
   That's why they also provide ADOX. You should look into that.   
      
   I don't use data controls (ie ADODC) myself (preferring everything to be   
   in code), but I'd be curious whether something like the following is of   
   any use to you.   
      
   You can call it (from the form containing the ADODC control) using   
   something like: AdodcTest Me.Adodc1, "PKfield"   
      
   Public Sub AdodcTest(ByRef dc As Adodc, ByVal vField As Variant)   
        Dim rst As ADODB.Recordset   
      
        Set rst = dc.Recordset   
      
        If rst Is Nothing Then   
            Debug.Assert False   
        Else   
            With rst   
                Debug.Print "BASECOLUMNNAME: " &   
   .Fields(vField).Properties("BASECOLUMNNAME").Value   
                Debug.Print "KEYCOLUMN: " &   
   .Fields(vField).Properties("KEYCOLUMN").Value   
                Debug.Print "ISAUTOINCREMENT: " &   
   .Fields(vField).Properties("ISAUTOINCREMENT").Value   
      
                'Dim j As Integer   
                '   
                'For j = 0 To .Fields(vField).Properties.Count - 1   
                '    Debug.Print .Fields(vField).Properties(j).Name;   
                '    Debug.Print .Fields(vField).Properties(j).Attributes;   
                '    Debug.Print .Fields(vField).Properties(j).Type;   
                '    Debug.Print .Fields(vField).Properties(j).Value;   
                '    Debug.Print   
                'Next j   
            End With   
      
            Set rst = Nothing   
        End If   
   End Sub   
      
   I'm just wondering if the output is any different between the old and   
   new versions of your database. If it is, then that is obviously of some   
   help. If it's not - then tough. :)   
      
   HTH   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)