Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Unanswered: Link table to other database

    When starting my DB I want to make links to tables in other databases.
    Therefor I used following code :

    Code:
    Private Sub LinkTableToExternalDB(databaseName As String, tableName As String)
    
            DoCmd.TransferDatabase acLink, _
                           "Microsoft Access", _
                           databaseName, _
                           acTable, _
                           tableName, _
                           tableName, _
                           False, _
                           True
        End If
    This works fine except for one database which is password-protected.
    So I was thinking of adding the password as a parameter like this :

    Code:
    Private Sub LinkTableToExternalDB(databaseName As String, tableName As String, Optional password As String)
    But I have no idea how to insert this password in de Docmd-statement.
    Last edited by artemide; 02-10-04 at 07:00.

  2. #2
    Join Date
    Jun 2003
    Location
    UK
    Posts
    68
    This may be a sttrange answer but why do you link the tables at opening and not have them permanantly linked ??

    Paul

  3. #3
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    Originally posted by Funkster
    This may be a sttrange answer but why do you link the tables at opening and not have them permanantly linked ??

    Paul
    Because my database retrieves data from third party databases.
    When their databases are moved to an other location I have to modify all the links.
    Now I use an ini-file containing all the locations.
    If a database is moved I only have to modify the ini-file.

  4. #4
    Join Date
    Jun 2003
    Location
    UK
    Posts
    68
    I knew there'd be a good reason. Try this:

    Set db = DBEngine.OpenDatabase("???\???.mdb", False, False, ";pwd=??????")

    'Connect to database and add a link to the Front End

    ConnectOutput CurrentDb, "TableName", ";DATABASE="DatabaseName", "TableName"

    RefreshDatabaseWindow

    Hope this helps

    Paul

  5. #5
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    Originally posted by Funkster
    I knew there'd be a good reason. Try this:

    Set db = DBEngine.OpenDatabase("???\???.mdb", False, False, ";pwd=??????")

    'Connect to database and add a link to the Front End

    ConnectOutput CurrentDb, "TableName", ";DATABASE="DatabaseName", "TableName"

    RefreshDatabaseWindow

    Hope this helps

    Paul
    I get an error on the ConnectOutput -statement.
    When I search the helpfiles for it I can't find anything.
    Do I have to make a reference for using this?

  6. #6
    Join Date
    Jun 2003
    Location
    UK
    Posts
    68
    Are you using ADO or DAO ??

    This code is running under DAO 3.6 with no references to ADO so I have no need to fully qualify the expressions.

    Paul

  7. #7
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    Originally posted by Funkster
    Are you using ADO or DAO ??

    This code is running under DAO 3.6 with no references to ADO so I have no need to fully qualify the expressions.

    Paul
    I have a reference to ADO 3.6 and declared db as DAO.Database

  8. #8
    Join Date
    Jun 2003
    Location
    UK
    Posts
    68
    If possible could you post the code you have created so that I can have a quick look ?

  9. #9
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    Originally posted by Funkster
    If possible could you post the code you have created so that I can have a quick look ?
    This is how I wanted to do it.


    Code:
    private sub test()
        call LinkTableToExternalDB("C:\myDB.mdb", "tblMyTable", "pass")
    end sub
    
    
    
    Private Sub LinkTableToExternalDB(databaseName As String, tableName As String, Optional password As String)
    
        If password = "" Then
            'This works fine because no password available
            DoCmd.TransferDatabase acLink, _
                           "Microsoft Access", _
                           databaseName, _
                           acTable, _
                           tableName, _
                           tableName, _
                           False, _
                           True
        Else
            DoCmd.TransferDatabase acLink, _
                           "Microsoft Access", _
                           ("DATABASE = " & databaseName & ";PWD=" & password), _
                           acTable, _
                           tableName, _
                           tableName, _
                           False, _
                           True
        End If   
    
    End Sub


    This is your suggestion :

    Code:
    Private Sub LinkTableToExternalDB(databaseName As String, tableName As String, Optional password As String)
        Dim db As dao.Database
        
        If password = "" Then
            DoCmd.TransferDatabase acLink, _
                           "Microsoft Access", _
                           databaseName, _
                           acTable, _
                           tableName, _
                           tableName, _
                           False, _
                           True
        Else
            Set db = DBEngine.OpenDatabase(databaseName, False, False, ";pwd=" & password)
    
            'Connect to database and add a link to the Front End
            ConnectOutput CurrentDb, tableName, ";DATABASE=" & databaseName, tableName
    
            RefreshDatabaseWindow
    
    End If
        
    End Sub

  10. #10
    Join Date
    Jun 2003
    Location
    UK
    Posts
    68
    Give me a moment to run your code: Here's a link to an example of the code:

    http://msdn.microsoft.com/library/de...tablenamex.asp

    Paul

  11. #11
    Join Date
    Jun 2003
    Location
    UK
    Posts
    68
    What error are you getting?

  12. #12
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    Originally posted by Funkster
    What error are you getting?
    It said the subroutine was unknown, but I took a look at your link and that's quite normal because ConnectOutput seems to be a userdefined routine.

  13. #13
    Join Date
    Jun 2003
    Location
    UK
    Posts
    68
    Oops it was a long time ago that I did this. Yes you do need to add the ConnectOutput function. I hope this solves the problem.

    Paul

  14. #14
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    Originally posted by Funkster
    Oops it was a long time ago that I did this. Yes you do need to add the ConnectOutput function. I hope this solves the problem.

    Paul
    I will study this carefully and try to implement it in my code.
    If I don't get it done, I'll be back.
    Many thanks for your support.

  15. #15
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    OK, I'm getting close, but didn't reach the finish yet.
    Following code works fine with a password-protected database.
    Then I tried to connect to a non-pasword-protected DB, but I get an error which says : "Invalid argument"

    Code:
    Private Sub Test()
    connection = ";DATABASE=C:\MyDB.mdb;PWD="
    
    Call ConnectOutput(connection, "tblMyTable")
    End Sub
    
    
    Private Sub ConnectOutput(connection As String, tableName As String)
        Dim linkedTable As TableDef
        
        CurrentDb.TableDefs.Delete tableName
        
        Set linkedTable = CurrentDb.CreateTableDef(tableName)
        
        linkedTable.Connect = connection
        linkedTable.SourceTableName = tableName
        
        CurrentDb.TableDefs.Append linkedTable
    End Sub
    Any suggestion?
    Thanks

Posting Permissions

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