Results 1 to 8 of 8
  1. #1
    CodeLiftSleep is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Posts
    48

    Question How to do an INSERT INTO SELECT using Table from another DB and LEFT JOIN table from the current DB

    I'm in a little bit of a spot here that I haven't been able to resolve on my own, I thought I'd get some advice.

    I have a DB that retrieves only the accounts needed for a user from the Backend DB(due to the shared network drive being unusably slow with any more than 1 user on at a time). However, what I need to do in this instance is to use an INSERT INTO STATEMENT to retrieve Accounts on the server but then also do a LEFT JOIN with a table locally.

    Here is the statement I have which is throwing an "ERROR in the FROM Statement":

    SQL = "INSERT INTO Accounts SELECT a.*, t.* FROM Accounts a IN '" & DBPath & "AM.accdb' LEFT JOIN UnT t ON (t.SM = a.EmpName)"

    I've also tried it with

    SQL = "INSERT INTO Accounts SELECT a.* FROM Accounts a IN '" & DBPath & "AM.accdb' LEFT JOIN UnT t ON (t.SM = a.EmpName)"

    With the same results.

    Any help on getting this to work properly?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Show the value of DBPath.

    I cannot replicate this issue but I am not trying a join.

    If you don't need to INSERT any values from UnT, why join to it? Why would you duplicate values from UnT anyway?

    Does it work without the join?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CodeLiftSleep is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Posts
    48
    DBPath is the server path, it works fine every other query in the program uses it without issue.

    There is another rather complex subquery tacked onto it but that I know works because I use it elsewhere and it brings back what I need. That isn't the issue, so I simplified it down to this because once this works the rest of it will work.

    The issue is its throwing an error while trying to use a LEFT JOIN on a table from the current DB with a table from another DB.

    Yes the insert into select statement works fine by itself without the join.

    If I run the query with both tables in the same DB its fine, it runs without issue.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I still don't understand why the join is needed.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    CodeLiftSleep is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Posts
    48
    Quote Originally Posted by June7 View Post
    I still don't understand why the join is needed.
    Because I am only wanting to bring back the accounts where the employee names are equal in the table and in the section I left out it also includes where t.Manager = GetCurrentUser() among some other things...

    Manager is only in the UnT table so the LEFT JOIN causes it to only brings back accounts from employees with that manager

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Did some testing using query builder. First typed the INSERT query in SQL view without the join. Then switched to Design view and added the local table and join link. The resulting SQL statement has the JOIN clause before the IN clause. Tested INNER, LEFT, RIGHT and all inserted records.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    CodeLiftSleep is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Posts
    48
    Quote Originally Posted by June7 View Post
    Did some testing using query builder. First typed the INSERT query in SQL view without the join. Then switched to Design view and added the local table and join link. The resulting SQL statement has the JOIN clause before the IN clause. Tested INNER, LEFT, RIGHT and all inserted records.
    Nice! So I'd include the entire LEFT JOIN clause before the IN statement, like so?


    SQL = "INSERT INTO Accounts SELECT a.*, t.* FROM Accounts a LEFT JOIN UnT t ON (t.SM = a.EmpName) IN '" & DBPath & "AM.accdb'"

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Rats! Tested again and realized the Tables List was showing only tables from the remote db and I had linked to table with same name in the remote db. So I deleted the remote table and dragged the local table from Navigation Pane into query design and even though it appears to be the table in the JOIN clause, the query still looks for that table name in the remote db and therefore get an error message. Thinking cannot do what you want.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Left OUTER Join to more than one table?
    By oemar00 in forum Queries
    Replies: 3
    Last Post: 09-20-2013, 03:58 PM
  2. Replies: 8
    Last Post: 11-04-2011, 06:52 AM
  3. Replies: 3
    Last Post: 02-02-2011, 01:00 PM
  4. Replies: 1
    Last Post: 12-02-2010, 04:54 PM
  5. Join A Recordset to the current db Table
    By mjellis in forum Programming
    Replies: 0
    Last Post: 08-10-2010, 02:44 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums