Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    15

    Unanswered: link remote tables or create connection in code

    Hi I am writing an access database that relies heavily on ado.recordset.

    I was wondering whether people think it is better to create a link to a remote table in access and then use currentProject.connection as the connection type for the recordset,

    or

    create an adodb.connection in code like this:
    Code:
    'creates a new adodb connection to the mainframe tables via odbc
    Public Function createADODBConnection() As ADODB.Connection
        On Error GoTo errorhandler:
        Set createADODBConnection = New ADODB.Connection
        
        createADODBConnection.ConnectionString = "ODBC;driver={IBM DB2 ODBC DRIVER};DSN=etc etc
        createADODBConnection.Provider = "MSDASQL"
        createADODBConnection.CursorLocation = adUseClient
        createADODBConnection.Open
        Exit Function
    
    errorhandler:
        MsgBox "An error has occured: " & Err.description
        Set createADODBConnection = Nothing
    End Function
    which do people think is best?

  2. #2
    Join Date
    Apr 2004
    Posts
    130
    I use the following:

    Dim dbs As Database
    Dim tdf As TableDef
    Dim strDBNewPath as String

    ' Remove All Links
    For Each tdf In CurrentDb.TableDefs
    If Left(tdf.Name, 4) <> "MSys" And (tdf.Attributes And dbAttachedTable) = dbAttachedTable Then
    CurrentDb.TableDefs.Delete tdf.Name
    End If
    Next tdf

    ' Refresh all Links
    Set dbs = OpenDatabase(strDBNewPath)
    For Each tdf In dbs.TableDefs
    If Left(tdf.Name, 4) <> "MSys" Then
    DoCmd.TransferDatabase acLink, "Microsoft Access", Trim(strDBNewPath), acTable, tdf.Name, tdf.Name
    End If
    Next tdf


    Works like a charm.

    Winston

  3. #3
    Join Date
    Apr 2004
    Posts
    15
    thats great winston but what the hell has it got to do with my question?????

  4. #4
    Join Date
    Apr 2004
    Posts
    130
    I misread your note. Thought you wanted to relink tables.
    Apologies.
    Winston

  5. #5
    Join Date
    Apr 2004
    Posts
    15
    not a problem, thanks for reading it ;-)

  6. #6
    Join Date
    May 2004
    Location
    Moscow, Russia
    Posts
    29
    each call of this connection has been to create new connection

    It is not good because everyone connection demands resources of system

    better to declare a public variable of type connection and to check if that is not connected to start function of re-connection

    Code:
    Public ADODBConnection As New ADODB.Connection
    
    Public Sub createADODBConnection()
        On Error GoTo errorhandler:
        If ADODBConnection.State <> adStateClosed Then Exit Sub
        ADODBConnection.ConnectionString = "ODBC;driver={IBM DB2 ODBC DRIVER};DSN=etc etc
        ADODBConnection.Provider = "MSDASQL"
       ADODBConnection.CursorLocation = adUseClient
        ADODBConnection.Open
        Exit Sub
    
    errorhandler:
        MsgBox "An error has occured: " & Err.Description
        Set ADODBConnection = Nothing
    End Sub
    
    'usage
    Public Sub testconn()
     Call createADODBConnection
     ADODBConnection.Execute sqlscript
    End Sub

Posting Permissions

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