Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Location
    Myanmar (Burma)
    Posts
    42

    Unhappy Unanswered: How to change 'link path' with VBA?

    hi all GURU

    i linked a dbIV file with access 2000.
    and i read that access file from excel by using VBA.
    i've to change manually when i want to change the name of link dbIV.

    e.g
    current link db is >> d:\plant_1\log.dbf
    new link db is >> d:\plant_3\log.dbf

    *.dbf have same fields but different records.

    so... pls show me any way to change that link from VBA code.

    tks. in advance
    Cyber : The easiest place to make huge mistake.

  2. #2
    Join Date
    Jan 2004
    Location
    Islamabad, Pakistan
    Posts
    97
    Here is the sample code which I use normally to link, unlink and refresh links in my databases...

    you can use these functions with database file path as the parameter for linkdatabase() and refreshlinks()

    eg. LinkDatabase "C:\MyDocuments\mydatabase.mdb"

    **************

    Sub RemoveLinks()
    Dim tdf As TableDef
    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
    Set tdf = Nothing
    End Sub

    Sub LinkDatabase(StrDBPath As String)
    Dim dbs As Database
    Dim tdf As TableDef
    Set dbs = OpenDatabase(StrDBPath)
    For Each tdf In dbs.TableDefs
    If Left(tdf.Name, 4) <> "MSys" Then
    DoCmd.TransferDatabase acLink, "Microsoft Access", Trim(StrDBPath), acTable, tdf.Name, tdf.Name
    SysCmd acSysCmdSetStatus, "Processing table [" & tdf.Name & "]..."
    End If
    Next tdf
    SysCmd acSysCmdClearStatus
    Set dbs = Nothing
    Set tdf = Nothing
    End Sub

    Sub RefreshLinks(StrDBPath As String)
    Dim tdf As TableDef
    For Each tdf In CurrentDb.TableDefs
    If (tdf.Attributes And dbAttachedTable) = dbAttachedTable Then
    tdf.Connect = "; Database = " & StrDBPath
    SysCmd acSysCmdSetStatus, "Processing table [" & tdf.Name & "]..."
    tdf.RefreshLink
    End If
    Next tdf
    Set tdf = Nothing
    SysCmd acSysCmdClearStatus
    End Sub

  3. #3
    Join Date
    Jan 2004
    Location
    Islamabad, Pakistan
    Posts
    97
    The above is for linking access database tables but you can have the idea of doing it...

    Saqib

  4. #4
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122
    Quote Originally Posted by mmlatt
    hi all GURU

    i linked a dbIV file with access 2000.
    and i read that access file from excel by using VBA.
    i've to change manually when i want to change the name of link dbIV.
    You can change the property of anything like this thing.bit.setting = value
    the big question I can't remember the answer to is what the thingy dot something is...

    It's likely to be me.Connection.connectionstring = "foo bar etc" or something like that

    Does that help
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  5. #5
    Join Date
    Jan 2004
    Location
    Myanmar (Burma)
    Posts
    42

    Red face Tks. Sagib and MTH, but still have a difficulty

    Tks. Sagib for ur codes and MTH for ur advice.
    i can't run the code due to Sub or Function not define error.
    VBA don't know SysCmd.
    is there any extra component to use SysCmd command in Excel 2002 VBA.

    and i can't update like following too

    myDb.TableDef("MyTable").connected = "D:\test.dbf"

    tks. u very much

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Saqib's code looks like DAO. try adding an explicit reference to the DAO 3.6 library and change all the declarations for database and tabledef to:
    dim dbs as DAO.database
    dim tdf as DAO.tabledef

    if syscmd wont work for you, just drop all syscmd lines: they are only cosmetic.

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Jan 2004
    Location
    Myanmar (Burma)
    Posts
    42

    Unhappy i got error message when i try to append tdf

    Sub NewLink()

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim StrDBPath As String

    StrDBPath = "dBase IV; DATABASE=D:"

    Set dbs = OpenDatabase("d:\db1.mdb")

    dbs.TableDefs.Delete ("consunti")

    Set tdf = dbs.CreateTableDef("consunti")

    tdf.Connect = StrDBPath
    tdf.SourceTableName = "consunti.DBF"

    ' display error message on following line
    ' Runtime error 3170
    ' Application-defined or object-defined error
    dbs.TableDefs.Append tdf

    dbs.Close

    End Sub

  8. #8
    Join Date
    Jan 2004
    Location
    Islamabad, Pakistan
    Posts
    97
    Here is the Modified sample code which I you can use link, unlink and refresh links for DBase IV tables in your access databases...

    you can use these functions with database file path as the parameter for linkdatabase() and refreshlinks()

    In Access Create a General Module and Copy these in to that module and then you'll be to access these funtions on any form or report...

    eg. LinkDatabase "C:\MyDocuments\mydatabase.mdb"


    I hope this would help...


    **************

    Sub RemoveLinks()
    Dim tdf As TableDef
    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
    Set tdf = Nothing
    End Sub

    Sub LinkDatabase(StrDBPath As String)
    Dim dbs As Database
    Dim tdf As TableDef
    Set dbs = OpenDatabase(StrDBPath)
    For Each tdf In dbs.TableDefs
    If Left(tdf.Name, 4) <> "MSys" Then
    DoCmd.TransferDatabase acLink, "DBase IV", Trim(StrDBPath), acTable, tdf.Name, tdf.Name
    SysCmd acSysCmdSetStatus, "Processing table [" & tdf.Name & "]..."
    End If
    Next tdf
    SysCmd acSysCmdClearStatus
    Set dbs = Nothing
    Set tdf = Nothing
    End Sub

    Sub RefreshLinks(StrDBPath As String)
    Dim tdf As TableDef
    For Each tdf In CurrentDb.TableDefs
    If (tdf.Attributes And dbAttachedTable) = dbAttachedTable Then
    tdf.Connect = "; Database = " & StrDBPath
    SysCmd acSysCmdSetStatus, "Processing table [" & tdf.Name & "]..."
    tdf.RefreshLink
    End If
    Next tdf
    Set tdf = Nothing
    SysCmd acSysCmdClearStatus
    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
  •