Create DSN with UID and PWD

[mcnews]

i am trying to modify some VBA code that create a DSN that use trusted
connection to accept a UID and PWD instead:

key = "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\"
''''''''
sh.RegWrite key & "TestInventory\", ""
sh.RegWrite key & "TestInventory\Database", "TEST_DEV"
sh.RegWrite key & "TestInventory\Driver",
"C:\WINNT\System32\SQLSRV32.dll"
sh.RegWrite key & "TestInventory\Server", "SQI-CDCDV1\QSRV1"
'''sh.RegWrite key & "UserInventory\Trusted_Connection", "Yes"
sh.RegWrite key & "TestInventory\UID", "InventAdmin"
sh.RegWrite key & "TestInventory\Pwd", "InventAdmin"
sh.RegWrite key & "ODBC Data Sources\TestFreezer", "SQL Server"
Set dbs = CurrentDb()
DoCmd.DeleteObject acTable, "viewTEST"
Set tdf = dbs.CreateTableDef("viewTEST", dbAttachSavePWD, _
"viewTEST", _
"ODBC;DSN=TestFreezer;UID=InventAdmin;PWD=InventAdmin" & _
";APP=Microsoft Office
2003;WSID=CCID-XDV-CITRIX;DATABASE=TEST_DEV;TABLE=dbo.viewTEST")

it seems to work on my PC but i have to manually create the DSN before
it works on other PCs.

?

 

[Douglas J Steele]

Why not forget about the DSN altogether, and use a DSN-less connection.

I show one way to do this at
http://www.accessmvp.com/djsteele/DSNLessLinks.html

Check Carl Prothman's site
http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForSQLServer for
the appropriate connection string to use.