copy and create table relationships...

[Guest]

Hi all,

I was able to create a new database (called NewDB.mdb) and tables structure
via VBA from a linked database(OldDB.mdb) while in current application
(myProject). Now I would like to copy the existing relationships (OldDB.mdb)
and create the same one to the new created database (NewDB.mdb).

If anyone know how to do it, please help...

Thanks in advance...
Violette

 

[Allen Browne]

Wow, Violette. It would be possible to:
- OpenDatabase() on the OldDB.mdb.
- Loop through the Relations collection.
- CreateRelation()
- CreateField() and append each one.
- Set the Attributes, and append the Relation.

It would be much easier to:
- Create a new (blank) database:
File | New
- Import everything (tables and relations):
File | Get External | Import
- In the Import dialog, click the Options button, and choose:
Structure only.

Now you have the structure only database, you could use it as a template to
FileCopy and populate with the data instead of needing to create the tables
and their attributes and fields and their attributes and relations and their
attributes and fields each time.

 

[Guest]

Hi Allen,

Thanks for reply. Yeah, we (programmer)know how to do it manually but the
purpose of create a command button called "Create New Database" in a form so
the users (non-technical skills) just click the button and it will do all the
work! I may just hard code the relationships in VBA for now first... since I
have 7 tables, it will take a bit of code...
Best...
Violette

 

[Allen Browne]

Was there a show-stopper with the FileCopy suggestion?
It would be much easier.

 

[Guest]

Hi Allen,

I don't think so. I'm a newbie to MS Access that's why I don't know there
is other options... My assignment is to create a command button in an
existing form (which have an button to open a file, and a button to re-link
the back-end database). My button purpose is when user click it, it will
created a database, table definitions, and relationships from back-end
database and refresh the link to the new created database...
What I have done so far is check the back-end path (OldDB.mdb), import the
table data structure in the current database(MyProject), create a new
database (newDB.mdb) as the user type in the name, export those tables to
NewDB.mdb. and I refresh the link...

Anyway, if I have a template with empty tables + relationships, can you show
me how to do "FileCopy"? or do you have any example how to code it? I check
out help but don't see any example... My case is when I open my application
it linked to an external database...

Your help is highly appreciated.

Violette

 

[Allen Browne]

Call me lazy, but all I would do is create a new back end manually without
the data. (See first reply.) Call it (say) template.mdb.

Now the only line of code you have to write to generate a newDB with blank
tables is:
Call FileCopy "C:\MyPath\template.mdb", "C:\MyPath\NewDB.mdb"

 

[Guest]

Hi Allen,

Thanks, I will take a shot and see how is it work. My questions is if the
location for the template.mdb is not always the same, how do I know where
copy from?

Thanks.
Violette

 

[Allen Browne]

Perhaps you could put is in the same folder as the front end, so you would
use FileCopy on:
CurrentProject.Path & "\template.mdb"