Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing tables keeping relationships (97)

    Hi, I have this code which imports all (non system) tables from a database, it works fine ( thanks to previous posts on the forum - i got stuck on the password thingy)

    Function importTables()
    Dim strTable As String, strPswd As String, i As Integer
    Dim db As DAO.Database
    strPswd = InputBox("Password for DB?")
    Set db = OpenDatabase("K:blahblahtest.mdb", False, False, ";PWD=" & strPswd)
    For i = 0 To db.TableDefs.Count - 1
    If db.TableDefs(i).Attributes = 0 Then
    strTable = db.TableDefs(i).Name
    DoCmd.TransferDatabase acImport, "Microsoft Access", _
    "K:blahblahtest.mdb" _
    , acTable, strTable, strTable, False
    End If
    Next i
    db.Close
    Set db = Nothing
    End Function

    It doesn't import the relationships though, which is not good [img]/forums/images/smilies/sad.gif[/img] is it just a tweak of one of the parameters or do I have to find a different way of doing this?

    <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15> Any help very appreciated as always .
    Thanks,

    pmatz

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Importing tables keeping relationships (97)

    You will need to use DAO to do this, AFAIK TransferDatabase method does not import relationships along with tables. For example, see this MSKB article:

    ACC2000: How to Use DAO in Visual Basic Code to Import Relationships

    Can also use ADOX. Example, see:

    ACC2002: How to use ADOX to Import Relationships

    Since you are already using DAO in your procedure, recommend stick to DAO methods. DAO & ADO do not get along well in same procedure. (Just noticed using ACC 97 - note ADOX not supported in ACC 97; link provided for information only for anyone who prefers ADO to DAO).

    HTH

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Importing tables keeping relationships (97)

    Here is another MSKB link (specific to ACC 97):

    ACC97: Sample Code to Import All Database Objects

    Sample code includes importing Relation objects. This may be simpler method for what you're doing.

    HTH

  4. #4
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing tables keeping relationships (97)

    COol! Thanks Mark, I am sure I can use links to talior my code .
    <img src=/S/coffeetime.gif border=0 alt=coffeetime width=32 height=48>
    Im only just getting my head around the depths od DAO and stuff, so this is really helpful.
    Thanks,

    pmatz

  5. #5
    Star Lounger
    Join Date
    Apr 2006
    Location
    California
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing tables keeping relationships (97)

    I need to convert several hundred BE 97 .mde databases. I've copied the code from the article that you referenced in this post. I've a refrence to the Microsoft DAO 3.6 Object Library. However, I get a "Member or data member not found" error on compilation on the line that contains "strFFName = fld.ForeignName" in the "Copy relationships to current database" section of the code. Any thoughts on what I've done wrong/overlooked?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing tables keeping relationships (97)

    Either clear the reference to the Microsoft ActiveX Data Objects 2.n Library, or move this reference to below the DAO one, or declare fld explicitly as a DAO object:

    Dim fld As DAO.Field

    Otherwise, fld will be interpreted as an ADO field, and this causes confusion, of course.

  7. #7
    Star Lounger
    Join Date
    Apr 2006
    Location
    California
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing tables keeping relationships (97)

    Hans,
    Thanks much.
    Marty

Posting Permissions

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