Linking to MS Access Database on Local Drive

[SteveX]

I am very new to Revolution and I would like some step by step instructions for accessing data in an existing MS Access database. I would like to eventually learn how to add/modify/remove/query this data. Basically I want to be able to build the database structure in MS Access and then use Revolution to build the User Interface. Is this possible? I know I have a ton of new terminology to learn during the conversion from Access to Revolution so be gentle....

Thanks,

Steve X.

[Lynn]

I am very new to Revolution and I would like some step by step instructions for accessing data in an existing MS Access database. I would like to eventually learn how to add/modify/remove/query this data. Basically I want to be able to build the database structure in MS Access and then use Revolution to build the User Interface. Is this possible? I know I have a ton of new terminology to learn during the conversion from Access to Revolution so be gentle....

Thanks,

Steve X.

Hi Steve,

What version of Revolution are you using?

[SteveX]

Version 2.7.2

I have just recently downloaded MySQL server and associated tools. If that would be easier then I can give it a shot as well. I can get Revolution to connect to the MySQL server running locally, however, I haven't been able to figure out all the commands necessary to be able to write to the tables and then recall the information. I really want to learn to use Revolution, but thus far I have had a great deal of difficulty trying to transition from MS Access to Revolution.... I haven't given up yet.... I'm a glutton for punishment..

Thanks for any and all help you can give. (a step-by-step book would be nice

Steve X.

[Janschenkel]

Hi Steve,

While you cannot directly connect to MS Access databases from Revolution, you can use Rev's generic ODBC driver to connect to a Data Source Name (DSN) that you setup in the ODBC Control Panel of your Windows computer.
Think of a DSN as a shortcut that you can reference by name.

So to connect to an MS Access database from Rev, use the following steps:
1) Create a DSN in the ODBC Control Panel
. Go to the ODBC Data Source Administrator - Control Panel
. Go to the tab 'System DSN' and click the 'Add' button
. Select the line 'Microsoft Access Driver (*.mdb)' and click 'Finish'
. Give it a name that helps you remember which database it is (you will use this later on) - for this example we'll use 'RevAccessTest'
. Select the .mdb file that is the actual Access database
2) Connect to the DSN from Revolution using the ODBC database type

Code: Select all

put revdb_connect("odbc","RevAccessTest",,,) into tConnectionID
-- check if we could connect; if not, show an error
if tConnectionID is not a number then
  answer error tConnectionID
else
  -- we're connected; execute some query
  put "SELECT * FROM customers" into tQuery
  put revdb_querylist(tab,return,tConnectionID,tQuery) into tQueryList
  -- close the connection
  get revdb_disconnect(tConnectionID)
end if

As for MySQL, you don't need to setup a DSN or anything to connect to it from Revolution. But you'll have to create the schema yourself. I believe you can download a graphical MySQL that will make this process a lot easier, in a way close to Access and SQLServer.

Hope this helped,

Jan Schenkel.

[SteveX]

Thanks but I figured out how to connect to the database via ODBC. However, I don't know how to add, modify, delete records.

I'm getting closer but so far - no cigar....

Thanks for any help..

Steve X

[Janschenkel]

Hi Steve,

You're going to have to dig in and learn the SQL language:
- use SELECT to find records
- use INSERT to create a record
- use UPDATE to modify records
- use DELETE to delete records

For an introduction to SQL, see:
- http://www.w3schools.com/sql/default.asp
- http://www.quartam.com/tutorials/sqltutorial.htm

Hope this helped,

Jan Schenkel.

[SteveX]

Thank you Jan!

That is what I was most unsure about. Do I only use the SQL language for the database commands or will I be using SQL commands for all aspects of Revolution?:roll:

I've been away from the coding aspect of programming for a while now so I've got a lot to learn. MS Access doesn't necessarily require a huge amount of coding to create a database and build a user interface for that database.

I love to learn new things so - Here I Go...

[SteveX]

Jan,

I am working with the MySQL database and I am trying to use the SQL command to insert information but it's not working. Here is the code I am using in the script for my button:
revsqlexecute "Insert Into Members (Firstname,Lastname) values Field 'FirstName', Field 'LastName'"

I have also tried the following:
revsqlexecute "Insert Into Members values Field 'FirstName', Field 'LastName'"

What am I doing wrong? I am not getting an error when I apply the script but when I test the program I get the error:
Type Handler: can't find handler
Object NextBttn
Line revsqlexecute "Insert Into Members values Field 'FirstName', Field 'LastName'"
Hint revsqlexecute

This doesn't tell me anything!!

I am not getting any information sent to the SQL database.

HELP!!!

