Connectivity Issues between Access 2003 and SQL Server 2000

[Rick]

First, this is my first attempt at creating an Access front end to an SQL
server for the backend so please be gentle.

I created a database using Microsoft Access 2003 and linked it to an SQL
2000 Server to allow multiple users concurrent access.

Problem: I have some users who can access the database without a problem
but other users, about a third, receive an error, "Can't find file." I am
certain this is a rights issue for the SQL server but I can't figure out why.

Attempted Resolution: I created a .dsn file to pass the SQL database
password allowing users to access to the SQL server without having to know
this password. I've verified cliconfig is set up properly. I've created a
"User Group" to grant permissions to group members.

Nothing has worked and I've heard Microsoft is aware of the issue but I
haven't seen/read any fixes for it. I've also heard there is code you can
write in the Access MDB file to automate connection resolving this issue but
I don't know how to write this code or where to look to find it.

Can anyone help me with this problem or point me to a web site that
addresses this issue?

Thanks

 

[Nikos Yannacopoulos]

Rick,

To begin with, you can make a DSN-less connection to the back end, which
eliminates the need to manually create a DSN on each user's PC (which
is probably your problem).

In a similar situaton I have, I use the main form's Open event to call a
sub which connects to the back end, looping through a table (tblTables)
which stores the names of the tables to be connected. The SQL server
User Name and Password are hardcoded, but as I districute an .mde FE,
the user can't get to them. The sub is:


Sub Connect_To_BE()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim MyServer As String
Dim MyDatabase As String
Dim MyUserName As String
Dim MyPassword As String
MyServer = "The Server Name"
MyDatabase = "The Database Name"
MyUserName = "The User Name"
MyPassword = "The Password"
Set db = CurrentDb
Set rst = db.OpenRecordset("tblTables")
rst.MoveFirst
Do Until rst.EOF
DoCmd.TransferDatabase acLink, "<SQL Database>", _
"ODBC;DRIVER=SQL Server;" _
& "SERVER=" & MyServer & ";" _
& "UID=" & MyUserName & ";" _
& "PWD=" & MyPassword & ";" _
& "DATABASE=" & MyDatabase, _
acTable, rst.Fields(0), rst.Fields(0)
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

Having disabled Access's special keys, the user cannot use F11 to
display the database window (thus gaining access to the tables), or
Ctrl+G to get to a code window and do anything from there. Also, Before
I run the connection sub, I check in code that the database window is
not visible, so as to make sure that the user has not opened the .mde
while holding down the Shift key, so as th get the database window and
then connect to the back end; if the database window is found visible,
trying to open the form kicks them out of the database instead of
connectiong to the back end.

HTH,
Nikos