GRANT UPDATEIDENTITY?

L

LMC

I am using MS-Access 2003 SP3. The code below compiles but errors on the
db.Execute line when the code is run.

Dim strUser as String, strSQL as String, strTbl as String
Dim db as DAO.Database

Set db = CurrentDB()
strUser = NetworkUserName
strSQL = "GRANT UPDATEIDENTITY ON " & strTbl & " TO " & strUser
db.Execute strSQL, dbFailOnError

...............additional code...............

db.Close
Set db = Nothing

strUser is the result of a function that correctly returns the network user
id of the person currently logged in. There is no further security set on the
database other than Active Directory permissions applied to the server where
the database resides.

strTbl is a variable string that accurately holds the table name of the
AutoNum field that I want to be updatable via code.

When I enter the SQL below, without the variable strings, into the standard
Query window to test it, it produces the following error upon saving the data
definition query. SQL statement; expected 'DELETE', 'INSERT', "PROCEDURE',
'SELECT", or 'UPDATE'.

GRANT UPDATEIDENTITY ON TableName TO CurrentUser

I've looked on the web and in MS-Access Help for examples of code using this
option; but have found only definitions that give the general syntax and say
that it will allow updating of numbers in AutoNum fields.

Any Help is appreciated.

LMC
 
L

LMC

Thanks for responding Dave. The table name is passed via the function call;
see clarified code below. strSQL gets recognized as... "GRANT UPDATEIDENTITY
ON copy_tblDOC to p54890". For testing purposes I currently have all the
additional code lines rem'd out. My testing theory expects that I would run
the code with the rem'd lines, then manually open the table and be able to
manually edit the numbers in the AutoNumber field. Is that an accurate
assumption to expect?

Function fncImport (strTbl as String)

Dim strUser as String, strSQL as String, strTbl as String
Dim db as DAO.Database

'...............additional rem'd code...............

Set db = CurrentDB()
strUser = NetworkUserName
strSQL = "GRANT UPDATEIDENTITY ON " & strTbl & " TO " & strUser
db.Execute strSQL, dbFailOnError

'...............additional rem'd code...............

db.Close
Set db = Nothing

...............additional code...............

End Function
 
D

Douglas J. Steele

I don't think UPDATEIDENTITY works that way with Autonumber fields. I
believe it's only for the Identity data type in SQL Server.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top