relinking ODBC tables (1 Viewer)

[PeterWieland]

Hi,

I have a A2000 frontend, SQL2000 backend database, and I have used the code from Microsoft Knowledge Base to Relink ODBC tables when the database opens. This logs the user into the SQL server tables as a data browser user, the username and password stored in a table as per the MS article. I have created a second, similar procedure and table that should log the user in as the dbo for admin purposes. This code is run from a password protected command button. Most of the time the tables relink ok, with full rights, but sometimes 1 or 2 of them only have the original read permissions, and records cannot be added/deleted/modified. If I goto Tools/Database Utilities/Linked Table Manager and manually relink the table all is ok.

Does anyone know why this happens, and more importantly, how to stop it?

Peter

 

[Pat Hartman]

Do the problem tables have unique indexes? Jet will only allow updates to ODBC tables if they have unique primary keys.

 

[PeterWieland]

Hi Pat,

Thanks for the reply. I have been on vacation, just got back today.

Yes, all of the tables have a primary key defined, I found out a while ago that Jet has to have this to be able to update. Anyway, the problem only occurs if I try to re-link as a different user in code. If I use the Linked Table Manager it works first time every time.

Peter

 

[Pat Hartman]

Sounds like some users have read-only access to the db.

 

[PeterWieland]

Hi Pat,

There are only 2 users set up. The database users attach to the db as 'user', and have read-only rights to most of the tables, and rights to add and modify data on 2 of the tables. I, as the database creator and administrator, need full access to all the tables for administrative functions.

What I am trying to achieve is as follows.

When the database front end opens, it automatically attaches to SQL Server as 'user'. This bit works fine, using the code from the MS Knowledgebase. I have a command button 'Administration', which opens up a form and asks for a password, which also works fine, and when the correct password is entered, I run the MS code which should re-attach me to the SQL tables as 'administrator'. This is the bit that does not work properly. Some of the tables do not always re-attach with administrator rights. I have been running this system for some time, and in the past I have used the Linked Table Manager to re-attach, but with over 20 tables in the back-end, it gets a bit tedious, I wanted to automate the process.

I could have a seperate frontend for administration, but every time I make a change to the frontend, I would have to remember to make the same changes to both the 'users' and 'administrators' versions.

Peter