How to create a new access database and add new to table to it using vba ?
Could any one show me how to create a new access db and add a new table to it using vba?Thanks
Re: How to create a new access database and add new to table to it using vba ?
Do you mean using VB6? If you didnt have a current database then there are no modules/macros that
you can write/save to.
Re: How to create a new access database and add new to table to it using vba ?
Quote:
Originally Posted by 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.
No i mean VBA !
Re: How to create a new access database and add new to table to it using vba ?
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.
Re: How to create a new access database and add new to table to it using vba ?
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
Re: How to create a new access database and add new to table to it using vba ?
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.
VB Code:
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
Re: How to create a new access database and add new to table to it using vba ?
Quote:
Originally Posted by 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.
VB Code:
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
Re: How to create a new access database and add new to table to it using vba ?
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?
Re: How to create a new access database and add new to table to it using vba ?
Quote:
Originally Posted by 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?
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!
Re: How to create a new access database and add new to table to it using vba ?
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.
Re: How to create a new access database and add new to table to it using vba ?
Quote:
Originally Posted by 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.
VB Code:
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!!
Re: How to create a new access database and add new to table to it using vba ?
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.
Re: How to create a new access database and add new to table to it using vba ?
Quote:
Originally Posted by 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.
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!!
Re: How to create a new access database and add new to table to it using vba ?
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.
Re: How to create a new access database and add new to table to it using vba ?
Quote:
Originally Posted by 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.
i get this error:
compile eror:
User- defined type not defined
and it points to : Dim oDB As DAO.Database .
Re: How to create a new access database and add new to table to it using vba ?
In the VBA IDE go to Tools > References and select "MS DAO 3.x Object Library"
