ADO: how to connect to a SQL, Access or Sybase database & how to manipulate objects
DISCLAIMER: this doesn’t pretend to be a complete “how-to” on ADO; it’s meant to give a few pointers in the right direction.
It’s also not the only way to do things, perhaps not even the best way but it’s worked for me in the past ;-)
Finally: is it better than DAO? Dunno. I’m still using both methods, and depending on my/my users’ needs, I choose either one or the other.
Hopefully the examples are of some use to some of you …
Before you start: make sure you add a reference to ADO and ADO Extensions to your project! This adds the ADODB and ADOX object models which you need to manipulate your database ;-)
ADO Connections
With ADO you use a Connection object to gain access to your databases using so-called OLE DB providers. There are 2 types of Providers:
1. Data providers allow you to link up to any database created in the application that the data provider was written for – i.e. they’re written to “talk” to Access, SQL Server, Oracle, Sybase, etc. The functionality of each provider depends mostly on the RMDBS. What works with one mightn’t work with another
This is a list based on the Providers that MS supplies in MDAC 2.6 (as nicked from Microsoft):
Code:
Area Topic
ODBC databases Microsoft OLE DB Provider for ODBC
Microsoft Indexing Service Microsoft OLE DB Provider for Microsoft Indexing Service
Microsoft Active Directory Service Microsoft OLE DB Provider for Microsoft Active Directory Service
Microsoft Jet databases OLE DB Provider for Microsoft Jet
Microsoft SQL Server Microsoft OLE DB Provider for SQL Server
Oracle databases Microsoft OLE DB Provider for Oracle
Internet Publishing Microsoft OLE DB Provider for Internet Publishing
Simple data sources Microsoft OLE DB Simple Provider
2. Service providers add extra functionality to the standard functionality of your data provider – this is MS-ese to say “whatever your RMDBS can’t do we’ve done for you”. I haven’t used these very much, so can’t say much about them
A connection to a database is made by creating a new ADODB.Connection object in your code (sounds harder than it is:
Code:
Dim l_cnConnection As New ADODB.Connection
will do this for you), and then setting the connection string. The connection string is different for each Provider you want to access. Below you’ll find some examples (all examples assume a Connection Object which I’ve defined on a modular level – i.e. this Connection object can be used throughout all Subs and Functions that are added to this module. As you can’t use NEW on a modular level, so the declaration’s a bit different:
Code:
Private m_cnConnection As ADODB.Connection
.):
Connection to an Access database using the Microsoft.Jet.Ole.DB.4.0 provider (similar functionality to the DAO Jet workspace)
Code:
l_sConnect = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\My Documents\MyAccessDB.mdb;" & _
"User ID=admin;" & _
";Password="
'Set connection
Set m_cnConnection = New ADODB.Connection
With m_cnConnection
.ConnectionString = l_sConnect
.Open
.CursorLocation = adUseClient
End With
SQL Server connection string:
Code:
'Goes straight to SQL Server; use Servername / DBname / Valid User ID / valid Password combination to connect
l_sConnect = "PROVIDER=SQLOLEDB;" & _
"Server=srv;” & _
"Database=pubs;" & _
"UID=sa;" & _
";PWD=pwd;"
'Set connection
Set m_cnConnection = New ADODB.Connection
With m_cnConnection
.ConnectionString = l_sConnect
.Open
.CursorLocation = adUseClient
End With
Sybase connection string – I tend to use DSNs to connect to our Sybase DB – makes for easier code maintenance. All I need to do to adapt our Excel tools to a new environment = set another DSN
Code:
:
l_sConnect = "PROVIDER=MSDASQL;" & _
"DSN=UATEnv" & p_sDSN & ";" & _
"UID=test;" & _
";PWD=testing;IS=SET ANSINULL OFF;"
'Set connection
Set m_cnConnection = New ADODB.Connection
With m_cnConnection
.ConnectionString = l_sConnect
.Open
.CursorLocation = adUseClient
End With
We added the
after a mishap where one developer used
and another stuck to
. Caused us a few headaches to find out why Nulls weren’t being returned properly!
For other providers please check this link (
very useful list of all connection strings you could possibly need):
ADO recordsets.
Once the connection has been established, there are 2 ways of querying data in your database: by using stored procedures (queries in Access language) or by having your code pass the db a bit of SQL. Code example of both (the examples assume a recordset which has been declared on the modular level (
Code:
Public m_rsRecordset as ADODB.Recordset
):
Executing a stored procedure/query
Code:
Public Function ExecStoredProcedure(p_sProcedureName As String, p_sParameterString As String) As Boolean
Dim l_cmdTmp As New ADODB.Command
Dim l_sCMDString As String
Dim l_rsTmp As New ADODB.Recordset
ExecStoredProcedure= False
On Error GoTo ErrorHandler
'Clear recordset so we pick up only latest stored proc result
Set m_rsRecordset = Nothing
l_sCMDString = "exec " & p_sProcedureName & " " & p_sParameterString
With l_cmdTmp
.ActiveConnection = m_cnConnection
.CommandText = l_sCMDString
.CommandType = adCmdText
.CommandTimeout = 0
Set l_rsTmp = .Execute
End With
'Set recordset to modular variable
Set m_rsRecordset = l_rsTmp
ExecStoredProcedure= True
Exit Function
ErrorHandler:
MsgBox Err.Description & “ (“ & Err.Number& “)”
Exit Function
Resume
End Function
Call this function as follows:
If Not ExecStoredProcedure("qryLargeCreditClients", ""
Then Err.Raise 99, , "ERROR: could not run query qryLargeCreditClients"
Use the parameter string to add any parameters your stored procedure might have. The example query qryLargeCreditClients has no parameters, so an empty string is passed.
Executing a bit of SQL
Code:
Public Function RunSQL(p_sSQL As String) As Boolean
Dim l_cmdTmp As New ADODB.Command
Dim l_rsTmp As New ADODB.Recordset
On Error GoTo ErrorHandler
'Clear recordset so we pick up only latest stored proc result
Set m_rsRecordset = Nothing
'Run SQL
With l_cmdTmp
.ActiveConnection = m_cnConnection
.CommandText = p_sSQL
.CommandType = adCmdText
.CommandTimeout = 0
Set l_rsTmp = .Execute
End With
'Set recordset to modular variable
Set m_rsRecordset = l_rsTmp
Exit Function
ErrorHandler:
MsgBox Err.Description & “ (“ & Err.Number& “)”
Exit Function
Resume
End Function
Call this function as follows:
If Not RunSQL("SELECT * From tblClientOne WHERE ClientCredit > 50"
Then Err.Raise 99, , "ERROR: could not run SQL"
Table and Index objects:
Finally, here’s an exmaple that’ll add a new table + Primary Key + Index to your database:
Code:
Dim l_tblTable As New ADOX.Table
Dim l_catCatalog As New ADOX.Catalog
Dim l_idxIndexPrimary As New ADOX.Index
Dim l_idxIndex As New ADOX.Index
On Error GoTo ErrorHandler
'Set catalog - this object = the container for tables, queries, indices amd other database objects
l_catCatalog.ActiveConnection = m_cnConnection
With l_tblTable
'Name the table
.Name = "tblClient"
'Add fields
.Columns.Append "ClientID", adInteger
.Columns.Append "ClientName", adVarWChar, 30
.Columns.Append "ClientAddress", adVarWChar, 40
.Columns.Append "ClientTown", adVarWChar, 20
.Columns.Append "ClientCredit", adCurrency
.Columns.Append "ClientAddDate", adDate
'Add index - e.g. primary key
l_idxIndexPrimary.Name = "xPrimaryKey"
l_idxIndexPrimary.Columns.Append "ClientID"
l_idxIndexPrimary.PrimaryKey = True
l_idxIndexPrimary.Unique = True
.Indexes.Append l_idxIndexPrimary
'Add ordinary 1-field index
.Indexes.Append "xName", "ClientName"
End With
'Add the new table to the catalog by using the APPEND method
l_catCatalog.Tables.Append l_tblTable
I’ll follow-up with an example of using DAO to access your database – later, dude ;-)
Meanwhile, hope this helps as a first step
Cheers
Nikki
![[swords]](/data/assets/smilies/swords.gif "[swords] [swords]")
needed some pieces of this code 