Search code examples
c#ms-accessodbc

Copy Access table with primary keys

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?

Solution

  • With ODBC, you can get/grab the indexes. Most of the time, the PK column has a index called Primary key. This is not 100% guaranteed.

    So, this would work "most" of the time:

    eg:

    Sub Test22()

    Using con As New OdbcConnection(My.Settings.Test44ODBC2)
        '            Using cmdSQL As New OdbcCommand("SELECT TOP 1 * From PKTEST", con)

        con.Open()

        Dim schemaTable As New DataTable
        schemaTable = con.GetSchema("Indexes", New String() {Nothing, Nothing, "PKTEST"})
        Me.DataGridView1.DataSource = schemaTable

    End Using

 End Sub

    Output:

    enter image description here

    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:

    enter image description here

    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

    enter image description here

    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).