How to connect Database using ODBC

This lesson demonstrates how to use ODBC to connect to a database. Here we have accessed MS Access Database and have fetched results from it. We have created this example using LiveCode, MS Access 2003 and Windows XP, but you can use ODBC to connect to any database that supports it, from within LiveCode.

You can download the associated sample stack and database for this lesson below.

Creating a Database using MS Access

Creating a Database using MS Access

Click File->New, this will open a Tab in the right side of the Application which will ask you to select what type of DataBase you are creating. Click Blank Database option. This will create a blank Database, which we have named livecodeTest.

Creating a table in MS Access

Click "Create Table in Design View" , this will open up the table design. Add two fields labeled Name and Company to the table and save it.

Fill the table with Data

Double click the table "test", this will open the blank table. Fill in some data and then save the table and database.

Create a ODBC connection for the Database livecodeTest

Step 1: Goto Control panel -> Administrative Tools -> Data Sources(ODBC).

Step 2: Add new Data Source

Step 2: Add new Data Source

Click the ADD button. This will popup a window asking for the correct driver, Select Microsoft Access Driver (*.mdb) and click finish

Step 3: Selecting the Database file and Naming it.

Step 3: Selecting the Database file and Naming it.

Set the name as "runrev" and select the database file livecodeTest.mdb.

Step 4: Creating Login and Password

Step 4: Creating Login and Password

Click on Advanced button to set the Username as "testUser" and Password "123456". Click Ok to save login details and click ok to save the Data Source. Setting username and password is essential to protect the data, of course for a real world database you would use a secure password.

Step 5: Accessing ODBC connection in LiveCode

Step 5: Accessing ODBC connection in LiveCode

Create a basic test stack with a card, a scrolling field, and a button called "Fetch Data", as above.  We will now add code to the Fetch Data button so that it will fetch all the data from the Database Table "Test" and display it in the scrolling field.

Step 6: Coding for Fetch Data

on mouseUp
   
   local tDatabaseID
   
   --This will open up connection for the MS Access Database using a ODBC connection
   put revOpenDatabase("ODBC", "runrev", "livecodeTest", "testUser", "123456" ) into tDatabaseID
      
   if tDatabaseID is null then
      answer "Not connected"
   else
      answer "connected"    
      
      --This statemenr will select all the data from the table Test 
      put "SELECT * FROM test" into tQuery      
      
      --This will query the Database Table using the statement in tQuery
      put revDataFromQuery(tab, cr, tDatabaseID, tQuery) into tData
      
      --This will display all the data from the Database
      put tData into field 1
      
      --This closes the Database connection
      revCloseDatabase tDatabaseID
   end if  
   
   
end mouseUp

Step 7: Displaying Results

Step 7: Displaying Results

Click on the Fetch Data button, and you should see a result similar to the above. We have fetched the data to LiveCode from a MS Access Database using the ODBC connection.

Note: While accessing the MS Access Database file from LiveCode, you need to make sure the Access Database is closed, otherwise it will throw an error.

2 Comments

Will

I'm trying to create an app that gets data from a MS Access database. The database has about 120,000 records of six fields each. From what I've read, using a ODBC link maybe slow so a MySQL or SQLite may be a better choice. However it is not clear to me how to convert my MS Access database to anything related to SQL. Any direction to some self-help would be appreciated. I'm on a MAC. Also, I can't seem to find any tutorials on ODBC for a MAC.

Help!

Hanson Schmidt-Cornelius

Hi Will,

best place to look is in our community. I would be surprised if there was not someone out there who could help you.

You can find the forums here: http://forums.runrev.com/

In particular, have a look at the "Databases" forum.

Kind Regards,

Hanson

Add your comment

E-Mail me when someone replies to this comment