[AccessD] SQL ODBC - create table connection

I use this site for reference for connection strings:
http://www.connectionstrings.com/


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dale Kalsow
Sent: Thursday, May 19, 2005 3:34 PM
To: Access Developers discussion and problem solving
Subject: [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!
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com