Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    acImport gives Linked table (Access2003)

    Hi!
    I am using code to Import the table.
    When it is linked - my query is very slow.
    However it is making table Linked again.

    Private Sub Form_Open(Cancel As Integer)


    Dim msg As String
    Dim rsp

    msg = "Do you want to updat/refresh the data ?"
    rsp = MsgBox(msg, vbYesNo, "Update Data")

    If rsp = vbYes Then

    DoCmd.TransferDatabase acImport, "Microsoft Access", _
    "Cocuments and SettingsAccessReport.mdbs", acTable, "ReportTable123", "ReportTable"
    End If
    End Sub

    How can I Import 6 tables?
    And if you please, can explain it to me - why is the code works faster than when I am Importing same tables using File-Get Externat Data-Import???

    THANKS

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

    Re: acImport gives Linked table (Access2003)

    TransferDatabase with acImport does *not* link a table.
    However, if the destination table "ReportTable" already exists, TransferDatabase does not overwrite it, but it imports to a new table ReportTable1 (or if that exists, ReportTable2, etc.)
    So you should delete the destination table before importing the source table.

    If you want to import 6 tables, issue 6 TransferDatabase commands.

    VBA code generally works faster because it assumes that you know what you're doing so it performs fewer checks than the menu option.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: acImport gives Linked table (Access2003)

    Call me crazy but I had added a line - DoCmd.DeleteObject acTable, "ReportTable"
    and result is ReportTable appearing to be linked.
    Though I have to say this database "Cocuments and SettingsAccessReport.mdbs"
    is THE database where I am working. So maybe I should change it to CurrentDB or something?
    And ReportTable123 is the Linked table from Oracle.



    Private Sub Form_Open(Cancel As Integer)


    Dim msg As String
    Dim rsp

    msg = "Do you want to updat/refresh the data ?"
    rsp = MsgBox(msg, vbYesNo, "Update Data")

    If rsp = vbYes Then

    DoCmd.DeleteObject acTable, "ReportTable"

    DoCmd.TransferDatabase acImport, "Microsoft Access", _
    "Cocuments and SettingsAccessReport.mdbs", acTable, "ReportTable123", "ReportTable"
    End If
    End Sub

    Thanks

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

    Re: acImport gives Linked table (Access2003)

    Importing a linked table, whether from the same database or from another one, will result in a copy of the linked table, i.e. the result will be a linked table too

    To import the data themselves you must import them directly from Oracle. The code will look similar to the followin, but with the correct names substituted, of course:

    DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=dsnname;UID=username;PWD=password;SERVER =servername", acTable, "ReportTable123", "ReportTable"

  5. #5
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: acImport gives Linked table (Access2003)

    I am almost there...except I am confused with table names - "ReportTable123", "ReportTable"

    I am getting message that MS Jet DB engine can't find ReportTable.
    I had tried with ReportTable existed and ReportTable absent and absent and deleted.

    Any suggestions? Thanks! It sees Oracle DB and it was the hardest part, right?
    Name of THE table in Oracle I need to Import is ReportTable123.
    ReportTable is destination, is it correct?

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

    Re: acImport gives Linked table (Access2003)

    "ReportTable" is the name of the destination table in your Access database.

    Are you sure that the error message is caused by DoCmd.TransferDatabase and not by DoCmd.DeleteObject?

  7. #7
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: acImport gives Linked table (Access2003)

    Please, one more time. No, Delete code getrs passed fine.
    It seems like my code is finding RecruitTable123 in Oracle and when it is getting to acTable, "RecruitTable123", "RecruitTable"
    it spits message that engine can not find RecruitTable. Error read 'make sure object exists and name spelled correctly'...
    I am not sure - if I should have a shell for RecruitTable? Does it suppose to exist as if it was Append query?

    Or to make a story short - which table suppose to exist in Access and which is suppose to not before code execution? Thanks

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

    Re: acImport gives Linked table (Access2003)

    The destination table should not exist yet. So I don't understand the error message, sorry.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: acImport gives Linked table (Access2003)

    The destination table should not exist yet and ReportTable123 should/should not be linked into Access.mdb?
    Sorry and thanks

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

    Re: acImport gives Linked table (Access2003)

    The destination table should not exist yet.
    It shouldn't matter whether your database already contains a link to ReportTable123 or not, as far as I know.

Posting Permissions

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