Connectivity to Access2000 - help.

[Dan-A]

Hi All,

I'm new to ODBC and I am just trying to understand the basics...

I want to update record in an access table with FM data for a matching field (memberID)...

All examples I found on the Dev CD or in help files point

to an "update statement" UPDATE remoteTable SET myStatus = updatedValue.

My understanding is that updatedValue is a "hard coded value"...

How can I set myStatus = a value from a field in FM for the matching record??

thanks

Dan

[fmsavey]

First you must define a DNS (Data Source Name) using the ODBC administration tool that "points" to the Access database. Next create a new script and add to it the "Execute SQL" script step. Click on the ODBC Specify button and locate the DNS name that you defined using the ODBC Admin Tool and choose it. Next, click on the "Specify Calculated SQL Text" button and write your update statement. Something like...

"UPDATE [access table name] SET [access column name] = '" & [FM field name] & '" WHERE [access column name (key)] = '" & [FM field name (key)] & "'"

When the script is executed it will update the Access field with the information in FM (This is assuming a one to one relationship on the key). To update multiple records put the Execute SQL script step in a loop.

[Dan-A]

Thanks for the info...

table names & column or field names are in brackets is that correct?

So that is the syntax i am missing...

I will try that.

[Dan-A]

This is what i did to get the update to work but.....

"UPDATE tblTempInbox SET DispoStatus = '" &Trim (IncomingNouveauMail::t_StatutDispo) & "' WHERE MemberID = '" & Trim(IncomingNouveauMail::t_MembreID) &"'"

Used the trim function to remove leading space that prevented a match & an update.