Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

How to modify TableDef's Connect ppty in DAO/VBA?

Hi all,

I created a DAO database (.mdb) which uses some linked tables. These are all in an other .mdb database, it's name referred below as toMDBName. Depending on users' environment, from users' aspect this .mdb may be on different drives, in different folders etc. Therefore I want to gain possibility changing linked tables' Connect property, from VB code. For this I wrote a function, named below as SetLinkedTables. It seems to work properly - except that [b]after the statement "Set DB = Nothing" all the assigned newly Connect values are lost and appear the old ones ...[/b]

Breaking in line "Next" and watching the values, all OK. If creating a second "For Each" loop WITHOUT prior "Set DB = Nothing" and watching the values in this loop, they are still OK. None VB error messages. If create a third .mdb and insert the function into that, with line modified to format 'Set DB = OpenDataBase ("xxxx")' (i.e. the modifyings aren't for the own, rather for an outer .mdb), nothing is changes.

[b]How making the modifyings permanent??[/b]

Thanks in advance.

[code]
Private Function SetLinkedTables(toMDBName As String) As Boolean
Dim DB As Database
Dim TD As TableDef
Dim Cnct As String

On Error GoTo SLT_Err

Cnct = ";DATABASE=" & toMDBName

SetLinkedTables = False

Set DB = CurrentDb

For Each TD In DB.TableDefs
If TD.SourceTableName <> "" Then _
TD.Connect = Cnct
Next

Set DB = Nothing

SetLinkedTables = True

SLT_Exit:
Exit Function

SLT_Err:
MsgBox Err.Description
Resume SLT_Exit

End Function 'SetLinkedTables
[/code]

Comments

  • gyapeszgyapesz Member Posts: 19
    : Hi all,
    :
    : [code]
    : Private Function SetLinkedTables(toMDBName As String) As Boolean
    : Dim DB As Database
    : Dim TD As TableDef
    : Dim Cnct As String
    :
    : On Error GoTo SLT_Err
    :
    : Cnct = ";DATABASE=" & toMDBName
    :
    : SetLinkedTables = False
    :
    : Set DB = CurrentDb
    :
    : For Each TD In DB.TableDefs
    : If TD.SourceTableName <> "" Then
    : TD.Connect = Cnct
    : [b]TD.RefreshLink[/b]
    : End If
    : Next
    :
    : Set DB = Nothing
    :
    : SetLinkedTables = True
    :
    : SLT_Exit:
    : Exit Function
    :
    : SLT_Err:
    : MsgBox Err.Description
    : Resume SLT_Exit
    :
    : End Function 'SetLinkedTables
    : [/code]
    :
    :

    I found the solution - the line in bold was needed.


  • writetoashishpwritetoashishp Member Posts: 1
    The code i have written in modules section of access mdb, and when i execute the code in "immediate " window or call the function from outside, it does not work, and gives error (when executed in Immediate window),
    " Compile error: Argument not optional" error.
Sign In or Register to comment.