title: How can I script copying an Access table from one mdb to another?
body: <p>I've inherited a setup that utilizes Access 97 databases. I need to copy a table from one main mdb to multiple others to prep them for use. The source mdb and source table are mostly static, the destination mdbs vary widely.</p>
  
  <p>So, I have:<br>
  source.mdb contains table A<br>
  destination.mdb contains tables 1, 2, and 3  </p>
  
  <p>I need to end up with:<br>
  source.mdb is unchanged (contains table A)<br>
  destination.mdb contains tables 1, 2, 3, and A  </p>
  
  <p>This is a simple enough task within the Access GUI, but because this task needs to happen hundreds of times in a day, I'd like to automate it. The ultimate goal is to have a script or batchfile that I can give each mdb user (about 75 employees) to manipulate each database on their own before use. I'm much more at home in a UNIX/Oracle environment, so scripting this up has thrown me for a loop.</p>
  
  <p>In Oracle, I would use a query like this:  </p>
  
  <blockquote>
    <p>copy from user/password@sourceDB to user/password@destinationDB<br>
       create new_tableA using select * from tableA; </p>
  </blockquote>
  
  <p>A similar question here on stack overflow tells how to copy records from one table to another, but the destination table pre-exists, and particular fields are defined:  </p>
  
  <blockquote>
    <p>strSQL = "INSERT INTO ServiceRecordInvoices " &amp; _<br>
           "( sriID, sriServiceRecordID, sriInvoiceDate, sriInvoiceNumber, " &amp; _<br>
       "sriDescription, sriInvoiceAmount ) " &amp; _<br>
    " IN '" &amp; strDatabasePathandNameTo &amp; "' " &amp; _<br>
    "SELECT srpID, srpServiceRecordID, srpInvoiceDate, srpInvoiceNumber, " &amp; _<br>
       "srpParts, srpPartsAmount " &amp; _<br>
    "FROM ServiceRecordParts IN '" &amp; strDatabasePathandNameFrom &amp; "';"  </p>
  </blockquote>
  
  <p>My first try looks like this and unsurprisingly, doesn't work. Can someone steer me right?  </p>
  
  <blockquote>
    <p>copyTableSql = "CREATE [new_tableA] " &amp; _<br>
       " IN '" &amp; destinationDBpath &amp; "' " &amp; _<br>
       "SELECT * FROM tableA IN '" &amp; sourceDBpath &amp; "';"  </p>
  </blockquote>
  
  <p>Thanks for any advice.</p>