 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / August 2006create new blank database by VBA code
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. > Hi all, > [quoted text clipped - 6 lines] > (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 > Hi all, > [quoted text clipped - 6 lines] > (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 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 > > Hi all, > > [quoted text clipped - 45 lines] > > John 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, > Hi Parkjv1, > Thank you for you reply. I got your code + my code (as below) and now my [quoted text clipped - 86 lines] > > > > John 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 > Hi, > [quoted text clipped - 98 lines] > > > > > > John |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.