Creating Tables and Columns at Run Time

The following code is an example of creating a Table and Columns using ActiveX Data Objects (ADO):

Sub CreateEmployee()

Dim cn As ADODB.Connection
Dim strSQL As String
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=c:\test.mdb;"
cn.Open
strSQL = "CREATE TABLE `Employee` (`Last Name` varchar(30), `First Name` varchar(30), `Age` integer)"
cn.Execute strSQL
cn.Close

End Sub

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.

Three Main Objects of ADO

Connection Object

This object is the one used to connect to database, which is then used to execute commands against the database or retrieve a Recordset. The object has the ConnectionString property that is used to specify the database you want to connect to. The Open method establishes the database connection. The Close method releases the connection and the memory used by the object.

The Connection String can be generated at run time using the DataLinks. Please see the topic Building Database Connection String Programmatically.


Connection String Attributes:

Provider – The provider name or driver name that ADO will use to access the database.

User ID – When needed, it is used by the provider to establish the proper rights for accessing the database.

Password – To validate the user, the user’s password is needed by the provider.

Integrated Security – Setting this value to SSPI, the driver will use the Windows NT Integrated
Security. User ID and Password are not used when this is done.

Data Source – If you are connecting to an Access database, this should be set to the file and complete path of the file. This should have the machine name when connecting SQL Server.

Initial Catalog – This attribute should have the database name when connecting to SQL Server.

Recordset Object

This object is important because it is used to manipulate databases. It is a memory allocation in a client computer or server that has a set of rows of one table or more of the database. With Recordset, you can add, edit, delete and update records of the actual table in the database.


Command Object

This object has the information about the command to be executed. The command object could have a Query (SQL) command, Table, View or Stored Procedure.

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.

Microsoft ActiveX Data Objects (ADO)

Microsoft ActiveX Data Object (ADO) is a Windows 2000 (or later) component for accessing databases. It doesn’t need to be distributed because it belongs to its core services.
ADO has the object name “Microsoft ActiveX Data Objects 2.x Library”. The “x” refers to the version number and it varies on every machine. Loading this, please see Referencing Object discussion.

The first step to making use of the ADO is connecting to the database. You need to have the correct “Connection String” prior to connecting to the database. If you don’t know exactly what Connection String you have to use, you can use the Connection String builder I discussed in Building Database Connection String Programmatically. Once you have your Connection String, you can store it in a variable or you can save it in worksheet cell for future use with your program.

Below is a sample code on how to make connection to the database:

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\test.mdb;"

If your Connection String is in a cell in a worksheet, for example, it is in Cell A1 of Sheet1, the Connection String in the code is can be replaced with the following:

cn.Open Sheets(“Sheet1”).Cell(1,1)

or,

cn.Open Sheets(“Sheet1”).Range(“A1”)

Once connected to the database, you can now do the data manipulation. You can create your own table and columns. You can add, edit or delete a record to the database.

In my future posting, I will include the following topics to help you in learning ADO in details:
3 Main Objects of ADO

• What is a Recordset?
• What is a Cursor?
• Executing a Command with ADO
• Retrieving and Manipulating Data with ADO
• ADO – Open Method
• ADO – Close Method
• Updating Database using ADO

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.

Building Database Connection String Programmatically

Situation:
In a few days from now, I will be doing a new Excel VBA project for a more-than-2-year-old client. The new project will require me to produce pivot-table reports from an external data source. This kind of requirement is usually easy because Excel PivotTable already supports external source of data. But the problem with this project, they are requiring me to come up with an Excel VBA program that be used to connect with different database platform. As I was thinking about the solution of this problem, I’ve posted to different Excel VBA forum to seek for help. No one has given me the complete answer but while I was reviewing their suggestions, I tried to put them together and came up with the correct solution. Truly forums are very helpful. Below is the sample source code of the database connection string builder that can provide different OLEDB connectivity programmatically:


Solution:

'PROCEDURE TO GET CONNECTION STRING USING DataLinks 
Function GetConnectionString() As String
   Dim objLink As New MSDASC.DataLinks
   Dim strConnectionString As String
   strConnectionString = ""
   On Error GoTo LinkErr
   strConnectionString = objLink.PromptNew
LinkErr:
   GetConnectionString = strConnectionString
End Function

'PROCEDURE TO TEST THE FUNCTION ABOVE
Sub Test() 
   Dim strCN As String
   strCN = GetConnectionString
   If strCN <> "" Then 
      MsgBox strCN
   End If
End Sub

The heart of the code is the GetConnectionString procedure. It uses the MSDASC.DataLinks object. But before you can use this code you need to load 2 object files from Microsoft. They are:

1.) Microsoft ActiveX Data Objects 2.5 Library (or later version)
2.) Microsoft OLE DB Service Component 1.0 Type Library

See: Referencing Objects

When MSDASC.DataLinks generates a connection string, it stores the value in strConnectionString variable and eventually, it is the value to be returned by the GetConnectionString function.

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.