Microsoft Office Tutorials and References
In Depth Information
Step-by-Step
step-by-step
1.
In your Excel workbook, press Alt+F11 to go to the Visual Basic Editor.
2.
From the VBE menu, click Insert Module.
3.
In the new module, type the name of your macro, which will be AddNewField . VBA will
automatically place a pair of parentheses after the macro name, followed by an empty line,
followed by the End Sub statement. Your code will look as follows:
Sub AddNewField()
End Sub
4.
Similar to what you have seen in this lesson’s macros, declare three variables: one for the
ADO connection, one for the ADO command, and one for the full path and name of the
Access database you are working with:
Dim dbConnection As ADODB.Connection
Dim dbCommand As ADODB.Command
Dim dbFileName As String
5.
Define the Access database path and name:
dbFileName = “C:\YourFilePath\Database1.accdb”
6.
Set the assignment to open the connection:
Set dbConnection = New ADODB.Connection
7.
Define the Provider and open the connection:
With dbConnection
.Provider = “Microsoft.ACE.OLEDB.12.0;Data Source=” & dbFileName & _
“;Persist Security Info=False;”
.Open dbFileName
End With
8.
Set the Command variables:
Set dbCommand = New ADODB.Command
Set dbCommand.ActiveConnection = dbConnection
9.
Establish the command that adds a field for a middle name:
dbCommand.CommandText = _
“ALTER TABLE tblEmployees Add Column MiddleName text)”
10.
Execute the command to create the new field:
dbCommand.Execute , , adCmdText
11.
Release Object variable memory:
Set dbCommand = Nothing
Set dbConnection = Nothing
Search JabSto ::




Custom Search