Create a new database with only certain fields from a table

[cblock1025]

Hey guys!
Just wanted to know if it is possible to automate with vb code a creation of a new database and just inserting certain fields into it. Or, maybe just import certain fields to another database already created. I know it sounds stupid but, I have my reasons. So, I guess using a module and putting a button in my form this will automatically create a new database with a table a certain fields of my database. The only thing I know possible is exporting tables from one database to another. Is this possible?

Christian

 

[cblock1025]

maybe just import certain fields to another database already created <-- Sorry about this. It is not import, but export. =)

 

[Edski]

Christian,
One way is to create a SQL type query and then just execute it in vba. Eg, for an existing database called db1.mdb:

strSQL = "INSERT INTO myTable IN db1.mdb " & _
SELECT myFields " & _
FROM myTable"
dbs.Execute strSQL

To create a new database, try:

Sub creatDB(strDB as String)
  ' Make sure there isn't already a file with this name
  If Dir(strDB) <> "" Then Kill strDB
  DBEngine.Workspaces(0).CreateDatabase(strDB, dbLangGeneral)
End Sub

Good luck.

 

[cblock1025]

Thanks for the fast reply. I will try to put it together and see if I can work something out. I will post as soon as I get something working. If anybody has any other ideas, feel free to post. Thanks.

Christian

 

[cblock1025]

Ok I was able to transfer the fields I wanted to an existing database. However, the code to create a database does not work. I am going to try to change it around and see what happens.

 

[cblock1025]

Ok, found this, but everytime I execute it I get an error on line 2.

Function CreateNewDatabase ()
Dim DefaultWorkspace As WorkSpace
Dim CurrentDatabase As Database, MyDatabase As Database

Set DefaultWorkspace = DBEngine.Workspaces(0)

'Create new database specifying the correct collating order.
Set MyDatabase = DefaultWorkspace.CreateDatabase("MyNewDb.mdb", DB_LANG_GENERAL)
MyDatabase.Close
End Function

 

[cblock1025]

Nevermind I got it. Just had to add in a library reference.

 

[Edski]

cblock,
I forgot to tell you about the Reference. Glad it worked for you.
Edski