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