Microsoft Office Tutorials and References
In Depth Information
‘Set the assignment to open the connection.
Set dbConnection = New ADODB.Connection
‘Define the Provider and open the connection.
.Provider = “Microsoft.ACE.OLEDB.12.0;Data Source=” & dbFileName & _
“;Persist Security Info=False;”
‘Set the Command variables.
Set dbCommand = New ADODB.Command
Set dbCommand.ActiveConnection = dbConnection
‘Create the table, which will be named tblEmployees.
dbCommand.CommandText = _
“CREATE TABLE tblEmployees (EmployeeID Char(10) “ & _
“Primary Key, LastName text, FirstName text)”
‘Execute the command to create the table.
dbCommand.Execute , , adCmdText
‘Release Object variable memory.
Set dbCommand = Nothing
Set dbConnection = Nothing
The text reference following the field names in the CommandText is to advise
Access that the field’s data type will be text. As you may know with Access
tables, other field types are Memo, Number, Date/Time, Currency, Yes/No,
OLE Object, Hyperlink, and Attachment.
In this lesson, you write a macro that adds a new field to an existing table in an Access database.
For this lesson, you maintain an Access database named Database1.accdb . In that database is a
table named tblEmployees. You discover that a new field is required to be added to that table, which
will record the middle names of employees. The data type of the new field will be Text. To get the