Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2004
    Posts
    287

    Unanswered: Moving Data from one database to another

    Is there an automatic way to move data between databases.
    I have a linked exchange inbox as a table in my database, and the same fields for the rest of the table, I want to move them from TBL_Inbox to TBL_Main
    Actually move rather than copy? How can I do this via VBA please (or a macro)???

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    2 steps:

    Query to copy the data over then either drop the table or delete the contents ... Issue: are you "moving" the entire table contents? Or is this a"selective" move?
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Mar 2004
    Posts
    287

    update

    i want to just move certain fields across from the Inbox table

    and could you explain in lameman's terms how to do it please?

    what do you mean by drop, what commands do i use, code, etc...

    thanks.

  4. #4
    Join Date
    Mar 2004
    Posts
    287
    right i have it working via a macro...

    Here's the general jist of it...

    1. TransferSpreadsheet - Exports to Excel file from Selected field in QUERY
    2. Wipe the ITHelpdesk Table clean
    3. TransferSpreadsheet - Imports the database back into TBL_Main
    4. Delete External file here

    I need to cover these two points and how to do them via VBA or Macro;
    2. Wipe the ITHelpdesk Table clean
    4. Delete External file here

    Can you or anyone help me further please.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The SQL statement for deleting all the contents of a table is:
    Code:
    DELETE * FROM YourTable
    To delete an external file you use the "kill" command in VBA.
    However, having a kill command anywhere in your app makes it... well I imagine you'll get more security messages...
    Code:
    Kill pathname
    Is there any reason why you're transferring to spreadsheet rather than just populating a temporary table?
    George
    Home | Blog

  6. #6
    Join Date
    Mar 2004
    Posts
    287
    thanks, but how do i run the SQL / SQL query from within VB please?
    (DELETE * FROM YourTable)

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    doCmd.RunSQL "DELETE ...."
    George
    Home | Blog

  8. #8
    Join Date
    Mar 2004
    Posts
    287
    many thanks.

  9. #9
    Join Date
    Mar 2004
    Posts
    287
    thanks, that worked a treat - just one last thing,
    do you know how to suppress the messages that appear about deleting records, etc etc

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That's an acecss security thing...
    I think there's a way you can programmatically turn them off - I think it somethings like
    Code:
    Set warnings = false
    ...
    Set warnings = true
    But don't quote me on that at all.
    George
    Home | Blog

  11. #11
    Join Date
    May 2005
    Posts
    1,191
    But don't quote me on that at all.
    DoCmd.SetWarnings False
    'do sql
    DoCmd.SetWarnings True
    Me.Geek = True

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That's what I meant
    George
    Home | Blog

  13. #13
    Join Date
    May 2005
    Posts
    1,191
    But of course
    Me.Geek = True

  14. #14
    Join Date
    Mar 2004
    Posts
    287
    many thanks, that worked a treat!

Posting Permissions

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