I want to pull all current employee names (PMNAME) from our ERP system (file name PMEMPM) in to the name field of my Notes db. The ERP system’s Name field includes all current and terminated employees. Current employees are designated by having a blank in the status (PMSTS) field. I have the @DbCommand coded as follows:
@DbCommand (“ODBC” : “NoCache” ; “Data_Source”; “”; “”; “SELECT PMNAM FROM PMEMPM WHERE PMSTS = ’ '”)
When I click the down arrow on the dialog list for the Notes Name field, I get the error:
Could not execute @db function
The Select Keywords box appears with the Keywords B1:B0
I am not sure what I am missing. Can anyone offer any guidance? Thank you.
I’ve had this problem many times before. It usually turns out to be either: the ODBC data source is improperly defined OR the ODBC driver does not support the operation (unlikely in this case, the SELECT statement looks innocent enough - should you be supplying a username and password though ?).
You can do some basic tests on whether ODBC is set up correctly. Start a DOS prompt, go to the Notes 6 executable directory and type the command NDCTEST.
The program will prompt you through a series of tests to connect to the data source of your choice. If it cannot connect, it usually gives you a clue as to why it is failing.
If all else fails, try obtaining a third party ODBC driver. All drivers are not created equal. Good luck.
I immediately get prompted for my Client Access password. All names in the PMNAME field appear. Unfortunately, these names also include terminated employees (PMSTS = T) that I am trying to avoid in the pulldown. Do you agree that the ODBC driver is working properly?
The SQL statement was causing the problem. The statement I had coded in the @DbCommand was too vague. I ended up writing a query in Microsoft Query with the parameters that I needed. I then copied that statement in to the Notes statement and got the results that I needed.