Connection Object
Connection Object Introduction
Connection Objects are VBA's standard interface to all databases.
Dim cn as Object
Set cn = CreateObject(“ADODB.Connection”)
Because Connection Objects are standard, VBA doesn't need to know database specifics, such as what brand it is, what server it is running on, what drive it is on. But Connection Objects DO need to know these things. To tell Connection Objects about databases they connect to, we use Connection Strings. Connection Strings set Connection Object properties.
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & thisworkbook.FullName & ";" & _
"Extended Properties=Excel 8.0;"
Because each database has different properties, the Connection String to each database is different. To find the appropriate Connection String template for a specific database, we can use:
http://www.connectionstrings.com/
Once we have the Connection String template, we have to modify it with the details of our database, such as what program will translate between the database and VBA (Provider), default directory (Data Source), and other properties.
Examples:
MS Excel:
The Connection String shown above is for older versions of Excel (which is often compatible with newer versions).
MS Access:
NOTE! MS Access and MS Excel use the same “provider”. That’s not normal.
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" &
"Data Source=C:\Northwind 2003.accdb;"
MS SQL Server:
This example uses ODBC. ODBC is an older method. This illustrates that VBA is totally isolated from the specific method or database that the connection object points to. Once a connection object is created, VBA uses that connection object in exactly the same way regardless of method or database. This enables our VBA to be “ported” to different databases without changes (except to the connection string).
Cn.Open "DRIVER={SQL Server};" & _
"SERVER=" & sServer & ";" & _
"DATABASE=" & sDatabase & ";" & _
"UID=" & UID & ";" & _
"PWD=" & PWD & ";"
IBM DB2:
DB2 runs on “mainfames”. I added this to point out that Excel can talk to any database in exactly the same way. Once we know VBA and SQL, all databases are open to us. Here are two examples. The first is ODBC. The second is OLEDB.
Cn.Open "DRIVER={Client Access ODBC Driver (32-bit)};" & _
"SYSTEM=" & sServer & ";" & _
"DBQ=" & sDatabase & " *USRLIBL;" & _
"NAM=1;" & _
"CONNTYPE=0;" & _
"PROMPT=2;" & _
"TRANSLATE=1;"
Cn.Open "Provider=IBMDASQL;" & _
"Data Source=" & sServer & ";" & _
"Default Collection=" & sDatabase & " ;" & _
"Library List=*USRLIBL;" & _
"Prompt=2;" & _
"Convert Date Time To Char=FALSE;" & _
"Naming Convention=1;"