Learn ODBC : Getting Connected

In this set of articles we will learn how to write code that will allow you to connect to tables within a database. We will show you how to connect to Data collection tables as well as normal SQL tables and show you the four things that you will need to do with them, find, Delete , update and export the records. This first article will teach you how to connect to a Data Base using the ADO object.

Before we get started there are a two things we should know. Firstly, what is the difference between a Database and a Table? Well if you imagine the database as a house and the table one of the items within that house then you should understand the difference. And Secondly what is ADO. ADO or ActiveX Data Objects is the component supplied by Microsoft to allow you to talk to databases. This object has been around for a long

time and has been superseded by more complex but better objects but for us and IBM SPSS Data Collection ADO is more than enough for our needs. So what do we have to do , well we basically have to follow these 7 steps.

1.Create an ADO connection to a database
2.Open the database connection
3.Create an ADO recordset
4.Create the SQL to “Delete”, “Update”, “Insert” or “Find” the records
5.Open the recordset and do what you need to do
6.Close the recordset
7.Close the connection

Ok , so all that said how are we going to run these commands? Well in these examples we are going to put the code in a survey. You might think why, but as you will see from these articles there are times when  connecting to external databases is useful. So the first thing we need is an MDD, In this example this is the metadata that we are going to use, but of course you would have all your other questions in the survey also.

Metadata(en-AU, Question, label)
ConnectionInfo "{Info}" info;
End Metadata

And next we need the routing , in this article we are just going to look at connecting to the database so that we can start to do things with the tables. So what’s first well the first thing we need to do is to declare some variables. ( we did this in the web routing )

Dim sProject, sServer, sConnection , sPassword, sUser
Dim oConnection

So what do we have , well we have a variable for the name of the Data Collection project we are going to look  at ( sProject ) followed by the name of the SQL server that holds the information ( sServer ). Next comes a variable to hold what is called the connection string ( sConnection ). The connection string is what is used to tell the program how to connect to the SQL server. We then have a variable for the username and password
that will be used in the connection string ( sPassword , sUser ). and finally we have a variable to store the connection object itself  ( oConnection ). Next type the following,

sProject = "ADO"
sServer = "MyServer"
sUser = "admin"
sPassword = "abc"
Set oConnection = CreateObject("ADODB.Connection")

In this example we are going to connect to a Data Collection survey called “ADO” that is sitting on a server called “MyServer” with a username and password of “admin” and “abc”. You of course will have to change these settings for your server. Next we are going to create the connection object. This is done by using the createobject command and the “ADOB.Connection” dll. Once we have all this working the next thing that need to  be done is to create the connection string. This can be done by the connection string wizard, but in this example we have done it by hand and made it generic.

sConnection = "Provider=mrOleDB.Provider.2;Data Source=mrRdbDsc2;"
sConnection = sConnection + "Location='Provider=SQLOLEDB.1;Password=" + sPassword 
sConnection = sConnection + ";Persist Security Info=True;User ID="
sConnection = sConnection + sUser + ";Initial Catalog=" 
sConnection = sConnection + sProject + ";Data Source=" + sServer
sConnection = sConnection + "';Initial Catalog=\" + sServer 
sConnection = sConnection + "SPSSMR_FMROOTMaster" + sProject + ""
sConnection = sConnection + sProject + ".mdd;MR Init Project=" + sProject

This connection string has been setup to use the MDD in the master directory of the server. You will notice that the name of the server is the same as the name of the SQL server in this case so you may need to change this for your setup if you have SQL on a different machine to the Primary Data Collection Server. If you had to do this your string would look something like this.

sConnection = "Provider=mrOleDB.Provider.2;Data Source=mrRdbDsc2;"
sConnection = sConnection + "Location='Provider=SQLOLEDB.1;Password=" + sPassword 
sConnection = sConnection + ";Persist Security Info=True;User ID="
sConnection = sConnection + sUser + ";Initial Catalog=" 
sConnection = sConnection + sProject + ";Data Source=" + sServer
sConnection = sConnection + "';Initial Catalog=\" + sDataCollectionServer 
sConnection = sConnection + "SPSSMR_FMROOTMaster" + sProject + ""
sConnection = sConnection + sProject + ".mdd;MR Init Project=" + sProject

I guess at this point we should also show you a normal connection string to a normal sql table. If you wanted  to connect to a normal table, let’s say something like a table in the participants database , it would look like this.

sConnection = sConnection + "Provider=SQLOLEDB.1;Password="
sConnection = sConnection + sPassword + ";Persist Security Info=-1;User ID=" + sLogin
sConnection = sConnection + ";Initial Catalog=" + sProject
sConnection = sconnection + ";Data Source=" + sServer

Ok so now that we have our strings sorted out we need to test them to see if they are correct. Type the following in

oConnection.Open(sConnection)
If ( oConnection.State = 1 ) Then
     ConnectionInfo.Label.Inserts["Info"] = "Connection to the Database worked"
Else
     ConnectionInfo.Label.Inserts["Info"] = "Connection to the Database failed"
End If
oConnection.Close()

The first thing we have done after adding this code in is to open the connection. We do this by passing the connection string into the open function of the connection object and then we check the state of the connection object. If the state is set to 1 then we know that the connection was successful and we can then do the label insert into our info item to say that the connection was a success. If we get anything else other than a 1 then we know something has gone wrong so we record that fact by doing another label insert. Once all this has been completed we then close the connection object. We do this because we do not want to make a connection and then leave it open if we are not going to do anything with it. Basically we don’t want to leave open connections to the server as they take up resources that could be used elsewhere. And finally we destroy the object and show the results.

Set oConnection = Null
ConnectionInfo.Show()

And there you have it, we have , in our Data collection survey made a connection to an SQL table. In the next article we will start to learn about the things that we can do once we have made the connection.

The full code for this article can be found in :
http://CodeCorner.SmaterDimensions.Com @ Connect to a Data Collection Survey

4 thoughts on “Learn ODBC : Getting Connected

  1. hello,
    1- in the case where we try toconnect to connect to a Data Collection survey with each account ( username , password ) we ca try the test ; i’ve try all them and i can’t acces the projet.
    2 – is it necessary to install Data collection data model.

    i’m working on version 6

  2. Hi Imane

    1: I suspect that you have not used the correct accounts. Out of the box you would only be able to use the Installing user account or the running user account, but you will only have access to these if you did the install. I suspect that you need to talk to IT/SQL Admin to get an account that has access to your data.

    2: If you are writing your survey script in any of the Data Collection products then no it is not necessary to install the Data Model as it is already installed on your machine.

Leave a Comment

%d bloggers like this: