title: Insert into a different Access.accdb select from SQL Server linked table
body: <p>I have an MS Access 2010 database with tables linked to a SQL Server 2008R2 back end. </p>
  
  <p>I need to select records from a linked table and insert them into a <em>different</em> Access .accdb, that has a local table.</p>
  
  <p>This is the code I have:</p>
  
  <pre><code>Const DbPath = "L:\path\"
  Const DbBaseName = "FileTransfer OnSite"
  Const DbExt = ".accde"
  Const LockDbExt = ".laccdb"
  Const ConString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq="
  
  Dim Con As ADODB.Connection
  Dim RS As ADODB.Recordset
  Dim DbOutName As String
  Dim DbInName As String
  Dim Overwrite As Integer
  Dim PutStr As String
  
  'do some sanity checking first, then
  FileCopy DbInName, DbOutName    'make a physical copy of the blank on-site file
  Set Con = New ADODB.Connection
  Con.ConnectionString = ConString &amp; DbOutName
  Con.Open
  
  PutStr = "INSERT INTO [;Database=" &amp; DbOutName &amp; "].[Loan] (Loan, FromFirm, ToFirm, Borrower, PropStreet, PropCity, PropState, PropZip, Judicial, Examiner) " &amp; _
      "SELECT Loan, FromFirm, ToFirm, Borrower, PropStreet, PropCity, PropState, PropZip, Judicial, Examiner " &amp; _
      "  FROM Loans " &amp; _
      " WHERE FromFirm = '" &amp; Me.cmbFrom &amp; "' " &amp; _
      "   AND ToFirm = '" &amp; Me.cmbTo &amp; "'"
  Set RS = Con.Execute(PutStr)
  </code></pre>
  
  <p>Note that [;Database=...].[Loan] is a local table to the destination database, and that <code>FROM Loans</code> references the externally linked table in the source db (the one where this code is running).</p>
  
  <p>PutStr resolves to: (Edited for privacy of the companies involved)</p>
  
  <pre><code>INSERT INTO [;Database=L:\Path\FileTransfer OnSite-E-A.accde].[Loan] (Loan, FromFirm, ToFirm, Borrower, PropStreet, PropCity, PropState, PropZip, Judicial, Examiner)
  SELECT Loan, FromFirm, ToFirm, Borrower, PropStreet, PropCity, PropState, PropZip, Judicial, Examiner
    FROM Loans
   WHERE FromFirm = 'E'
     AND ToFirm = 'A'
  </code></pre>
  
  <p>I get an error message on the <code>Set RS =</code> line stating: </p>
  
  <pre><code>Run-time error '-2147217865 (80040e37)':
  [Microsoft][ODBC Microsoft Access Driver] The Microsoft Access
  database engine cannot find the input table or query 'Loans'. Make
  sure it exists and that its name is spelled correctly.
  </code></pre>
  
  <p><code>Loans</code> is defined in Access as an externally linked table to a SQL Server back end. Other code in the db is working just fine accessing this table. One thing that I thought of, is that the table <code>Loans</code> is not in the default schema in SQL Server, but as far as I can tell, Access doesn't care at this point. I can select from <code>Loans</code> elsewhere, since the schema info is contained in the link - Access just knows it as <code>Loans</code>. I did try referencing <code>Loans</code> as <code>[schema].[Loans]</code>, but Access didn't like that, either.</p>
  
  <p>I'm not sure why Access can't find the table <code>Loans</code> that is defined in the DB where the code is running. I supposed I could break this up into a SELECT from Loans, then a loop to insert into the external .accdb, but I'd rather do it cleanly in a single query if possible.</p>