Creating Database Objects in 1 Database from another database

[dpimental]

I need to create forms, reports in 1 database from another database.

I also need to import tables into the same database as I am creating forms and reports in.

The idea, is that I am developing some modules in a master database, which will (I hope) be able to create a number of databases and create various objects in each of these databases without losing control of or shutting down the main database.

Any ideas out there?

David I'm Your Huckleberry!

 

[danvlas]

You could use the OpenDatabase method, then get all object names from the MSysObjects and import them:

Sub ImportAll()
Dim mydb As DAO.Database
Dim myrs As DAO.Recordset
Dim mySQLstr As String
Set mydb=DBEngine.OpenDatabase("C:\Path\File&quot

'tables
mySQLstr = "SELECT MSysObjects.Name " _
& "FROM MsysObjects " _
& &quot;WHERE (Left$([Name],1)<>'~') AND (Left$([Name],4) &quot; & &quot;<> 'Msys'&quot AND (MSysObjects.Type)=1;&quot;

Set myrs=mydb.OpenRecordset(mySQLStr)
With myrs
While Not .EOF
DoCmd.TransferDatabase acImport, , &quot;C:\Path\File.mdb&quot;, acTable, !Name, !Name, False
.MoveNext
Wend
.Close
End With
Set myrs = Nothing

'and so on with all other objects (I don't have Access where I am right now and I don't know the MSysTable types by heart.

mydb.Close
Set mydb=Nothing
End Sub


And if you use the OpenDialog to get the name of the database, it's piece of cake.

HTH,

Dan

 

[dpimental]

I actually am doing several things.

I am creating db's on the fly (this is done already).

I want to create forms and reports on the fly from one access db to another access db. In other words the master database has all the modules that do all the work. It will open a database and create forms and reports in it based on existing tables in this open database.

Prior to that I want to have imported excel spreadsheets into that database. But I want to control everything from the master database.

So, here's the scenario.
Open Master DB.
Run Code.
It creates a newdb.
Transfers excel spreadsheets into new db.
Creates forms and reports in new db.
Closes new db.
Go to the next db.

That's what I want to do.

David. I'm Your Huckleberry!

 

[danvlas]

I think you can do all these from the master db. Using
Set mydb = DBEngine.OpenDatabase
after you have created the new file will give you full control over that database. You can create objects, delete objects and do anything from the master db, just like working in the current database.

If your scenario works in the current database, I really don't see any reason for it not to work with a 'remote' mdb file. All objects, methods and properties are fully exposed and you don't even need Automation...All you need: appropriate permissions, but you're the Creator, so you have them.

Regards,

Dan