N
Nicolas Weeger
Hello, and sorry if this is the wrong forum to post into.
I have a question regarding ODBC linked tables.
Here is the working environment:
Access 97, with ODBC linked tables/views from a SQL Server. Those
tables/views are used in reports.
The VBA modules use an ODBC connection to access the SQL server for
executing stored procedures.
When the application starts, it asks a login/password, which is used to
connect to the database. Then the application calls 'sp_setapprole' to
enter a predefined application role.
The trick is that linked tables are then relinked. First, ODBC tables
are dropped, then based on a query on a SQL Server table they are
relinked. This is so that it's easy to add or remove a linked table to
the Access application.
The issue i'm having is that, to correctly relink the tables, i need to
use the user's login and password. Using the application role's name &
password does not work. This, probably, is because an application role
can't be used to login directly to the server.
So the question would be: is there a workaround to that? Is there a way
to force Access to link tables, but through the ODBC connection I define
in VBA (so with the application role). The aim, of course, is to not
even let users do select on views/tables.
The views are mandatory, since i'm using them in reports. So I can't use
ADODB recordsets for records, short of first getting data from ADODB/SQL
and putting it in local Access tables, then using those local tables for
the reports
Definitely not something I want to do.
Many thanks for your replies and/or useful tips
Nicolas Weeger
(remove NOSPAM from mail)
I have a question regarding ODBC linked tables.
Here is the working environment:
Access 97, with ODBC linked tables/views from a SQL Server. Those
tables/views are used in reports.
The VBA modules use an ODBC connection to access the SQL server for
executing stored procedures.
When the application starts, it asks a login/password, which is used to
connect to the database. Then the application calls 'sp_setapprole' to
enter a predefined application role.
The trick is that linked tables are then relinked. First, ODBC tables
are dropped, then based on a query on a SQL Server table they are
relinked. This is so that it's easy to add or remove a linked table to
the Access application.
The issue i'm having is that, to correctly relink the tables, i need to
use the user's login and password. Using the application role's name &
password does not work. This, probably, is because an application role
can't be used to login directly to the server.
So the question would be: is there a workaround to that? Is there a way
to force Access to link tables, but through the ODBC connection I define
in VBA (so with the application role). The aim, of course, is to not
even let users do select on views/tables.
The views are mandatory, since i'm using them in reports. So I can't use
ADODB recordsets for records, short of first getting data from ADODB/SQL
and putting it in local Access tables, then using those local tables for
the reports
Definitely not something I want to do.Many thanks for your replies and/or useful tips
Nicolas Weeger
(remove NOSPAM from mail)