Linking tables between two *.mdbs

[Krava]

Hi folks.!
I am new at this forum and I would like to have one question to you.
I have postet this thread on VB forum but no success so I hope for success here.

I am stuck with one problem I cannot figure out. I have a data *.mdb placed on server and I have a client *.mdb at every PC. The data *.mdb is password protected so first I used a manually created table links from data *.mdb to client *.mdb. But now, I would like to have a client *.mdb without permanent table links and I would like to add a links by VBA by executing the client *.mdb according to special conditions. I know how to link a tables from *.mdb to *.mdb if the source *.mdb (data) is not password protected. In case of password protected source I am lost and I tried many different ways. Do you have any opinion on that how to solve it? I think it is not possible to do it by direct link, but with ODBC only, but I am not sure.
Thank you in advance.

Krava

 

[Pat Hartman]

The easiest way to do it is to copy the connection string for the linked table from the MSysObjects table. That gives you the exact connection string.

BTW, it is far more efficient to leavet the tables linked than to link them in the background every time you open the db.

 

[Krava]

Hi, Pat!

Thank you for your suggestion, I will try it.
I agree with you about permanent link efficiency, but I have a reason to do so, because our company is located in three different locations.
At every location there is a server and number of clients. I develop an application on the same principals for all three locations. I am able to read IP address of client at every location and according to IP range I want the client to make a table link to its server data. This will happen only once- by first execution of client application. This is my idea how to solve three-location problem.
Maybe it is not one of the bests, but...

Regards from
Krava

 

[Krava]

Hi, Pat!

I have tried it and I managed to change the Connection property on permanent table links so the result is just excellent!

Many thanks one more time.

Krava

 

[Bosch]

security question again..

Pat/Krava..

I have the same situation. I need to relink my tables programatically and my backend is password protected. How do I pass the password ?

Seems like Pat have the solution..but I did not understand his comment.
"The easiest way to do it is to copy the connection string for the linked table from the MSysObjects table. That gives you the exact connection string."

Pat..Can you explain this? I use access 2003..I did a select on the Msysobjects table..but I dont see any connection string! What am I missing here?

Thanks

 

[Bosch]

May be my problem is little different..
I use the workgroup security for both backend and frontend..same mdw file is used for both FE and BE. My users have access to read design, read,update data from the tables..but when they open the front end and the program tries to relink tables from the database in a different location, they get message that says they dont have enough privileges to link. I think when they try to relink thru the pgm, the mdw file that is used to open the FE is not being used..FE tries to link to the tables in backend without passing any credentials..

Sorry..I am not even sure if I am explaining my problem clearly..Any help will be appreciated.

Thanks

 

[Bosch]

btw..the connection string in my msysobjects table is empty..under database field, it has the complete path for the BE db.

 

[Bosch]

What level of access needed to be able to relink..

Sorry for the muliple posts on the same question here..I just want to make sure I clearly explain my problem and hope to get some response from this forum..

Both my FE and BE have workgroup security. I have given my users read design, read/insert/update/delete data access on all the tables and linked tables. Both FE and BE uses the same workgroup file.
My application needs to programmatically relink to different databases based on whether they are connected to the company network or not..(My database is like a knowledge base, I want it to be available to the users whether they are at work or away..they will have to copy the db on the network to their computer to use it in the read only mode when they are not at work)..

Problem is, the relink will not work for my users. they get a message saying they dont have enough privileges to perform this action.

Someone please help me to resolve this problem.

Thanks.

 

[Pat Hartman]

I don't use Access security so I'm no expert but I think you need admin permission in order to link tables and your users don't have that permission so that is the problem. I don't believe that doing it in code will alter the situation.

 

[Bosch]

Pat..
Thanks for your reply..
I even tried giving admin rights to the users, still they get the same message.
Error # 3033..

Hope somebody will shed some light on this..

Thanks

 

[Bosch]

I found this link, which answered all my questions. Just in case anyone needs it..here is the link.

http://support.microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp#_Toc493299684