Creating a New Microsoft Access Database

If you want to programmatically transfer Excel data into a new Access database, you may need to create a database from scratch by using VBA code. The following example procedure demonstrates how to use the Data Access Objects (DAO) to establish a connection with Microsoft Access. The CreateDatabase method of the Workspace object is used to create a new database named ExcelDump.mdb in the root folder of the C drive. The CreateTableDef method of the Database object is then used to create a table named tblStates. Before a table can be added to a database, the fields must be created and appended to the table. The procedure creates three text fields (dbText) that can store 2, 25, and 25 characters each. As each field is created, it is appended to the Fields collection of the TableDef object using the Append method. Once the fields are created and appended to the table, the table itself is added to the database with the Append method. Because the database file named "C:\ExcelDump.mdb" may already exist in the specified location, the procedure includes the error-handling routine that will delete the file so that the database creation process can go on. Because other errors could occur, the Else clause includes a statement that will display the error and its description and allow an exit from the procedure.

Sub NewDB_DAO() Dim db As DAO.Database Dim tbl As DAO.TableDef Dim strDb As String Dim strTbl As String

S (bISlaies : lable

H0H

Field Name

Data Type 1

Description I * I

II h^hr -aMMUiUUI

Text

IStateNaina

Text

_1

■btatsCanital

Text

■ _ 1

▼ 1

Field Properties

General | Lookup |

Field Size

2

Format

Input Mask

Caption

Default Value

Validation Rute

Validation Text

Required

No

Allow Zero Length

No

Indexed

No

Unicode Compression

No

IME Mode

No Control None

IME Sentence Mode

On Error GoTo Error_CreateDb_DAO strDb = "C:\ExcelDump.mdb" strTbl = "tblStates" ' Create a new database named ExcelDump Set db = CreateDatabase(strDb, dbLangGeneral)

Figure 15-7: This Microsoft Access database table was created by an Excel VBA procedure.

' Create a new table named tblStates Set tbl = db.CreateTableDef(strTbl)

' Create fields and append them to the Fields collection With tbl

.Fields.Append .CreateField("StateId", dbText, 2) .Fields.Append .CreateField("StateName", dbText, 25) .Fields.Append .CreateField("StateCapital", dbText, 25) End With

' Append the table object to the TableDefs db.TableDefs.Append tbl

' Close the database db.Close

Set db = Nothing

MsgBox "There is a new database on your hard disk. " & vbCrLf _

& "This database file contains a table " & strDb & vbCrLf _ & "named " & strTbl & "." & vbCrLf _ & "Before you activate this database, close the Excel _ application."

Exit_CreateDb_DAO:

Exit Sub Error_CreateDb_DAO:

If Err.Number = 3204 Then

' Delete the database file if it already exists

Kill "C:\Exceldump.mdb"

Resume

Else

MsgBox Err.Number & ": " & Err.Description Resume Exit_CreateDb_DAO End If End Sub

0 0

Post a comment