Link SQL views

[RobertT687]

I'm using both Access2000 and 2003. I have a routine that refreshes my linked MSSQL table attachments by requiring a logon to the SQL server. All the linked tables have primary keys. When linking a View, the user is prompted to 'Select Unique Record Identifier'. The users frequently click 'OK' or 'CANCEL' without selecting anything. Sometimes this causes performance problems. I'd like to find a way in VBA to perform the link and supply the appropriate key fields for the views without the user seeing any dialog boxes or needing to be aware of the required key fields.

Thanks in advance for any ideas.

 

[easyit]

Why do you need to relink? Did the view change?

EasyIT

"Do you think that’s air you're breathing?

 

[RobertT687]

Because of security concerns we do not save the userid or password when linking. We don't want unauthorized users trying to see the table data. If the user has the login info then they are authorized.
And yes, relinking is also to handle the situation of the view changing. It's infrequent, but transparent to the users.

 

[easyit]

How about creating a read only account?

EasyIT

"Do you think that’s air you're breathing?

 

[RobertT687]

The issue isn't necessarily access to the table/view, but an attempt to avoid the 'Select Unique Record Identifier' dialog box under ALL circumstances and to guarantee that the correct key fields are ALWAYS selected. A read only account won't work because some of the tables are updatable. Views are used for the Read-Only data.

 

[dhookom]

You might want to look at Doug Steele's solution for DSN-less connections at

http://www.accessmvp.com/djsteele/DSNLessLinks.html.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]