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

Posted by Admin on 22-Jan-2009 12:13

We are developing a solution that uses a Progress 10.1a database as back-end and we are trying to use Access 2007 or Visual Studio 2008 as front-end.

The ODBC finds the databases and accesses the list of tables and columns, but when we try to create connections to them we receive an error stating that the object couldn't be found, and ask to verify the spelling of the table name.

We use the Progress Client (DataDirect Technologies Driver, with Windows Vista Ultimate 64Bits, MSAccess 2007 and Visual Studio 2008.

We access and map all the SYSPROGRESS Tables normally but none of the PUB.TABLE-NAME is accessible with SELECT or any SQL Statement.

We also fail with the link/connect method of both Access 2007 and Visual Studio 2008.

In the connection string we've tried with the "PUB." text in front of the table-name (in both upper and lower case), without the "PUB." text, with the access 2007 linked tables wizard, manually mapping one by one, and other procedures. None worked with those PUB.TABLE-NAME.

Please Help!

All Replies

Posted by Simon de Kraa on 22-Jan-2009 12:56

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

Posted by Admin on 22-Jan-2009 13:02

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

Posted by Admin on 22-Jan-2009 13:03

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

Posted by Admin on 22-Jan-2009 13:05

Here is my connstring:


Posted by Admin on 22-Jan-2009 13:11

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.

Posted by Admin on 22-Jan-2009 13:18

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.

Posted by Admin on 22-Jan-2009 13:23

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

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.

Posted by Thomas Mercer-Hursh on 22-Jan-2009 15:10

You might find some ideas here

Posted by Admin on 23-Jan-2009 10:09

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?

Posted by Simon de Kraa on 23-Jan-2009 10:23

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

Posted by Admin on 23-Jan-2009 10:32

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

Posted by Simon de Kraa on 23-Jan-2009 11:31

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.


_Dbaacc = Y --> SQL DBA access

_Resacc = Y --> Resource privileges

Posted by Thomas Mercer-Hursh on 23-Jan-2009 11:34

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.

Posted by Simon de Kraa on 23-Jan-2009 11:38

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".

Posted by Admin on 26-Jan-2009 12:58

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?

This thread is closed