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
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.
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.
Double click the table "test", this will open the blank table. Fill in some data and then save the table and database.
Step 1: Goto Control panel -> Administrative Tools -> Data Sources(ODBC).
Click the ADD button. This will popup a window asking for the correct driver, Select Microsoft Access Driver (*.mdb) and click finish
Set the name as "runrev" and select the database file livecodeTest.mdb.
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.
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.
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
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.