SQL Server linked tables

[Dale Fye]

I have an application that I am trying to migrate the BE from Access2k3 to
Sql Server.

When I link the tables using a DSN-less connections string (uses a SQL
Server authentication and password), it works just fine, but when other users
try to use it, they cannot connect, even though they have permissions to the
database.

The code I am using to create the dsn-less connection looks like:

While Not rs.EOF
DoEvents
If TableExists(rs("LocalName"), dbMain.Name) Then
dbMain.Execute "DROP TABLE [" & rs("LocalName") & "]",
dbFailOnError
End If
Set tdf = dbMain.CreateTableDef(rs("LocalName"))
tdf.Connect = fnConn()
tdf.SourceTableName = "dbo." & rs("LocalName")
tdf.Name = rs("LocalName")
If ((tdf.Attributes And dbAttachSavePWD) <> dbAttachSavePWD) Then
tdf.Attributes = tdf.Attributes Or dbAttachSavePWD
End If
dbMain.TableDefs.Append tdf
rs.Edit
rs("ForeignName") = "dbo." & rs("LocalName")
rs("ConnectionString") = ""
rs("DatabaseString") = Mid(tdf.Connect, 11)
rs("Status") = "Complete"
rs.Update
If CodeProject.AllForms("frm_Linked_Table_Manager").IsLoaded Then
Form_frm_Linked_Table_Manager.sub_Table_to_Link.Form.Refresh
End If
Set tdf = Nothing
rs.MoveNext
Wend

Where the recordset is just a list of the tables to relink. and the
connection string looks like:

ODBC;
Driver=SQL Server;
SERVER=xxx.xx.xx.xxx;
DATABASE=databasename;
UID=genericuserid;
PWD=somepassword

Any ideas what I'm missing? How do I persist the password within the
connection string?

 

[Tom Wickerath]

Hi Dale,

Take a look at Access MVP Doug Steele's page on this topic:

Using DSN-Less Connections
http://www.accessmvp.com/djsteele/DSNLessLinks.html


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________