I've created an Access database which uses tables linked from a SQL
Server app - it prints out customised labels, which the SQL Server
program won't do. It all works fine, except that when you start the
Access database and try to use it (in other words as soon as you try
to query the SQL Server data),  you get this login box:

<img src="http://www.rainjam.com/images/sql_login_fail.jpg">

Pressing Enter gets you to this one:

<img src="http://www.rainjam.com/images/login.jpg">

This is going to be used by non-technical people, so it needs to have
as few hurdles as possible. And as it's on an internal system, I'm
assuming that anyone who can run the Access database has the rights to
look at the data in the SQL Server app. In other words I need some
code for the OnLoad event of the form which will pass the server,
database, user & password details to SQL Server, so users aren't
bothered by this login box. I've tried

Set objConn = New ADODB.Connection
objConn.ConnectionString = "Driver={SQL
Server};Server=[server];Database=[db];Uid=[blah];Pwd=[blah];"
objConn.Open

... which didn't work. Neither did the code <a
href="http://www.access-programmers.co.uk/forums/showthread.php?t=50449">here</a>,
for some reason.

Any ideas? I'm sure it should be fairly simple...?

---

Clarification of Question by nickhill78-ga on 29 Nov 2005 08:33 PST

I've also tried creating a File DSN on each machine it'll be installed
on, with these contents:

[ODBC]
DRIVER=SQL Server
UID=sa
Pwd= [password]
Trusted_Connection=Yes  [I also tried No]
DATABASE= [database name]
WSID= [machine name]
APP=Microsoft Open Database Connectivity
SERVER=ROSESERVER01

... and this didn't work either, despite being the one I used when
linking the tables in Access. Creating a System DSN didn't seem to
work either.

DoCmd.DeleteObject acTable, "Email"
DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=dsn
name;UID=sa;PWD=yourpass;DATABASE=thedatabasename", acTable,
"dbo.tablename", "nameof table in access", , True

you will need to create a dsn.

Do you mean I'll need to relink the tables having created a dsn?
Should I create a system or file dsn?

Thanks for the help

Nick

System DSN

I've now created a system DSN and relinked the tables using it. It's
still asking for a login though - any way of stopping the login box
from appearing (without using SendKeys which seems a little messy)?

Use the above code. To creat the links the true on the end of the
function is the choice to save the password. Make sure
UID=sa;PWD=yourpass are set correctly. UID is the user ID may or may
not be sa.