Linking to a SQL table via ODBC connection string

Question

I'm attempting to set up a tabledef to link to a SQL Server 2005 table. I'm using SQL Native Client driver. I must use SQL Server authentication rather than Windows authentication. I have the SQL userid and password and they work. But when I set up the connection in code, I get a Login popup with my Windows id pre-populataed and asking for my Windows password. How do I force the connection to use SQL authentication? I have researched online for hours and can find no definitive answer. There seem to be so many different parameters for userid and password, UID=, USERID=, USER ID=, PASSWORD=, PWD= etc. etc. I don't know which is right or even if that is the problem.

Here is the last of many versions I have tried:

Set tdf = db.CreateTableDef
   With tdf
      .Name = "tblEmployees"
      .Connect = "ODBC;Driver={SQL Native Client};Server=SVSQLPRD02\INSTANCE1;Database=AHCCCSPhoneDirectory;" & _
                 "user id=AHCCCSPhoneDirectory;pwd=AHCCCSPhoneDirectory"
      .SourceTableName = "dbo.Employees"
     
   End With
   db.TableDefs.Append tdf

Answer 1

I'd start by looking and using the string found at http://www.connectionstrings.com/sql-server-2005

Also ensure you are using the right ODBC drivers for your version (SQL Server 2005) and that your are using the appropriate connection string for that specific version.

---

Daniel Pineault, 2010 Microsoft MVP
http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net