Access form to skip SQL Server ODBC login

[tcstom]

I have an Access front end containing tables linked from two SQL Server databases on the same server. This means that my users are prompted twice for the same ODBC login account. What I really want is an Access form that is displayed on startup before any ODBC prompt, which accepts a user account and uses it for any ODBC requirements. Does this make sense? I basically don't want my users to see any ODBC prompts - I just want them to enter username and password into an Access form. Is this possible? Thanks.

Tom

 

[FancyPrairie]

Remove the links to all of your SQL tables. Then relink them. When link dialog box appears (from which you can chose which tables you want to link to), there's a check box on the dialog box that asks if you want to save the password. If you check that box, the users will not be prompted for the password.

 

[dhookom]

FancyPrairie,
Wouldn;t all users then have the permissions and access level of the person that created the links? Also, the name and password would be exposed to all users.

I think you should be able to create a form with text boxes for the two values. Then create an ADO recordset to each of the databases using a connection string based on the values in the text box.

A typical connection string might be

strConn = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" & _
 Me.UserID & ";Password=" & Me.Password & _
 ";Initial Catalog=Northwind;Data Source=swanky"

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]

 

[FancyPrairie]

No, the users would not have the save permissions if you invoke security on your database (which you should). The username and password are stored in the system table MSysOjbects which you could secure so users could not open it.

 

[dhookom]

I didn't recall any mention of security of the database. Without security, they could view the design view of the table and open the properties dialog.

I still think the user would inherit the database permissions of the person who originally linked the tables.

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]

 

[mkov]

When I link my tables to Access from SQL, I use a generic user and password for SQL authentication. That user only has datareader and datawriter access.

Then I secure my front end and use that login to drive the access the user gets in the database.