Find AutoIncrement & Primary Key fields in ADODB Recordset

[G_Hosa_Phat]

Hello again, everyone. I've run into yet another issue I can't seem to overcome with any of the resources at my disposal. Is it possible to determine if a field in an MS Access (2003) table is AutoIncrement and/or a Primary Key by looking at an ADODB Recordset from that table?

I'm trying to convert data from an Access database over into MySQL. As I go through each of the tables in my Access database, if that table doesn't already exist in the MySQL database, I need to create it. I'm building a SQL command string by looping through the field names of an ADODB recordset from the Access database. During this loop, I would like to identify those fields that are setup as AutoIncrement/Primary Key fields so that I can include those in my SQL statement appropriately.

    Private Sub CreateNewDestinationTable(ByRef TableName As String)
        Dim X As Integer

        DDBCommand = "CREATE TABLE [" & TableName & "] ("

        For X = 0 To OTempRS.Fields.Count - 1
            DDBCommand = DDBCommand & "[" & OTempRS.Fields(X).Name & "] "

                     DDBCommand = DDBCommand & "VARCHAR(" & OTempRS.Fields(X).FieldSize & ")"

            ' --- Here is where I would add the conditional statement to include
            ' --- the appropriate SQL commands to create the field as a Primary
            ' --- Key and/or AutoIncrement field as needed.

            If X < OTempRS.Fields.Count - 1 Then
                DDBCommand = DDBCommand & ", "
            End If
        Next X
        
        DDBCommand = DDBCommand & ")"
    End Sub

I'm sure I could probably pop up a dialog to request user intervention to identify these types of fields, but it would be nice if I didn't have to. Any ideas or suggestions would be greatly appreciated. Thanks for your time.

[brucevde]

Use the Fields.Properties collection to find some schema information.

The values loaded into the collection depend on how you open the recordset but you get into a catch-22 situation. I only have Access 2002, hopefully it is different with Access 2003.

If the CursorLocation is adUseServer then you can get the AutoIncrement Property.

rs.Fields(0).Properties("IsAutoIncrement").Value

But to get the Key information the CursorLocation needs to be adUseClient.

rs.Fields(0).Properties("KeyColumn").Value

I haven't checked all the possible combinations of the CursorTypes and LockTypes. Maybe there is one that causes ADO to return all information.

Failing that, the Connection.OpenSchema method might work for you. Note that its functionality is not fully implemented in all providers.