Below is a partial cut&paste from the 8.0 ODBC Toolkit but it's the same process for 6.1. I tried to correct the port numbers etc but I may have missed something.
It's not mentioned below but you'll need exclusive access to the database before you execute the query. This basically means you'll need to kick everyone out (including Data Collection stations) and stop the appservers. After creating the view, you need to go back and give the user access to the new view with a GRANT statement. Restart you appservers and you should be in business.
How to setup additional ODBC users with varying user rights:
1.On the server, Start button --> Programs --> Progress 9.1D --> SQL Explorer Tool.
2.Select "File" --> Connect
3. Fill in the following:
Service or Port: <ODBCGroup Port> e.g. 6150
Password: <Your chosen password for sysprogress>
Then click "Ok"
4. Type in the following or copy / paste from the script included in this kit.
CREATE USER 'odbcuser','odbcuser';
This creates the user "odbcuser" with a password of "odbcuser", you can substitute in your names and passwords.
5. Click the lightening bolt button to execute the statement. Note if there are any errors in the below window.
6. Repeat this procedure for each user you wish to create.
7.Once the users are created they have no rights on any tables, to give them rights type the following code or copy/paste from the script included with the kit.
GRANT SELECT ON PUB.abccode TO odbcuser;
GRANT SELECT ON PUB.agingrptfmt TO odbcuser;
GRANT SELECT ON PUB.alertgroup TO odbcuser;
8. Then click the lightening bolt button to execute the statements. Note check for errors in the window below.
9. Repeat for each user
Since specifying all the tables can be tedious, we have provided a list in the included script, which you can edit to meet your needs it is included to reduce the workload in typing. You would copy/paste in the 1000 or so tables to give them SELECT rights on these tables.
Note the statement can specify 4 basic rights SELECT(read), INSERT(write a new record), UPDATE(change an existing record), DELETE( delete an existing record). You can allow all the rights in one statement if you wish. See below.
GRANT SELECT,INSERT,UPDATE,DELETE ON PUB.abccode TO odbcuser;
We in Support do not recommend any rights to common users other than read rights i.e. SELECT.
"PUB.adccode" is the table. All table names are preceded by the public designation or PUB.
"TO odbcuser" is the user you are defining rights to.
";" end all line statements with a semi-colon.
"COMMIT;" End the whole block of statements with a commit to ensure the statement is commited whether you have AutoCommit enabled or not.
Additional information on support for SQL commands, check the OpenEdge online documentation:
Under Data Management --> SQL Development or SQL Reference
Creating a View(optional: when necessary)
When you get the error too many indexes, known to occur on Customer table.
For other errors using MS Access where the call fails use the dbtool and Data Dictionary to remove SQLwidth problems.
1. How to add a view for a table in this case Customer.
Open the SQL Explorer tool. [START] Programs OpenEdge SQL Explorer Tool.
Select FILE CONNECT.
Service or Port:6150 (ODBCport)
As an example for Customer table, fill in the following text to the upper statement box in the SQL Explorer.
CREATE VIEW odbcuser.customer_view AS
SELECT * from pub.customer;
NOTE: you need to give the view an owner name e.g. " odbcuser ". If you do not the SQL
statement will error. Depending on the owner you give you might need to make sure the permissions for that user are given for the view AND the underlying tables the view refers to. So would need SELECT permissions on PUB.customer and odbcuser.customer_view.
To execute the statement either press the Lighting Bolt, or <Ctrl> <E>. You may also need to user "Commit;" to commit the changes if the autocommit option is not enabled.
Open a blank database, then File Get External Data Import
In the "Import" window, at the bottom is a field called "Files of Type" dropdown to select ODBC DATABASES ( )
Select the DATA SOURCE i.e. DSN, Mfgsys61 will likely be the DSN, but the name is not important just as long as it connects to the database that you created the view in i.e. 6150 port and click OK. You will be prompted to supply a password.
Then the "link Tables" dialog allows you to select the table
Depending on the owner name chosen, it should be at the top of the table list. Highlight it and click [OK].
Select the fields desired.
--- In firstname.lastname@example.org, "John Sage" <list@...> wrote:
> Howdy. Does anyone remember how to make a custom table view for Vantage
> 6.1 / Progress 9.1? I cannot find my "howto" document.