How To Connect To A Database Using ODBC - LiveCode Lessons

Home » How To Connect To Database Using Odbc.net » How To Connect To A Database Using ODBC - LiveCode Lessons

Maybe your like

Skip to main content LiveCode

Dictionary

Guides

Lessons

Courses

LiveCode Lessons Search term How to connect to a 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 from this url: https://tinyurl.com/ya5he5y3

attachment ODBC_Lesson.zip

Heading anchor 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.

Heading anchor Creating a table in MS Access

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.

Heading anchor Fill the table with Data

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.

Heading anchor Create a ODBC connection for the Database livecodeTest

Create a ODBC connection for the Database livecodeTest

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

Heading anchor 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

Heading anchor 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.

Heading anchor 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.

Heading anchor 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.

Heading anchor Step 6: Coding for Fetch Data

Step 6: Coding for Fetch Data on mouseUp local tDatabaseID --This will open up connection for the MS Access Database using a ODBC connectionput revOpenDatabase("ODBC", "runrev", "livecodeTest", "testUser", "123456" ) into tDatabaseID if tDatabaseID isnullthenanswer"Not connected"elseanswer"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 tQueryput revDataFromQuery(tab, cr, tDatabaseID, tQuery) into tData --This will display all the data from the Databaseput tData intofield1--This closes the Database connection revCloseDatabase tDatabaseID endif end mouseUp

Heading anchor 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.

  • Prev: Connecting to a MySQL database
  • Next: How to create and use an SQLite database

5 Comments

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!

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

Hi,

I have the data in Chinese in MSSQL. How can I display the data in Chinese for MSSQL?

Thanks & Regards, Dicky

Hi Dicky,

Take a look at the textDecode function. This should allow you to convert the data from your queries for display in LiveCode.

I hope that helps.

Kind regards

Elanor

It is very important useful and helpful

Add your comment

Name* Email* Comment* E-Mail me when someone replies to this comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Topics

Last Updated

Feb 25, 2018

Author: Maria Pantousi

Other Resources

  • Getting Started with LiveCode

  • Get Up and Running with LiveCode
  • Getting Started with LiveCode Development

  • LiveCode Lessons

  • How To - Step-By-Step Guides To Tasks In LiveCode
  • How To - LiveCode Server Tasks
  • How To - LiveCode Mobile Tasks
  • How To - LiveCode Sample Scripts
  • How to - LiveCode Marketplace Products
  • How to Purchase and License LiveCode

  • Tutorials

  • Creating a Video Library Application
  • Creating an 8-puzzle Game

  • Data Grid

  • LiveCode Data Grid
  • Data Grid Tips & Tricks
  • Converting the Stock Program

  • LiveCode Releases

  • LiveCode 6.5
  • LiveCode 6.7
  • Summer School 2
  • LiveCode 8

  • LiveCode Builder

  • Creating Widgets
  • Building, Sharing and Installing Extensions

Tag » How To Connect To Database Using Odbc.net