By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,578 Members | 825 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,578 IT Pros & Developers. It's quick & easy.

Create DSN-Less connection to SQL 2005 with DAO

P: n/a
Hello,

Is it possible to create a DSN-less connection to a table in SQL
Server 2005 using DAO in Access 2003? I tried what I thought was
pretty standard code but every time I run it it still prompts me for a
DSN:

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnect As String

' note - username and password below are changed...
strConnect = "ODBC" & _
";DRIVER = SQL Server" & _
";SERVER = PEDSQL" & _
";DATABASE = CATI" & _
";UID = username" & _
";PWD = password;"

Set db = CurrentDb
Set tdf = db.CreateTableDef("AddressResponse")
tdf.SourceTableName = "AddressResponse"
tdf.Attributes = DB_ATTACHSAVEPWD
tdf.Connect = strConnect

db.TableDefs.Append tdf
db.TableDefs.Refresh

Set tdf = Nothing
Set db = Nothing

When I step through this code in the debugger I get prompted for a DSN
at the 'db.TableDefs.Append tdf' line. If I go ahead and select a DSN
here I am still prompted for user ID and password. If I enter the
user ID and password just as I specified them in strConnect it will go
ahead and create the link. Is there a way to create the link 'from
scratch' without prompting for a DSN? Any help greatly appreciated.
Thanks!

Bruce

Jul 16 '07 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.