Copy Access table with primary keys-CodePudding

Using C#, ODBC and the Jet 4.0 engine to work with an (old) Access database. The task requires that I do not use DAO.

The Microsoft Jet 4.0 database engine unfortunately does not support the "RENAME" keyword. All of the following throw an exception:

RENAME TABLE old_name TO new_name;
RENAME old_name TO new_name;
ALTER TABLE old_name RENAME TO new_name;

But the following syntax does work:

SELECT * INTO new_name FROM old_name;

The problem is that the primary keys are not copied.

Is there a way to create a copy of a table, and in the copy preserve which fields are primary keys?

CodePudding user response：

Using only documented SQL commands, it's not possible. See the

However, a user could have created the index and given it a different name.

So, for example, I can go into the above table, and re-name Primary key to anything I want - say this:

So, not really great.

However, while the application and "most" of your code could stick to ODBC?

If you use oleDB, then you CAN WITH 100% reliability get the PK, and you not had to reference DAO.

So, while the above shows we re-named the PK with "zoo" on the end, this oleDB code will STILL return the PK column like this:

Sub GetoleDBGetKey()

    Using con As New OleDbConnection(My.Settings.TESTAce)
        Using cmdSQL As New OleDbCommand("SELECT * from PKTEST", con)

            con.Open()
            Dim schemaTable As DataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys,
                            New Object() {Nothing, Nothing, "PKTEST"})

            Me.DataGridView1.DataSource = schemaTable
        End Using
    End Using
End Sub

So, oleDB will work 100%.

The ODBC snip above? Well, we would have to assume "PrimaryKey", and this is not really a requirement (that it is/was named "PrimaryKey")

So, you could consider oleDB.

I not dug really deep, but I don't think ODBC can get the PK with reliability.

You could play with the odbc getSchema I have above. There are some code snips that suggests that you open connection, open a reader, and use getschema of the data reader, and that DOES work - but I don't have a working example (or even know if this can work - but there is some suggesting that this can/does work).