Easy way to promote dev to prod using SQL backend?

Question

Does anyone know of a good way to promote a dev Access application to prod? I have two SQL databases for backends. 1 is Dev the other is Prod. Like a good developer, I'm developing my changes against the dev database. When I promote to Prod, I currently have to delete all the SQL links and recreate them using the Prod database.  This kills all the relationships in the Database Tools/Relations table.  All the relationships in the queries seem to be fine, but not for the base database setup.  Also, after the switch I have to rename all the links so they are the same (removing DBO_ ).  The only alternative that I see is copying everything from a Dev file to a Prod file except for the links to the SQL tables.  This is painful and time consuming.  Any thoughts?

Answer 1

Hi,

You might take a look at this demo from UtterAccess. Hope that helps...

Answer 2

Hi Carl,

>> Easy way to promote dev to prod using SQL backend

In my option, there is no easy way to achieve your requirement, you will need to manually relink the tables with delete the linked tables and relinked it again. It is because TableDef.SourceTableName is used to store Source table name, and it is read-only, we could not change it. Linked table manager would not find the table if the name has been changed.

For a general suggestion, when you design a dev and prod database, you need to keep the table name the same.

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 3

thanks for the tip and thoughts guys. I don't know if the utter access will help or not.  The site says it doesn't work with SQL, but I'll see what I can come up with.  Ed, I do keep the table names the same in prod and dev. As a matter of fact, the dev database is a backup restore of the prod database, so even the data is the same. thanks again.

Answer 4

I'm replying to an old post with the answer to my own question, just in case someone searches for this and needs this answer. 

There is an easy way to switch between the Dev SQL database backend and the Prod SQL database without breaking any of the relationships.

In the External Data tab, select Linked Table Manager.  This is where you update your front end connections with your backend data.  In the Linked Table Manager, at the bottom is a check box for "Always prompt for new location".  Select all your tables and this check box, then click OK.  Access will then prompt you for the ODBC driver to use, select the dev, or prod, driver.  Once you do this it will update all your tables with those on the desired database.  As long as both databases have the same columns in all the tables, everything will seamlessly switch over.