Results 1 to 2 of 2
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Connect to different SQL instance using VB (Access

    I was trying to write a simple dialogue box that connects an Access project to a different SQL Instance.
    I have no problem doing this with linked SQL tables.

    This is to allow swapping between a Main and a Traing copy of the database during development.
    (Yes I could use the normal File Connection screen but I wanted to make my own where more things can be run after the connection is completed).

    I have the correct connection string for the project
    However , if I try to reconnect using the dialog box then I get an error

    Assuming that the valid connection string is stored in a variable called strConnSQL.
    Then I am using the lines below to reconnect

    CurrentProject.Connection = strConn
    CurrentProject.OpenConnection

    However line 1 fails

    If I add the line

    CurrentProject.CloseConnection above these then it still fails on the line that sets the connection string

    The error says that this is not allowed when the Connection is Open

    I even tried putting a DoEvents statement into the sequence but this made no difference at all

    I have tried this on Access 2000 and also Access 2003

    Any ideas?
    Andrew

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Connect to different SQL instance using VB (Ac

    Someone had to reply to this so it better be me!

    There is apparently a BUG (surely not) in Access 2000 that causes this.
    I think it also affects 2003 but I could be wrong there.
    Anyway, the solution appears to be 2 fold.

    1. Trap the error and repeat the closeconnection command
    2. Make sure you only use the equivalent of the BaseConnectionString (don't know why this is but it worked for me)

    Solution example is given below...

    Public Const glb_DataProvider = "SQLOLEDB.1" 'This might need changing but not at the moment

    Function ReLink(frm As Form) As String 'Parameter is '

    Dim strConn As String
    Dim varU, varP, varS, varD
    Dim strUser As String, strPWD As String, strDatabase As String, strServer As String

    On Error GoTo NoConnectError

    varU = frm!ServerUID
    varP = frm!ServerPWD
    varS = frm!ServerName
    varD = frm!fraConnectTo

    If IsNull(varS) Then
    ReLink="Server is not entered"
    Exit Function
    End If

    If IsNull(varD) Then
    ReLink= "Database is not entered"
    Exit Function
    End If

    If IsNull(varU) Then
    ReLink="User ID is not entered"
    Exit Function
    End If

    If IsNull(varP) Then
    ReLink= "Password is not entered"
    Exit Function
    End If


    'Now get real data
    strServer = varS
    strUser = varU
    strPWD = varP

    strDatabase = "DatabaseNameGoesHere"


    'Connect
    'Base Connection String only
    strConn = "PROVIDER=" & glb_DataProvider & ";"
    strConn = strConn & "PASSWORD=" & strPWD & ";"
    strConn = strConn & "PERSIST SECURITY INFO=TRUE;"
    strConn = strConn & "USER ID=" & strUser & ";"
    strConn = strConn & "INITIAL CATALOG=" & strDatabase & ";"
    strConn = strConn & "DATA SOURCE=" & strServer

    'Perform reconnect
    On Error GoTo ConnectionCloseError
    If CurrentProject.IsConnected = True Then Application.CurrentProject.CloseConnection
    DoEvents

    Application.CurrentProject.OpenConnection strConn

    ReLink = ""

    Exit Function

    NoConnectError:
    ReLink = "An early re-link error occurred" & vbLf & Err.Description

    Exit Function

    ConnectionCloseError:
    If Err.Number = 6008 Then
    Resume
    Else
    ReLink = "Could not reconnect to the new server" & vbLf & Err.Description
    Exit Function
    End If

    Exit Function

    End Function

    Now about that advice on adp V mdb's!
    Andrew

Posting Permissions

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