How to create a new access database and add new to table to it using vba ?

[tony007]

Could any one show me how to create a new access db and add a new table to it using vba?Thanks

[RobDog888]

Do you mean using VB6? If you didnt have a current database then there are no modules/macros that
you can write/save to.

[tony007]

Do you mean using VB6? If you didnt have a current database then there are no modules/macros that
you can write/save to.

No i mean VBA !

[RobDog888]

If yo open up Access and press Alt+F11 you will see what I mean. Its blank because you dont have a
current db. So what I was trying to find out was how and where you wanted to do this.

Do you need to do this from another Access db, a different Office Suite program, etc.

[tony007]

Well here is what i want to do. I create a form in access database and i put a button there. Once i click on the button it will go call the cratetable funciton and should create a new db in c: and also make a new table with valu that i put in the vba code.
Private sub createtable()

.
.
.

end sub

[RobDog888]

Ah, ok. So you just need a button on your access form to create a new blank db and a single new table
composed of some selected criteria.

You can use this to create a new db and table.

Private Sub CreateATable()
 
    Dim oApp As Access.Application
    Dim oDB As DAO.Database
    Dim oTD As DAO.TableDef
 
    Set oApp = New Access.Application
    oApp.NewCurrentDatabase "C:\Test.mdb"
    Set oDB = oApp.CurrentDb
    Set oTD = oDB.CreateTableDef("MyTable")
    With oTD
        .Fields.Append .CreateField("FirstName", dbText)
        .Fields.Append .CreateField("LastName", dbText)
        .Fields.Append .CreateField("Phone", dbText)
        .Fields.Append .CreateField("Notes", dbMemo)
    End With
    oDB.TableDefs.Append oTD
    oDB.Close
    Set oTD = Nothing
    Set oDB = Nothing
    oApp.Quit acQuitSaveAll
    Set oApp = Nothing
    
End Sub

[tony007]

Ah, ok. So you just need a button on your access form to create a new blank db and a single new table
composed of some selected criteria.

You can use this to create a new db and table.

Private Sub CreateATable()
 
    Dim oApp As Access.Application
    Dim oDB As DAO.Database
    Dim oTD As DAO.TableDef
 
    Set oApp = New Access.Application
    oApp.NewCurrentDatabase "C:\Test.mdb"
    Set oDB = oApp.CurrentDb
    Set oTD = oDB.CreateTableDef("MyTable")
    With oTD
        .Fields.Append .CreateField("FirstName", dbText)
        .Fields.Append .CreateField("LastName", dbText)
        .Fields.Append .CreateField("Phone", dbText)
        .Fields.Append .CreateField("Notes", dbMemo)
    End With
    oDB.TableDefs.Append oTD
    oDB.Close
    Set oTD = Nothing
    Set oDB = Nothing
    oApp.Quit acQuitSaveAll
    Set oApp = Nothing
    
End Sub

Many Many thanks to u . Well if i want to insert some data in to the created tables how i can do that? i mean insert statments inside the sub part .THanks

[RobDog888]

You would probably want to use an INSERT SQL Statement executed from a connection/command object.
Is this a direct copy of an existing table or only certain records?

[tony007]

You would probably want to use an INSERT SQL Statement executed from a connection/command object.
Is this a direct copy of an existing table or only certain records?

well evantually i want to insert system cataloge popluation to that new db that i created. SO i want to learn how to reach that db and do some insert statments against it!

[RobDog888]

Oh. I'm not 100% sure you can add anything to a system table.

You may be better off creating a copy of the db and parsing out what you dont want/need.

[tony007]

Ah, ok. So you just need a button on your access form to create a new blank db and a single new table
composed of some selected criteria.

You can use this to create a new db and table.

Private Sub CreateATable()
 
    Dim oApp As Access.Application
    Dim oDB As DAO.Database
    Dim oTD As DAO.TableDef
 
    Set oApp = New Access.Application
    oApp.NewCurrentDatabase "C:\Test.mdb"
    Set oDB = oApp.CurrentDb
    Set oTD = oDB.CreateTableDef("MyTable")
    With oTD
        .Fields.Append .CreateField("FirstName", dbText)
        .Fields.Append .CreateField("LastName", dbText)
        .Fields.Append .CreateField("Phone", dbText)
        .Fields.Append .CreateField("Notes", dbMemo)
    End With
    oDB.TableDefs.Append oTD
    oDB.Close
    Set oTD = Nothing
    Set oDB = Nothing
    oApp.Quit acQuitSaveAll
    Set oApp = Nothing
    
End Sub

well i tried to run this code but no database was created!!

[RobDog888]

Did you run it from within a module in access? Did you step through the code to see if there were any errors?
The db will output to C:\ as Test.mdb.

[tony007]

Did you run it from within a module in access? Did you step through the code to see if there were any errors?
The db will output to C:\ as Test.mdb.

well i made a button on a form and it call this from with it private sub . No errror but i did a serach on my pc i could not find that db!!

[RobDog888]

Place a breakpoint on the beginning of the sub and step through the code to see it it errors or what the values
are along the way.

[tony007]

Place a breakpoint on the beginning of the sub and step through the code to see it it errors or what the values
are along the way.

i get this error:

compile eror:

User- defined type not defined

and it points to : Dim oDB As DAO.Database .

[RobDog888]

In the VBA IDE go to Tools > References and select "MS DAO 3.x Object Library"