Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2011
    Posts
    5

    Unanswered: Bypass linked table credentials popup?

    Hi,

    This code, which I picked up on the 'net somewhere, and am using in an MS Access FE, works great for creating a local copy of a SQL Server table. Only problem is the first time it does so, it pops up the SQL Server login, and I have to choose "use trusted connection". How can I bypass that?
    Public Sub CreateLocalSQLTable(strTable As String, strSQL As String, strPKField As String)
    Dim qdf As DAO.QueryDef
    Dim strQuery As String
    Dim db As Database
    Dim SQL As String

    strQuery = "qryTemp"
    DoCmd.Close acTable, strTable
    Call OpenGConn
    On Error Resume Next
    DoCmd.DeleteObject acQuery, strQuery
    DoCmd.DeleteObject acTable, strTable
    On Error GoTo ErrorHandler

    Set db = CurrentDb
    Set qdf = CurrentDb.CreateQueryDef(strQuery)
    With qdf
    .Connect = sConnODBC
    .SQL = strSQL
    .Close
    End With
    strSQL = "Select * INTO " & strTable & " FROM " & strQuery
    CurrentDb.Execute strSQL
    DoCmd.DeleteObject acQuery, strQuery
    db.Close
    End Sub

    Public Const sConn = "DRIVER=SQL Server Native Client 10.0;SERVER=WDC;Trusted_Connection=yes;DATABASE=IP OP_BE"

    Public Function OpenGConn() As ADODB.Connection
    Set GConn = New ADODB.Connection
    GConn.ConnectionString = sConn
    GConn.Open
    ' Find out if the attempt to connect worked.
    If GConn.State = adStateOpen Then
    '
    Else
    MsgBox "Error opening connection OpenGConn"
    End If

    End Function

  2. #2
    Join Date
    Aug 2011
    Posts
    5
    Quote Originally Posted by Pat Phelan View Post
    Modify OpenGConn to explicitly use a trusted connection.

    -PatP
    Thanks, but this is the connection string. What else can I do?

    Public Const sConn = "DRIVER=SQL Server Native Client 10.0;SERVER=WDC;Trusted_Connection=yes;DATABASE=IP OP_BE"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •