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?

-- 

email address is invalid
Please reply to newsgroup only.

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
__________________________________________

"Dale Fye" wrote:

> 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?
> 
> -- 
> 
> email address is invalid
> Please reply to newsgroup only.