Generate a new backend

[Korr]

I ran the Database Splitter (Access 2013) and it created two files: mydb.accdb, frontend; mydb_be.accdb, backend. All the table definitions, indicies, relationships, and data were moved to mydb_be.accdb.

I want the client to be able to create a new backend (via File>New) for other topics to run in my frontend. How do I code my frontend to generate a new backend when the table definitions, indices, relationships are no longer in the frontend?

I thought of giving the client a blank backend to use like a template. When they need a new backend they can duplicate the file in File Explorer. This is a simple solution, but not elegant.

Another solution would be to use VBA to create a new accdb, create tables, add fields, create relationships, etc. I loath the thought of having to CreateTableDef, CreateField etc.

Does Access have a code generator? That is to say, I direct Access to my tables and it magically generates VBA subroutines to create the tables and relationships. I call the subroutines in File>New.

Does anyone have a better solution? Thank you.

 

[Access Developer]

I've always had good luck using a "template" table definition -- that is, I
have an empty copy of the real tables, and code to create a new DB, then the
code uses CopyObject to copy the "templates" to the newly created DB. In a
few instances, I've done this so a user could create a new copy for
practice, but mostly I've used it to create temporary databases (rather than
temporary tables in the current DB) -- deleting temporary tables increases
the need for compact and repair, while using temporary databases does not.

If I recall correctly, there may be more detail in the section on temporary
databases at MVP Tony Toews' website, http://www.granite.ab.ca/accsmstr.htm.

 

[gilbert_y]

Thanks for the reply. I'll go with your suggestion.

What is the difference between DoCmd.CopyObject and
DoCmd.TransferDatabase? They seem similar.