Results 1 to 6 of 6

Thread: Link table

  1. #1
    Join Date
    Nov 2002
    Location
    Czech republic
    Posts
    3

    Arrow Unanswered: Link table

    Dear friends !

    How i can to link table from first secure Access database into second general other Access database.

    I'm looking for solution of this problem many hours (DAO tblDef.Connect, ACC DoCmd.TransferDatabase acLink, ADO) but i don't know to solve this problem !

    Is possible to solve this problem ?

    Thank You
    Martin

  2. #2
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251

    Thumbs down yes and no

    I was trying to do the same.... i was fighting....

    The only way I found is to manually create link (dummy link) and then change it using code. So for example you have a link which points to C:\test.mdb, then you use a code and change it to c:\documents and settings\%current_user\desktop

    this is copy-paste from my App.... relinks all tables with name link.....

    For Each strTable In CurrentDb().TableDefs
    If Left(strTable.Name, 3) = "link" Then
    strTable.Connect = ";DATABASE=" & strMyConnectionString
    strTable.RefreshLink
    End If
    i = i + 1
    Next


    zdravim Cechy!

    Jiri

  3. #3
    Join Date
    Nov 2002
    Location
    Czech republic
    Posts
    3

    Arrow Re: yes and no

    Interesting, but i need to link the table automatically from code . . .
    Probably i'll solve my problem through recerdsets

    Thank You very much

    Vse dobre do SF zasila
    Martin

    Originally posted by playernovis
    I was trying to do the same.... i was fighting....

    The only way I found is to manually create link (dummy link) and then change it using code. So for example you have a link which points to C:\test.mdb, then you use a code and change it to c:\documents and settings\%current_user\desktop

    this is copy-paste from my App.... relinks all tables with name link.....

    For Each strTable In CurrentDb().TableDefs
    If Left(strTable.Name, 3) = "link" Then
    strTable.Connect = ";DATABASE=" & strMyConnectionString
    strTable.RefreshLink
    End If
    i = i + 1
    Next


    zdravim Cechy!

    Jiri

  4. #4
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251

    Re: yes and no

    What do you mean by 'link tables automatically?' There is no such thing!


    LINK tables is MS Access stuff, if you want to access tables from code, you can use DAO or better ADO - you create connection string and then open recordset....


    for ALL connection strings incl. MS JET (used for Access MDB) go to http://www.able-consulting.com/tech.htm



    Dim myRecSet As New ADODB.Recordset

    strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\MyDatabase.mdb"

    myRecSet.Open "tblMyTable", strConnString, adOpenStatic, adLockReadOnly

    With myRecSet
    .... do whatever you want
    End With

    myRecSet.Close
    Set myRecSet = Nothing


    jiri

  5. #5
    Join Date
    Nov 2002
    Location
    Czech republic
    Posts
    3

    Arrow Re: yes and no

    I agree what You write, but my English is poor, sorry . . .
    On MSDN can You read :

    Sub ADOCreateAttachedJetTable()

    Dim cat As New ADOX.Catalog
    Dim tbl As New ADOX.Table

    ' Open the catalog
    cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=.\NorthWind.mdb;"

    ' Set the name and target catalog for the table
    tbl.Name = "Authors"
    Set tbl.ParentCatalog = cat

    ' Set the properties to create the link
    tbl.Properties("Jet OLEDB:Create Link") = True
    tbl.Properties("Jet OLEDB:Link Datasource") = ".\Pubs.mdb"
    tbl.Properties("Jet OLEDB:Link Provider String") = ";Pwd=password"
    tbl.Properties("Jet OLEDB:Remote Table Name") = "authors"

    ' Append the table to the collection
    cat.Tables.Append tbl

    Set cat = Nothing

    End Sub

    BUT THIS IS VALID ONLY FOR UNSECURED MDB, OR IF YOU LINK UNSECURE DATABASE INTO SECURED DATABASE .

    Where I can to write full path towards file with info about workgroup and next login data (UID, PWD) for *.mdw (*.mda) file ?
    I thing 'Link Provider String' - password, serve the purpose of link other as Access database through DSN in ODBC sources

    Many thanks
    Martin

    PS: I go sleep, I'm very exhausted now . . .



    Originally posted by playernovis
    What do you mean by 'link tables automatically?' There is no such thing!


    LINK tables is MS Access stuff, if you want to access tables from code, you can use DAO or better ADO - you create connection string and then open recordset....


    for ALL connection strings incl. MS JET (used for Access MDB) go to http://www.able-consulting.com/tech.htm



    Dim myRecSet As New ADODB.Recordset

    strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\MyDatabase.mdb"

    myRecSet.Open "tblMyTable", strConnString, adOpenStatic, adLockReadOnly

    With myRecSet
    .... do whatever you want
    End With

    myRecSet.Close
    Set myRecSet = Nothing


    jiri

  6. #6
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251

    Re: Link table

    Did you click on the link I included in the last email? If you would, you would get something like this....




    For standard security

    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=c:\somepath\myDb.mdb;" & _
    "User Id=admin;" & _
    "Password="

    If using a Workgroup (System Database)

    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=c:\somepath\mydb.mdb;" & _
    "Jet OLEDBystem Database=MySystem.mdw", _
    "myUsername", "myPassword"
    Note, remember to convert both the MDB and the MDW to the 4.0
    database format when using the 4.0 OLE DB Provider.


    If MDB has a database password

    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=c:\somepath\mydb.mdb;" & _
    "Jet OLEDBatabase Password=MyDbPassword", _
    "myUsername", "myPassword"

Posting Permissions

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