[AccessD] SQL ODBC - create table connection

Good Afternoon,

 

I have a sql 2003 database that is running mixed mode.  I am trying to attach to and open a sql table from Access 2003.  I am using the following code.  It works fine when I attach the table but when I try to open it it says the user is not valid for a trusted connection and then it gives me a box that I can uncheck the trusted and then type in a user account and password that will then give me access to the data.  Does anyone know how to create a connection that will allow me to attach the table and open it with out getting the error and having to do the 2nd step.  Thanks!

 

Option Compare Database

Option Explicit

 

 

Public Function LinkUserTable()

'*****************************************************************************

'// If the USERS table is not already linked, create a link.

'//

'// NOTE - The USERS table must have a primary key in the SQL database in order

'//        for it to be updateable in Access.

'//

'*****************************************************************************

Dim sTest As String

Dim sConn As String

Dim fCreate As Boolean

 

Dim TBL As New Table

Dim CAT As New Catalog

 

  On Error GoTo GetUsers_Err

 

  sConn = "ODBC;Driver={SQL Server};Server=10.30.31.56;Database=SQL_HUDSON;Uid=oper;Pwd=oper;"

 

  '// Open the recordset from db

  CAT.ActiveConnection = CurrentProject.Connection

 

  sTest = CAT.Tables("Pallet_Sequence").Name

  

  If fCreate Then

        

    TBL.Name = "Pallet_Sequence"

    Set TBL.ParentCatalog = CAT

    

    

    TBL.Properties("Jet OLEDB:Create Link") = True

    TBL.Properties("Jet OLEDB:Link Provider String") = sConn

    TBL.Properties("Jet OLEDB:Remote Table Name") = "Pallet_Sequence"

    

    CAT.Tables.Append TBL

    TBL.Properties("Jet OLEDB:Link Provider String") = sConn    '// This has to be performed due to a bug with ADOX

    CAT.Tables.Refresh

   

    Set CAT = Nothing

    

  End If

  

Exit Function

GetUsers_Err:

  If err.Number = 3265 Then

    fCreate = True

    Resume Next

  Else

    MsgBox "Error Number:  " & err.Number & vbCrLf & _

      "Error Description:  " & err.Description & vbCrLf & _

      "Error Location:  basUsers.GetUsers", vbInformation

  End If

End Function

 

 

 


		
---------------------------------
Discover Yahoo!
 Have fun online with music videos, cool games, IM & more. Check it out!