Linking to a SQL table via ODBC connection string


  • 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

    Wednesday, June 27, 2012 11:56 PM