Transferdatabase

[QUINABA]

Hi All,

Is there a way to transfer a database object (table, query...) to a new database (non-existing) with a database name I can specify using macro or module? I have task that I do weekly to create a table from a master table and put it in a new database. I find it cumbersome to always have to create a blank database and then importing the new table. I hoping to automate this process.

Any help is greatly appreciated.

Thanks.

 

[QUINABA]

Thanks, I guess I can use your solution on another project. I'm looking to make it work in a macro any clues?

 

[seirra]

Is it possible to have a user specify the save location from a text box on a form. I know you can do it in Excel by specifying the Range and cell. Something like Form name and text box 1 or what ever, instead of C:\…….

 

[bat17]

If you change it form a Sub to a function the you should be able to call it from the Macro then.

Function CreateNewDB() 

Dim wrk As Workspace 
Dim dbNew As Database 
    
    Set wrk = DBEngine.Workspaces(0) 
        
    Set dbNew = wrk.CreateDatabase("C:\My Documents\NewDatabase.mdb", dbLangGeneral, dbEncrypt) 

    Set dbNew = Nothing 
End Function

How were you planning on running the Macro?

Peter

 

[QUINABA]

Thanks Bat17 and to all that pitched in! It works great. I'll be integrating the code with a macro that pulls out a weekly data by a specific region from a flat file, create the blank database then transfer the table. Then, I'll manually change the name of the database to a more descriptive name.

ray:

 

[bat17]

I missed the bit obout naming! You can change the name of the db in the code as you create it. You could either pop up an input box to ask for user input or contruct from date etc, or even combine both methods.

Function CreateNewDB()
Dim wrk As Workspace
Dim dbNew As Database
Dim strName As String
Dim strDefault As String

strDefault = "Accounts" & Format(DATE, "YYYYMMDD")
strName = InputBox("Enter name for new Database", "Database Name", strDefault)
If strName = "" Then Exit Function ' action canceled
Set wrk = DBEngine.Workspaces(0)
Set dbNew = wrk.CreateDatabase("C:\My Documents\" & strName & ".mdb", dbLangGeneral, dbEncrypt)
Set dbNew = Nothing
End Function

If you need to go further it is possible to use the common dialog controls to get a standard windows file save dialog box up.

 

[QUINABA]

Once the New database is created and have imported the table how can I rename the database in following format "NewDatabase_yyyymmdd_hhnnss")?

 

[bat17]

No need to rename it, just create it with the name that you want

Function CreateNewDB()
Dim wrk As Workspace
Dim dbNew As Database
Dim strName As String
strName = "NewDatabase_" & Format(Now, "yyyymmdd_hhnnss")
Set wrk = DBEngine.Workspaces(0)
Set dbNew = wrk.CreateDatabase("C:\My Documents\" & strName & ".mdb", dbLangGeneral, dbEncrypt)
Set dbNew = Nothing
Set wrk = Nothing
End Function

Peter

 

[QUINABA]

Thanks, I still think I need to rename, here's the sequence of what I've trying to do in the macro:

- Create table from flat file
- Create blank database
- transfer table from master database to the new blank database
- Rename the database with transferred table

If I create the blank database already formatted in "NewDatabase_yyyymmdd_hhnnss" i won't be able to accurately put the name in the Destination argument in the TransferDatabase action. Let me know if I'm wrong in my thinking or if I theres a better way.

 

[Norie]

How about the Name statement

Name oldpathname As newpathname

 

[QUINABA]

This is great!!! I got it to work.
Thanks a bunch!!! ray: