Top Contributors

Move away from ADP

We've got some older projects with a client that use Access ADP projects against SQL server.  I know this isn't supported with Access 2013.

Both projects are currently in Access 2010.  1 hits a SQL 2000 server (yes old). The other hits a SQL 2012 server.

There's a ton of code in vba, some of which has SQL in it, others use ADO and stored procedures on the server side.

At some point they're going to move to Access 2013.  Sure we could keep Access 2010 installed in parallel, but I'm looking for a solution to easily migrate these 2 databases without having to recode everything in .net.


Rank: Prodigy

Expert Comment

Jim Dettman (EE MVE)2015-02-18 12:22 PMID: 145541
Well just because ADP's have gone away doesn't mean you can't still use Access still with SQL.

You can use DAO or ADO via ODBC, still do pass-through, stored procedures, etc, just that you'll need to do the SQL stuff through SSMS as you won't have the native interface from Access.

Rank: Sage

Author Comment

Berkson Wein2015-02-18 05:18 PMID: 145560
Been using SSMS forever for all of the SQL stuff.  Crazy to have tried that through Access, especially stored procedure debugging.

SO what happens to the adp?  Do you suggest creating a new access db and importing the forms, modules, and reports?   Do you need to link to each used stored procedure as a pass through (specifying each one at setup time and manually adding new ones as they're added to the db)?  

When you link a stored procedure using a pass-through and that stp was used as the recordsource for a form, will the stp still be able to be referenced the same way?
Rank: Prodigy

Expert Comment

Jim Dettman (EE MVE)2015-02-19 07:44 AMID: 145623
Yes.  You create a blank accdb and then import everything but the tables.   Then in the new DB, link to the tables via ODBC.

  You'll need to change all the recordsources that use a SP over to a DAO or ADO object that you create (which can call a SP).
  Beyond that, it's hard to say what you'll need to do.  You can link to a view, call SP's from DAO or ADO via pass-throughs, use them for recordsets for forms, etc.  But some of the SP's might need to change and some things you might want to replace with VBA .  Record validation might be moved to the forms as well.

 A lot depends on the app and what it's having to do.  It's not an easy chore overall though.

Rank: Genius

Expert Comment

Nick672015-02-19 09:09 AMID: 145636
Do you need to link to each used stored procedure as a pass through (specifying each one at setup time and manually adding new ones as they're added to the db)?  

 When you link a stored procedure using a pass-through and that stp was used as the recordsource for a form, will the stp still be able to be referenced the same way?

I haven't done it myself, so you need to take much of it with a grain of salt, but the migration can be done.  Where an ADP sees what's on the server natively, an MDB or ACCDB you have to make explicit links to.  You can link to tables and views.  Anything where you can tell Access what should serve as a primary key can be read-write (tables, views, indexed views)  The rub is sprocs.

You can utilize sprocs.  You create a passthrough query, and give the SQL as straight-up T-SQL syntax commands
exec QryFREquipType 25
for example, executes my stored procedure QryFREquipType  and passes in 25 as the parameter
exec spExcelViewTest1 465, Null, Null, Null, Null, Null, Null
Multiple and optional parameters example

BUT sprocs return read-only recordsets.  And that's a problem for a form, needless to say

So for each form based on a stored procedure, you are going to have to decide how you will alter it to get around this problem.
Will you code for CRUD operations, or will you alter the recordsource to an Access query that does the same job that the sproc did?
That's the pain point.

In code, you can alter the passthrough queries as needed dynamically
dim db as database
dim qdf as querydef
set db = currentdb
set qdf = db.querydefs("pthrQryFREquipType")
qdf.SQL = "exec QryFREquipType 7"

But your ADP needed none of that, so a lot must change
Rank: Genius

Expert Comment

Bitsqueezer2016-02-11 07:45 AMID: 1894995

although that's an older thread, just to set the things right: A recordset returned by an SP is of course NOT read-only. It's only the strange Pass-Through-Query-Setting which MS made that all PTs are read-only (not understandable, no reason to do that).

You can continue to use the same SP to edit records (if that would not be possible it would also not be possible in an ADP...). You can also use table functions to edit records.

To successfully migrate an ADP to an ACCDB without changing anything to linked tables and DAO (would be a hell to convert) you only need to add the creation of a recordset for the form via VBA, you cannot, like in an ADP, use the name of an SP in the RecordSource property nor do you have an "InputParameters" property to set the variables for an SP.

If that's the case you must change each form using that method to execution of the SP with returning as ADO recordset in the ADP first (to make it easier to test that everything is still running) by using VBA in the Form_Open or Form_Load event. That's no problem as you can use a global Connectionstring variable to save the connection string you normally used for the server connection on ADP level which will be no longer available in ACCDB later.

Then you must use the right driver: OLEDB has to be used to make a form updatable. SQLNCLI will leave the recordset read-only (can be used as alternative for read-only forms or reports). Of course the recordset must be created in a way that it is updatable on the server which can be tested with SSMS.

Then set the Form Recordset object to the ADO recordset object you got from any source of the server (SP, View, UDF, SQL String in frontend (not recommended)). Forms can work with DAO and ADO the same way in ADP and ACCDB, no problem (a not well-known feature).

The only thing which will be a problem here is pressing F5 to refresh the form's contents. Access is not able to refresh a recordset on this way which was created by directly assigning a recordset to the form as long as also variables for SP or UDF was used, it cannot restore them and run the execution again. So you need to create your own function to refresh by using the AUTOKEYS macro and assigning all keyboard shutcorts for refresh to an own function which will find out the active form (Screen.ActiveForm) and call a procedure in the form to refresh itself. This can be easily done by implementing an interface sub so you can't forget to implement such function to each and every form.

I use that method successfully in my current ADPs (which I'm still going on to develop) so I could easily migrate the complete thing to an ACCDB at every time if I'm forced to do that. All my forms are based on SPs with recordset assignment. Same with all comboboxes (I do not use listboxes but of course it's the same here).

You should move each and every SQL command to the backend using views, SPs or UDFs, makes the whole application to be easily migrated to a complete other frontend like .NET, JAVA, PHP etc. later.