Solved: Create new blank database

[ViolettePhan]

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.

Violette

[stanl]

I generally use ADOX to create a new Database, then Open an ADO Connection Object and issue SQL such as 'SELECT * INTO [target] FROM [source] WHERE 0=1' to just copy the structure.
Stan

[stanl]

I'm sorry... I meant to include an idea of what I meant.



'assume database exists
MDB = "c:\temp\accts.mdb"
'and you want to create
MDB1 = "c:\temp\acctsnew.mdb
'and you want to transfer the structure of a table named accts

cConn="Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=" & MDB1 & ";"
oCat = CreateObject("ADOX.Catalog")
oCat.Create cConn 'mdb cannot already exists or error
oCat = Nothing

cConn="Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=" & MDB & ";"
oConn = CreateObject("ADODB.Connection")
oConn.Open cConn
cSQL = 'SELECT * INTO [accts] IN "" [' & MDB1 & "] FROM [accts] WHERE 0=1"
oConn.Execute cSQL
oConn.Close
oConn=Nothing


Stan

[ViolettePhan]

Hi Stanl,
Thanks for your reply. I haven't try your code yet cuz when I read your code it seems to me that you connect to other database to create a new one. What I want is I want to create a new db with all the empty tables and no linked.
Here is my situation & code right now:

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

[stanl]

Hi Stanl,
Thanks for your reply. I haven't try your code yet cuz when I read your code it seems to me that you connect to other database to create a new one.

No, that is not it. You may not have much experience with ADO. Personally, I haven't used DAO or Access Basic since 1997 so I would not be much help with TransferDatabase etc...

The code I posted is meant to be run independent, usually as a .wsc or vbscript file, though it could be executed as an Excel macro and still work with the Access file(s).

Sorry I could not be more helpful. Stan

[ViolettePhan]

Hi stanl,
Thanks for your help. I was able to figure out how to it.

Thanks,
Violette

[stanl]

Hi stanl,
Thanks for your help. I was able to figure out how to it.
Violette

I guess you can mark this as solved, or the sysop will:dunno