Determining if a SQL Server table has IDENTITY column from MS Access VBA over ODBC

Question

I have an MS Access program that constructs a SQL INSERT statement that inserts rows from an Access table into a linked SQL Server table with the same table structure. The SQL Server table may or may not have an IDENTITY primary key.

The MS Access source table has its corresponding primary key pre-populated, and these values must be inserted into the SQL Server table for referential reasons. SQL Server won't allow this unless I toggle the IDENTITY_INSERT property, but to decide if I need to toggle I need to determine if there is an IDENTITY column in the first place.

So in VBA from MS Access I need to determine if the linked SQL Server table has an IDENTITY column.

This question is covered here but for Transact-SQL.

I actually can get what I need using a pass-thru query, so it is feasible.

Create a pass-thru query to the SQL Server database.
In the SQL view enter
```
SELECT OBJECTPROPERTY(OBJECT_ID(N'table_name'), 'TableHasIdentity') AS 'HasIdentity'
```
and execute, it returns a 1-row datasheet with column HasIdentity that has value 1 if the table has an IDENTITY column.

The problem is that in VBA I cannot get at this datasheet. Given the query runs OK from MS Access I think the datasheet must correspond to a recordset and the following code should work.

Public Function metaODBCExecuteSQL(ByVal pstrConn As String) As Recordset

    Dim qdf As QueryDef
    Dim rst As Recordset

    Set qdf = CurrentDb.CreateQueryDef("ODBC Execute")
    qdf.ReturnsRecords = True
    qdf.Connect = pstrConn 
    qdf.SQL = "SELECT OBJECTPROPERTY( OBJECT_ID(N'table_name'), 'TableHasIdentity') AS 'HasIdentity'"

    Set rst = qdf.OpenRecordset(, dbSeeChanges)

    qdf.Close
    Set metaODBCExecuteSQL = rst

END Function

Instead the set rst line returns a run-time error 3219 invalid operation (with or without dbSeeChanges, which I believe is necessary for working with tables with IDENTITY).

Can anyone point out how I can retrieve the result set from the SELECT OBJECTPROPERTY query? What is wrong with my code, or is there another way.

After all if the MS Access UI can do it there must be a way.

First try this `Set rst = qdf.OpenRecordset(dbOpenDynaset, dbFailOnError + dbSeeChanges)` — 4dmonster, Jul 30 '19 at 06:21

score 1 · Accepted Answer · answered Jul 30 '19 at 07:30

If I create and save the query, this works great here:

? metaODBCExecuteSQL("")(0)
' Returns 0 or 1.

using this function and adjusting table_name to some valid table name:

Public Function metaODBCExecuteSQL(ByVal pstrConn As String) As Recordset

    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset

    Set qdf = CurrentDb.QueryDefs("ODBC Execute")
    qdf.SQL = "SELECT OBJECTPROPERTY( OBJECT_ID(N'table_name'), 'TableHasIdentity') AS 'HasIdentity'"

    Set rst = qdf.OpenRecordset()

    qdf.Close
    Set metaODBCExecuteSQL = rst

End Function

Determining if a SQL Server table has IDENTITY column from MS Access VBA over ODBC

1 Answers1