Re: VB app connectivity problems with Oracle 8i Server

The problem is not the ODBC driver per se.

This has to do with synonyms, code behavior, and permissions.

1. Even if you have a public synonym, SQL will always look in your schema first. Either your table name must have a schema prefix, or users cannot have tables of the same name. This cannot be overridden.
2. Many ODBC drivers and development packages will prepend table names with the user. So select * from table_name becomes.

begin
select * from <user>.table_name;
end;

You can override this by setting your ODBC driver connection to pass through. Otherwise, the query may be re-written by the driver or dev environment.

3) You can get around synonyms altogether by granting specific permissions on specific tables to roles and then granting the roles to users.

If all else fails, I would do #3.

Good Luck!
Chris

--

~~~~~~~~~~~~~~~~
Chris Weiss
www.hpdbe.com
High Performance Database Engineering
~~~~~~~~~~~~~~~~


"Priyesh Patel" <priyesh_at_mercurie.co.uk> wrote in message
news:a89eg6$oth$1_at_ayres.ftech.net...


> Hi,

>

> I am trying to migrate an existing VB application so that it uses Oracle


> instead of access database. I am using ODBC

> to connect the application to the oracle server.

>

> I have observed two problems :

>

> a). VB application complains about tables not found if public synonyms are

> created.

>

> b). Similar problem if more than one user has a table with the same name.

>

> The VB application works fine if there are no synonyms and if only one

> schema exists in the database.

>

> Has anyone else encountered this problem ? I have tried the Oracle

supplied


> ODBC driver and Microsoft's.


>

> I would be grateful for any advice.

>

> Regards

> Priyesh

>

> Mercurie IT

>

>