Creating access database

chandrashekar

Active Member
Joined
Jul 15, 2005
Messages
458
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,665
Office Version
2016
Platform
Windows
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

Code:
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
 
Last edited by a moderator:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,877
Messages
5,513,936
Members
408,978
Latest member
Ken0356

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top