Rebuilding Access adp in Access 2007 and getting error

Question

I'm trying to rebuild an Access adp application in Access 2007. I started with a blank database, linked the SQL Server tables I need with ODBC, and then copied over all of the forms from the original application. I then updated the data sources for the forms to go to the correct tables.

When I open one of the forms, I get the error "Syntax error in WITH OWNERACCESS OPTION declaration". This error is occurring before the Open event code is being run.

Does anyone have any ideas about how to track down where this error is coming from? Or maybe an idea of another way to do this altogether?

Thanks!

Answer 1

WITH OWNERACCESS OPTION makes no sense in an ADP, as it's a command for Jet SQL for use with Jet user-level security. With an ADP, the security should all be set on the server. So, you should remove all the WITH OWNERACCESS OPTION statements from your form RecordSources.

Frankly, I'm not sure they have an effect in anything other than a saved QueryDef, so likely they were an error in the original application.

Also, I would never recommend copying anything from an MDB into an ADP. First off, I don't believe migrating to an ADP is ever advisable (though I certainly use ADPs as an administrative tool with my MDB/ODBC apps that use SQL Server), but secondly, there are so many differences between the two platforms that I just can't see them working.

If I chose to migrate an MDB to ADP (and I can't imagine that I ever would, given that MS has been deprecating ADPs for about half a decade now), I would start from scratch and rebuild it from the ground up.