sql server odbc: Linking Access 2003 to SQL 2005 -- sql server odbc

Re: Linking Access 2003 to SQL 2005

BackthePack
9/20/2007 2:16:03 PM

Thank you Mary. Any tips or can you steer me to a decent article as to how
to go about using an ODBC connection string at runtime?

Jim

[quoted text, click to view]

Re: Linking Access 2003 to SQL 2005

Mary Chipman [MSFT]
9/20/2007 5:01:16 PM

(1) Do not ever use the sa login and password for anything. Create a
separate login and grant it only the permissions needed. If you've
gone to all the trouble to move the data to SQL Server, then you
probably don't want to jeopardize it by putting the entire server at
the mercy of ignorant (or malicious) users.

(2) Do not use a DSN. DSNs are a pain to create and adminster in
addition to being a security vulnerability. Connect using an ODBC
connection string at runtime instead. If you are using linked tables,
drop the links and recreate them using this connection string. You can
do this using VBA/DAO code that sets properties of TableDef objects
(you can also manipulate DAO.QueryDef objects programmatically).

-Mary

On Wed, 19 Sep 2007 13:24:00 -0700, BackthePack
[quoted text, click to view]

Re: Linking Access 2003 to SQL 2005

Mary Chipman [MSFT]
9/21/2007 12:00:00 AM

You can get the syntax from connectionstrings.com. If you set up
security using SQL logins (instead of Windows logins) then you need to
create a login form that lets the user fill in their name and
password. Here's a code sample to get you started -- you'll need to
modify this to accept input parameters for the login and password if
you don't use integrated security (which is recommended).

Public Sub LinkODBConnectionString()
Dim strConnection As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef

Set db = CurrentDb

' Specify the driver, the server, and the connection
strConnection = "ODBC;Driver={SQL Server};" & _
"Server=(local);Database=SqlDbName;Trusted_Connection=Yes"

' Specifying a SQLS user/password instead of integrated security
' strConnection = "ODBC;Driver={SQL Server};" & _
' "Server=(Local);Database=SqlDbName;UID=UserName;PWD=password"

' Create Linked Table. The LinkedTableName and the
' ServerTableName can be the same.
Set tdf = db.CreateTableDef("LinkedTableName")
tdf.Connect = strConnection
tdf.SourceTableName = "ServerTableName"
db.TableDefs.Append tdf

Set tdf = Nothing
End Sub

On Thu, 20 Sep 2007 14:16:03 -0700, BackthePack
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server odbc group.