Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2003
    Posts
    14

    Unanswered: Linking a table in another DB

    Can anyone tell me the VBA code to link a table in another database (in the same folder as CurrentDB). I know the name of the database and the name of the table. I just want it to link automatically, without putting up any dialog boxes.

    Thanks.
    B.

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465

    Re: Linking a table in another DB

    Originally posted by BMac
    Can anyone tell me the VBA code to link a table in another database (in the same folder as CurrentDB). I know the name of the database and the name of the table. I just want it to link automatically, without putting up any dialog boxes.

    Thanks.
    B.
    Do something like this:
    Dim db As Database, RS As Recordset

    Set db = CurrentDb
    ' Open the table for editing.

    Set RS = db.OpenRecordset("Tbl Vendedores", dbOpenDynaset)
    Saludos
    Norberto

  3. #3
    Join Date
    Nov 2003
    Posts
    14
    Hang on: I don't want a recordset. I want to link a table that is in a different database to my CurrentDB.

    My first guess is that it might use the following code:
    DoCmd.RunCommand acCmdLinkTables

    But I can't find the syntax to use that.

    The reason for wanting to do this is that I want to take data from this remote table and use it to update a table in my currentDB. So alternatively, if someone can tell me how to do an UPDATE query using a table in a different DB, that would be at least as good.

    Cheers.
    B.

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    In the menu file, external data , find your base and table and link
    Saludos
    Norberto

  5. #5
    Join Date
    Nov 2003
    Posts
    14
    Sorry, I didn't make it clear: I need to do this using VBA.
    Does anyone know the code to link to a table in an external database using VBA?

    Cheers,
    B.

  6. #6
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Sorry now i understand you:

    El siguiente ejemplo vincula la tabla de la base de datos ODBC Autores a la base de datos activa:

    DoCmd.TransferDatabase acLink, "Base de datos ODBC", _
    "ODBC;DSN=DatosOrigen1;UID=Usuario2;PWD=www;LANGUA GE=us_english;" _
    & "DATABASE=pubs", acTable, "Autores", "dboAuthors"
    Saludos
    Norberto

  7. #7
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb

    Originally posted by BMac
    Sorry, I didn't make it clear: I need to do this using VBA.
    Does anyone know the code to link to a table in an external database using VBA?

    Cheers,
    B.
    Linking a table form another ACCESS DB
    DoCmd.TransferDatabase acLink, "Microsoft Access", _
    txt_LinkPath & "\" & txt_Link_dbName, acTable, _
    txt_Link_SourceTableName, _
    txt_Link_DesinationTableName

    Otherwise use this of Norberto to link a table through ODBC

  8. #8
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Linking a table in another DB

    Originally posted by BMac
    Can anyone tell me the VBA code to link a table in another database (in the same folder as CurrentDB). I know the name of the database and the name of the table. I just want it to link automatically, without putting up any dialog boxes.

    Thanks.
    B.


    For further clarifaction of Noberto's post here is part of the help topic associated with the TransferDatabase method.

    TransferDatabase Method
    See Also Applies To Example Specifics
    The TransferDatabase method carries out the TransferDatabase action in Visual Basic.

    expression.TransferDatabase(TransferType, DatabaseType, DatabaseName, ObjectType, Source, Destination, StructureOnly, StoreLogin)

    expression Required. An expression that returns one of the objects in the Applies To list.

    TransferType Optional AcDataTransferType.

    AcDataTransferType can be one of these AcDataTransferType constants.
    acExport
    acImport default
    acLink
    If you leave this argument blank, the default constant (acImport) is assumed.

    Note The acLink transfer type is not supported for Microsoft Access projects (.adp).


    DatabaseType Optional Variant. A string expression that's the name of one of the types of databases you can use to import, export, or link data.

    Types of databases:
    Microsoft Access (default)
    Jet 2.x
    Jet 3.x
    dBase III
    dBase IV
    dBase 5.0
    Paradox 3.x
    Paradox 4.x
    Paradox 5.x
    Paradox 7.x
    ODBC Databases
    In the Macro window, you can view the database types in the list for the Database Type action argument of the TransferDatabase action.



    DatabaseName Optional Variant. A string expression that's the full name, including the path, of the database you want to use to import, export, or link data.

    ObjectType Optional AcObjectType.

    AcObjectType can be one of these AcObjectType constants.
    acDataAccessPage
    acDefault
    acDiagram
    acForm
    acFunction
    acMacro
    acModule
    acQuery
    acReport
    acServerView
    acStoredProcedure
    acTable default
    This is the type of object whose data you want to import, export, or link. You can specify an object other than acTable only if you are importing or exporting data between two Microsoft Access databases. If you are exporting the results of a Microsoft Access select query to another type of database, specify acTable for this argument.

    If you leave this argument blank, the default constant (acTable) is assumed.

    Note The constant acDefault, which appears in the Auto List Members list for this argument, is invalid for this argument. You must choose one of the constants listed above.


    Source Optional Variant. A string expression that's the name of the object whose data you want to import, export, or link.

    Destination Optional Variant. A string expression that's the name of the imported, exported, or linked object in the destination database.

    StructureOnly Optional Variant. Use True (1) to import or export only the structure of a database table. Use False (0) to import or export the structure of the table and its data. If you leave this argument blank, the default (False) is assumed.

    StoreLogin Optional Variant. Use True to store the login identification (ID) and password for an ODBC database in the connection string for a linked table from the database. If you do this, you don't have to log in each time you open the table. Use False if you don't want to store the login ID and password. If you leave this argument blank, the default (False) is assumed. This argument is available only in Visual Basic.

    Gregg

  9. #9
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    where to put the code?

    Originally posted by Norberto
    Sorry now i understand you:

    El siguiente ejemplo vincula la tabla de la base de datos ODBC Autores a la base de datos activa:

    DoCmd.TransferDatabase acLink, "Base de datos ODBC", _
    "ODBC;DSN=DatosOrigen1;UID=Usuario2;PWD=www;LANGUA GE=us_english;" _
    & "DATABASE=pubs", acTable, "Autores", "dboAuthors"
    Where exactly (module...) should I put such a code to link each table in a front-end db to the back-end?

    And: Does this kind of linking mean that a new drive letter "revealing" the source appears in MyComputer like it does when using the system tool to set ODBC links?
    I am looking for a method to link to another computer without revealing where the original db resides.

  10. #10
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Re: where to put the code?

    Ah, that method uses an existing connection that already means revealing the source by assigning a drive letter, I assume. But with this code:

    "Linking a table form another ACCESS DB:
    DoCmd.TransferDatabase acLink, "Microsoft Access", _
    txt_LinkPath & "\" & txt_Link_dbName, acTable, _
    txt_Link_SourceTableName, _
    txt_Link_DesinationTableName"

    from hambakka, would it be possible to use \\workstationname\share$ etc. without assigning a drive letter while the connection is established?

  11. #11
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Re: where to put the code?

    Originally posted by kedaniel
    Ah, that method uses an existing connection that already means revealing the source by assigning a drive letter, I assume. But with this code:

    "Linking a table form another ACCESS DB:
    DoCmd.TransferDatabase acLink, "Microsoft Access", _
    txt_LinkPath & "\" & txt_Link_dbName, acTable, _
    txt_Link_SourceTableName, _
    txt_Link_DesinationTableName"

    from hambakka, would it be possible to use \\workstationname\share$ etc. without assigning a drive letter while the connection is established?
    I think you can do that, as this shared folder is in your network. Just try

Posting Permissions

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