create new blank database by VBA code

[Guest]

Hi all,

Is it possible to create a new blank database with all table structures and
relationships by VBA code? My new task is to create a button "create new
database" , when the user click that button, user will prompt to open
directory, type the file name with defaut .mdb, when click ok from dialog
file and new database was create + all tables structures + table
relationships (no data), then refresh the current link database to the new
(just created) database.
Thanks in advance for your help.

 

[parkjv1]

Hi all,

Is it possible to create a new blank database with all table structures and
relationships by VBA code? My new task is to create a button "create new
database" , when the user click that button, user will prompt to open
directory, type the file name with defaut .mdb, when click ok from dialog
file and new database was create + all tables structures + table
relationships (no data), then refresh the current link database to the new
(just created) database.
Thanks in advance for your help.

You can try the following code; it's taken directly from the help files
from Access 2000

Sub CreateDatabaseX()

Dim wrkDefault As Workspace
Dim dbsNew As DATABASE
Dim prpLoop As Property

' Get default Workspace.
Set wrkDefault = DBEngine.Workspaces(0)

' Make sure there isn't already a file with the name of
' the new database.
If Dir("NewDB.mdb") <> "" Then Kill "NewDB.mdb"

' Create a new encrypted database with the specified
' collating order.
Set dbsNew = wrkDefault.CreateDatabase("NewDB.mdb", _
dbLangGeneral, dbEncrypt)

With dbsNew
Debug.Print "Properties of " & .Name
' Enumerate the Properties collection of the new
' Database object.
For Each prpLoop In .Properties
If prpLoop <> "" Then Debug.Print " " & _
prpLoop.Name & " = " & prpLoop
Next prpLoop
End With

dbsNew.Close

End Sub


HTH,

John

 

[parkjv1]

Hi all,

Is it possible to create a new blank database with all table structures and
relationships by VBA code? My new task is to create a button "create new
database" , when the user click that button, user will prompt to open
directory, type the file name with defaut .mdb, when click ok from dialog
file and new database was create + all tables structures + table
relationships (no data), then refresh the current link database to the new
(just created) database.
Thanks in advance for your help.

You can try the following code; it's taken directly from the help files
from Access 2000

Sub CreateDatabaseX()

Dim wrkDefault As Workspace
Dim dbsNew As DATABASE
Dim prpLoop As Property

' Get default Workspace.
Set wrkDefault = DBEngine.Workspaces(0)

' Make sure there isn't already a file with the name of
' the new database.
If Dir("NewDB.mdb") <> "" Then Kill "NewDB.mdb"

' Create a new encrypted database with the specified
' collating order.
Set dbsNew = wrkDefault.CreateDatabase("NewDB.mdb", _
dbLangGeneral, dbEncrypt)

With dbsNew
Debug.Print "Properties of " & .Name
' Enumerate the Properties collection of the new
' Database object.
For Each prpLoop In .Properties
If prpLoop <> "" Then Debug.Print " " & _
prpLoop.Name & " = " & prpLoop
Next prpLoop
End With

dbsNew.Close

End Sub


HTH,

John

 

[Guest]

Hi Parkjv1,
Thank you for you reply. I got your code + my code (as below) and now my
situation & code right now are:

My application name "MyProject" when it open is linked to an external
database name "oldDB.mdb". There's one command button in switchboard called
"Create new database". and that button has the following code:

Dim ws As Workspace
Dim dbNew As dao.Database
Dim NewFilename As String

'Get default Workspace
Set ws = DBEngine.Workspaces(0)

'Path and file name for new mdb file
NewFilename = "c:\Test\NewDB.mdb"
'Make sure there isn't already a file with the name of the new database
If Dir(NewFilename) <> "" Then Kill LFilename
'Create a new mdb file
Set dbNew = ws.CreateDatabase(NewFilename, dbLangGeneral)

'For data entry tables, export only table definition to new mdb file
DoCmd.TransferDatabase acExport, "Microsoft Access", NewFilename, acTable,
"MyTestTable", "MyTestTable", structureonly:=True
dbNew.Close
Set dbNew = Nothing

It created a "NewDB.mdb" with a table "MyTestTable" (which is what I wanted)
perfectly. But when I open "NewDB.mdb", "MyTestTable" is link to "oldDB.mdb".
I don't want that "NewDB.mdb" link to any where else (when I open it by
itself) and I also want "MyProject" (which I still open after created a new
database) to relink to "NewDB.mdb" instead of "oldDB.mdb". I called the
relink function (after set dbNew =nothing ) that I have to link at the
beginning but somehow i got a message said that it can't find "MyTestTable"...
Do you know how to drop the link for the new created database and relink
that new created database to my current open application?

Thanks for your reply
Violette

 

[parkjv1]

Hi,

I apologize for not answering in quite some time....

I have been trying to get some serious projects done around the house
prior to this winter. I have an old farm house built in 1916 with
original windows.....very time consuming.....I'm currently at work and
I'm heading home....I will read this more tomorrow and then get back to
you...that is if you haven't found a solution yet...

Tks,

 

[Guest]

Hi John,
Thanks, don't bother cuz I finished that project. Now I move on to other
projects so I may have some more questions later on. But again, thanks for
reply. Wish you doing well...

Appreciated it.
VP