HELP! Link to Progress DB using ODBC with Access 2007 or Vis

Are you connecting with the user that created the Progress database?

No. I use the admin-admin user or my user on the domain.

sadly I don't know the owner's password. It's a Datasul ERP database.

Here is my connstring:

"ODBC;DSN=ems2mov_local;HOST=127.0.0.1;PORT=15500;DATABASE=ems2mov;UID='admin';PWD='admin';;TABLE='pub.ped-venda'"

Are you able to pull data by using MS Excel? I just set this up this afternoon where I have SQL Cleint on Win32 machine , and Linux OE10-1A database and i was able to pull data from the database using the SQL server's port. I tried Access but don't know how to make links or import data. All I can say that is if you can import into Excel, you should be able to import into any MS application using the ODBC setup you have.

Agreed. But also returns an error. It simply doesn't return any data or allows retrieval of any kind but of reading the table structure. It's acusses a mis-spelling of the table name. Not of bad user/password.

Did you grant SELECT on all of your PUB tables? This is a big change in OE10 security as apposed to 9.1 version. Goto progress KB, search how to grant select statement for OE10 database to public. Once you have done this step, try the Excel setup. I am using Progress 10.1A driver version 5.10.00.37.

You might also want to try using sqlexp command from DB server side and run "selct * from sysprogress.sysdbauth; and see what users are granted access to the tables. There are some good docs on Progress KB, to show you how to change security using sqlschema and sqlexp commands.

You might find some ideas here http://www.oehive.org/node/954

Thank you Rico and Mercer. Your posts were helpfull but didn't solve the problem. You see, we tried to connect via Delphi and we succeeded to SELECT, UPDATE, INSERT, etc. with the same ODBC client. We only can't when we try with ODBC/ADO.NET, ODBC/DAO or LinkTables on MSAccess. It's very odd.

The big trouble is we need to do this job in Access or Visual Studio, we can't use Delphi because our client has all their programs developed in those systems.

Does any of you know if there is a default Administrator password in OE10?

I believe the Datasul people didn't change this password when they developed their ERP in progress language but of course they don't just inform the password to competitors...hehehe

We are trying Administrator as user and password and has worked fine, but maybe it's a catch we don't know... Does the SYSPROGRESS user has a default password? Can we use the SYSPROGRESS user to connect to the database in order to access tables and columns?

The user that created the database is in sysprogress.sysdbauth.

Edit: the user that created the database default has DBA access and resource access...

Message was edited by:

Simon de Kraa

And how do you recover the password of this user if you've lost it or forgot it or just don't know it? The creator is the SYSPROGRESS itself...I think. In the table has two users: Administrator and SYSPROGRESS and both have the other 2 columns marked as "y". What does it mean? On the other table (SYSPROGRESS_SYSTABAUTH) some colums are marked "g" (for grant I guess) but others ar marked "y" also, what's the difference?

Message was edited by:

Juan Pablo Silenzi de Stagni

Ok, that means the user Administrator has created the database and is by default a SQL DBA.

You should be able to use this user with a blank password to connect to the database and retrieve records.

FOR EACH _Sysdbauth.

DISPLAY _Grantee _Dbaacc _Resacc.

END.

_Dbaacc = Y --> SQL DBA access

_Resacc = Y --> Resource privileges

It would be unlikely that the creator was sysprogress unless you actually have an operating system user by that name. Do you have entries in _User?

What one usually gets with a new database is that the user who created the database, often root in a Unix environment or Administrator in a Windows one, and sysprogress will have default privileges. But, that doesn't mean that there are any entries in _User for them.

If you are not using _User for authentication, then there is a technique which I don't recall for getting in without a password, but I don't remember it exactly because I would never want to use a database that way. Instead, I create _User records for root and sysprogress and typically for a third user which will be used for this access by the outside product. All these then get passwords assigned as a part of being set up in _User. Then I grant privileges to the third user as I described in the OE Hive link I gave you and that is the user use for all SQL access.

If there are entries in _User for all your regular users, but not for root and sysprogress, then just create the additional ones as I described. If you have no entries in _User, then you are going to have to proceed more cautiously since the minute you assign one _User record, everybody will need one. If that is your situation, we are going to have to find you the reference on how to access with nothing in _User.

Yes, like Thomas said you should check the _User table...

See attachment for the ProKB entry "Basic Guide to Defining Progress SQL-92 Database Permissions & Security".

Yes guys thank you very much for your help, It was very usefull.

I didn't get where I wanted but I think I got a possible solution. A friend told me that I could publish the Progress tables on my MS SQL Server. We contracted a programmer to create a satelite program to this Datasul ERP and it was made in Delphi, it comunicates with the progress DB easily and with no fuss using the Administrator/Administrator pair (User/Password). Since seems to me the problem is with the VB/Progress-Progress/VB set, I could workaround it making the connection via SQL Server 2005. Do you know where could I get a step-by-step instruction about howto make this happen?