[Janschenkel]

Hi Steve,

"Can't find handler" means exactly that: the interpreter cannot find a command (aka 'message handler') by the name of 'revexecutesql'

To execute SQL queries that modify the database contents (such as INSERT, UPDATE and DELETE), you can use either the 'revdb_execute' function or the 'revSQLExecute' command.
Both require as their first parameter a connection ID, and as their second parameter an SQL query. The difference between the two is that the first is a function and thus returns a result, and the second will update the global variable called 'the result' when it's finished.

Furthermore, Revolution doesn't really look at the query to fill it up with the contents of fields or variables. You havez to construct the complete query yourself as a string.

Code: Select all

on mouseUp
  put revdb_connect("odbc","RevAccessTest",,,) into tConnectionID
-- check if we could connect; if not, show an error
if tConnectionID is not a number then
  answer error tConnectionID
else
  -- we're connected; execute the INSERT query
  put "INSERT INTO members VALUES ('" & \
      field "FirstName" & "','" & field "LastName" & "')" \
      into tQuery
  put revdb_execute(tConnectionID,tQuery) into tQueryResult
  -- close the connection
  get revdb_disconnect(tConnectionID)
end if
end mouseUp

I recommend that you download Sarah Reichelt's tutorials about using MySQL databases from Revolution: http://www.troz.net/Rev/tutorials.php
This way, you can pick up all hte necessary skills to connect to a database from Revolution, and then deal with the idiosyncracies of connecting to Access databases.

Hope this helped,

Jan Schenkel.

[gimpeltf]

I'm having trouble Updating a field when there's an apostrophe (single quote) in the field. The same code put directly into Access works fine so it has to do with the interface.

put "Update Campers Set LastName='" & fld LastName & "' where ID=" & fld WID & ";" into uQuery
put revdb_execute(tConnectionID,uQuery) into dummy

Of course, there would be a problem with unmatched quotes in the above if the Name was O'Boyle.
I've taken out the single quotes in the code and replaced with & quote.
the value in uquery wouls be
Update Campers Set LastName="O'Boyle" where ID=365;
This code would work directly in Access but returns an error of
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Any thoughts?

Gimp

[Janschenkel]

In 'standard' SQL, strings are opened and closed with apostrophes, not quotes - some SQL databases may allow both quotes and apostrophes, but Access is not one of them.
Try 'escaping' the apstrophe instead:

Code: Select all

on mouseUp
  put "Update Campers Set LastName='" & SQL_Escaped(fld LastName) & "' where ID=" & fld WID & ";" into uQuery
  put revdb_execute(tConnectionID,uQuery) into dummy
  answer dummy
end mouseUp

function SQL_Escaped pData
  replace "'" with "''" in pData
  return pData
end SQL_Escaped

Hope this helped,

Jan Schenkel.

[gimpeltf]

First off, I found the answer to my previous question in this thread on another thread before coming back here.

But, I have a question about setting up the ODBC path and other info. I know how to do it through the Control Panel manually. Is there a way to do it via Revolution?
I would like to be able to have the application itself use an Ask File dialog to allow the user to locate his file and then plug in all of the ODBC stuff that's needed.

Gimp

[Janschenkel]

While you can't do it directly using Revolution commands, you can do this on Windows by using VBScript. I found a VBScript here: http://www.enterpriseitplanet.com/resou ... hp/3089341

The easiest way would be to keep a template VBScript in a property, replace placeholders (for the database path, etc.) with the actual contents, then spit that out to a temp file and execute it using the 'shell' function:

Code: Select all

on mouseUp
  answer file "Select an Access database"
  if it is empty then exit mouseUp
  put it into tDatabasePath
  -- now get the template script and fill it up
  put the uDSNCreationScript into tVBScript
  replace "[[DatabasePath]]" with tDatabasePath in tVBScript
  ## TIP: look into the 'merge' function!
  -- put the script into a temporary file
  put the tempName & ".vbs" into tScriptPath
  put tVBScript into URL ("file:" & tScriptPath)
  -- and execute it silently
  put the hideConsoleWindows into tSavedHCW
  set the hideConsoleWindows to true
  get shell("cscript.exe //nologo" && tScriptPath)
  set the hideConsoleWindows to tSavedHCW
  -- make sure to clean up the tempprary file
  send "delete file" && quote & tScriptPath & quote to me in 1 second
  -- this gives enough time for the script to run before you delete it
end mouseUp

Hope this helped,

Jan Schenkel.

[gimpeltf]

Thanks, I'll give it a try if it isn't over my head.

[Adrien Bron]

Thanks for the information, has been very useful to me.