Creating access database

[chandrashekar]

Hi,

How to create a access database using vba code?

Where we should take input from the user.

Thanks in advance.

Regards,
Chandra Shekar B

 

[Trevor G]

Here is some code to create a database. If running from Excel you will need to add the reference to the Microsoft ActiveX Data objects 2. (what ever is the highest version) library

Private Sub CreateDatabase()
 
'Macro Purpose: Create an Access database on the fly
Dim dbConnectStr As String
Dim Catalog As Object
Dim cnt As ADODB.Connection
Dim dbPath As String
'Set database name here
dbPath = "M:\Access Files\Test ME Today.mdb"
dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"
'Create new database
Set Catalog = CreateObject("ADOX.Catalog")
Catalog.Create dbConnectStr
Set Catalog = Nothing
'Connect to database and insert a new table
Set cnt = New ADODB.Connection
With cnt
.Open dbConnectStr
.Execute "CREATE TABLE tblSample ([Name] text(50) WITH Compression, " & _
"[Address] text(150) WITH Compression, " & _
"[City] text(50) WITH Compression, " & _
"[ProvinceState] text(2) WITH Compression, " & _
"[Postal] text(6) WITH Compression, " & _
"[Account] decimal(6))"
End With
Set cnt = Nothing
End Sub

 

[chandrashekar]

Hi,

Thanks for ur reply. Code is working fine.