Question DNS Question

[IndigoStickman]

Hi all (not sure if I'm in the right place),

I'm putting the finishing touches to an Access database, and part of the design relies on linking to a number of tables within an external SQL database.
We've set up the DNS and entered the password etc, and have even checked the "remember password" box, yet when running and/or testing the database itself we are prompted for the SQL password when we reach the first form that will use the data within the external tables.

As this will be a multi-user database, we don't want to be giving access to the SQL portion to everyone i.e. the password, but I can't find anything anywhere on how to have the password already set and thus the login screen for the externally linked tables NOT show.

Can anyone help? I can explain further if this isn't clear enough.

 

[DavidAtWork]

I think to deal with this effectively, you'll have to create some sort of access level hierarchy. Do the users have any form of login to this database.
Assuming you have for example admin users and standard users, you write something in that will check the access level and if admin user, run a vba sub that will connect the SQL tables using this type of connection (doesn't require an ODBC DSN setting up)

Sub reLinkSQLtables()
 
Dim db As Database
Dim Rs As Recordset
Dim strConnectionString, strServer, strDatabase, strUID, strPWD As String
Dim sqlStr, varLinkedTableName As String
'assign logon details to variables used in the connection string
strServer = "yourServer"
strDatabase = "yourDatabase"
strUID = "yourUserName"
strPWD = "yourPassword"
Set db = CurrentDb()
strConnectionString = "ODBC;Driver={SQL Server};" & _
"Server=" & strServer & ";" & _
"Database=" & strDatabase & ";" & _
"Uid=" & strUID & ";" & _
"Pwd=" & strPWD
 
sqlStr = "SELECT * FROM [SQLtablesToLink]"
Set Rs = db.OpenRecordset(sqlStr)
Do While Not Rs.EOF
    varLinkedTableName = Rs.Fields(0)
    DoCmd.TransferDatabase acLink, "ODBC Database", _
                strConnectionString, acTable, varLinkedTableName, varLinkedTableName
    Rs.MoveNext
Loop
Set Rs= Nothing
Set db= Nothing
 
End Sub

All you require is a simple table with one field with a record for each SQL table you want to link. Set up a generic user in SQL Server, using SQL authenication and password, assign public and sysadmin roles and map it to your SQL database.
All you need to do is check the user's access level and if admin, call this sub
David

 

[Galaxiom]

DNS = Domain Name Server (nothing to do with your database)

DSN = Data Source Name

 

[IndigoStickman]

Thanks David, I'll use that as I'm the only one to be the admin user.

And thanks Galaxiom, I did mean DSN, doh.

 

[jdraw]

Also from David reLinkSQLTables function

.....
Dim strConnectionString, strServer, strDatabase, strUID, strPWD As String
Dim sqlStr, varLinkedTableName As String
...

does not do what you may think.
I see this syntax quite often an d the poster implies that

Dim strConnectionString, strServer, strDatabase, strUID, strPWD As String

will make these variables as type String. Not so.

Access/vba requires you to be explicit.

Dim strConnectionString as String, strServer as String, strDatabase as String, strUID as String, strPWD As String

or

Dim strConnectionString as String
Dim strServer as String
Dim strDatabase as String
Dim  strUID as String
Dim strPWD As String

In the case

.....
Dim strConnectionString, strServer, strDatabase, strUID, strPWD As String
Dim sqlStr, varLinkedTableName As String
...

strPWD and varLinkedTableName will be String but
strConnectionString, strServer, strDatabase, strUID and sqlStr
will be Variants.

Perhaps a minor point, but for clarity........