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.

 

[bat17]

If you look up CreateDatabase, and TransferDatabase in help you should find what you nedd.

Shout again if you get stuck

peter

 

[QUINABA]

I think I need CreateDatabase. I think I'm stuck, I'm no expert with VB but the syntax looks very complicated. I only want to create a blank database named "NewDatabase.mdb" in My Documents folder. Can anyone help with the syntax?

Thanks.

 

[Norie]

Try

Sub 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 Sub

Note the path might need changing, dependent on your set up.

 

[QUINABA]

Thanks for the reply! How do I run the code?

 

[Norie]

Go to the modules tab in Access and create a new module.

Paste the code in there.

Press F5.

Do you want to run it from a form?

If so in the click event of a command button put CreateNewDB.

e.g.
Sub ConmmanButton1_Click()
CreateNewDB
End Sub

 

[QUINABA]

I want to run it from a macro. Is that possible? I'm not sure what action to use, I saw RunCode but it can only use functions. I'm stuck again.

 

[Norie]

It is possible via a macro.

Check out the help for RunCode.

Though another way you could do it would be to create a button on a command bar.

I have done this previously but don't have the code to hand.

I'll try and find it and post again.

 

[QUINABA]

Thanks for reply, I'll research RunCode and hopefully figure out how to make it work. If by chance you find the code please post. I appreciate the time you've taken to help.

Thanks again and have a great weekend!

 

[Norie]

Try this:

Sub AddNewDbButton()
Dim cmdBar As CommandBar
Dim cmdCreateNewDB

    Set cmdBar = CommandBars("Menu Bar")
    
    Set cmdCreateNewDB = cmdBar.Controls.Add
    
    cmdCreateNewDB.OnAction = "CreateNewDB"
    cmdCreateNewDB.Caption = "Create New DB"
    cmdCreateNewDB.Style = msoButtonCaption
    
End Sub

Only run it once or you'll end up with loads of buttons.

BTW

You'll need:

Create a reference to the Microsoft Office 9.0 Object Library by using the References dialog box, available by clicking References on the Tools menu

Do this when you're in a module.