Create DDL from existing tables

[Guus2005]

Hi,

I have a backend which must be shipped with the customer. But it would be easier to just ship the frontend and let it create the backend from SQL scripts.

I want to be able to create make table sql scripts from existing tables and run them if the frontend cant find the backend.
Also some insert statements must be created also.

This functionality is also present in SQL Server. It creates all sql statements to recreate a database.

So in short i am looking for a set of functions (VBA) or an extern program to do this.

Any ideas?
Thanks

 

[Ranman256]

You can just ship both the FE and Backend (as access db).
Then they just relink and they go.

If they have a SQL server, they swap out the access tbl for the SQL.

 

[isladogs]

That's fine for a new FE/BE distribution. However, the problem with that comes when e.g. as part of an FE update, you need to modify the structure of an existing BE file when the client already has data in that file.

My solution for that was to run a SQL script as part of the update relinking process.
On the highly unlikely event of network issues during the update, the script is designed so it first checks if each section has already been applied. This allows the update to be repeated if necessary.
I also supply an undo script for each update so it can be completely reversed.

Attached are an example of each type of script. These are .sql files so double clicking the file will open SSMS. However they are just text files so can be opened in Notepad

Another solution that might interest you is an example DDL extractor developed by UA member David Marten (CheekyBuddha). See https://www.utteraccess.com/forum/index.php?showtopic=2055273

Hope that helps

 

[theDBguy]

Hi. Looks like somebody gave it a try before. There was also a mention of using a third-party application in the discussion.