I have a legacy Access database that is still being used.  I would like to have Sesame look up some information from the Access database.  In other words, can I be in Sesame and enter a lot number and have Sesame automatically look in the Access database and pull some data from Access into another element in Sesame?

It's Access 2007.  Just to be clear, I know I could move all the data from the Access file to Sesame by downloading the file to a .csv file and then uploading into Sesame. 

That would require that I create a database to put all the Access data, which I'm trying to avoid.  Doing so would take more time than I want to invest.

I was hoping I could get the Access info "as needed" from my application in Sesame.

If what I'm asking is not possible let me ask another question.  Is it possible to have Sesame upload a .csv file automatically when the user enters a certain form?  Or can sesame create a temporary list from a csv file and then use that list to "look up" a value?

So, which possibility seems the most likely to succeed, and how do I do it?

Couple more questions for you NHUser:

Does the Access database have security on it?
What is the Table name in the Access Database that you want the data from?

-Ray

.mdb file extension or was it created in Access 2007 or converted so it has the .accdb file extension?

-Ray

Hey Paul,

Okay got it working.

First step
Open Notepad
Copy and paste the following
Quote:

Replace the word scrit with script(Had to change it in order to post it, line 8 and line 39)
Click File->Save As
Change Save As Type: to "All Files"
In File name: enter "C:\Sesame2\GetDataFromAccess.wsf", without the quotes.
Click Save
Close Notepad

Okay now in Sesame Designer, Go ahead and add a command Button named something that describes that it's going to get Data from Access.
Go into the On Element Entry event of that command button
Put the following code

Var vScriptPath as String
Var vAccessFilePath as String
Var vQuery as String
Var vReturn as String
Var vPos as Int

vScriptPath = "C:\Sesame2\GetDataFromAccess.wsf"
vAccessFilePath = "C:\MyAccessFile.mdb"

vQuery = "Select [Elastomer History].[FieldOne], [Elastomer History].[FieldTwo] FROM [Elastomer History] WHERE ((([Elastomer History].[Lot Number])='" + LotNumberElement + "') AND (([Elastomer History].[Base Material Number])='" + BaseMaterialElement + "'));"

vReturn = @RedirectProcess("cscript """ + vScriptPath + """ """ + vAccessFilePath + """ """ + vQuery + """ ""chemist""", "")
If vReturn <> "" Then
{
	vPos = @InStr(vReturn, @Chr(10) + @Chr(13) + @Chr(10))
	vReturn = @Right(vReturn, @Len(vReturn) - vPos - 2)
	ElementOne = Split(vReturn, "|")
	ElementTwo = Split(vReturn, "|")
} 

You will need to replace the following parts with your actual names.
C:\MyAccessFile.mdb = The actual path to your mdb file
FieldOne = The name of First Field in the Access Table you want Data from
FieldTwo = The name of Second Field in the Access Table you want Data from
LotNumberElement = The name of the Element on your Sesame form that contains the Lot Number
BaseMaterialElement = The name of the Element on your Sesame form that contains the Base Material Number
ElementOne = The name of the First element on your Form in Sesame that you want the data from FieldOne in Access to go
ElementTwo = The name of the Second element on your Form in Sesame that you want the data from FieldTwo in Access to go

For more than two elements just modify the vQuery line(If you want you can create the query in Access 2007, go to SQL view and then just copy the Select part), and then copy the line with Split() in it for each new element.

I think that's everything. Now I'm going to go enjoy my evening knowing that I do not have to look at Access for the rest of the day 

-Ray

It's working great!!  Doing just what I need it to do!

Try adding a ForceRedraw() call after your visibility call to force Sesame to redraw the screen with that element now visible. 2.5.3 will make this easier as it has a command to set the mouse pointer to an hourglass symbol and then back again.

-Ray