title: RODBC read table from Access with non-standard name
body: <p>I am working in R on a dataset provided in MS Access. As I try hard to make my code reproducible, I want to avoid doing anything to data using Access. </p>
  
  <p>the database has some tables that have non-ascii characters (Persian names)</p>
  
  <p>I connect to the database:</p>
  
  <pre><code>cns &lt;- odbcConnectAccess2007(mdbfilename)
  </code></pre>
  
  <p>when I get a list of tables with:</p>
  
  <pre><code>tbls &lt;- sqlTables(cns)
  head(tbls ,10) 
  </code></pre>
  
  <p>I get something like this:</p>
  
  <pre><code>                   TABLE_CAT TABLE_SCHEM                 TABLE_NAME   TABLE_TYPE REMARKS
  1  D:\\HEIS\\DataRAW\\80.mdb        &lt;NA&gt;          MSysAccessObjects SYSTEM TABLE    &lt;NA&gt;
  2  D:\\HEIS\\DataRAW\\80.mdb        &lt;NA&gt;                   MSysACEs SYSTEM TABLE    &lt;NA&gt;
  3  D:\\HEIS\\DataRAW\\80.mdb        &lt;NA&gt; MSysNavPaneGroupCategories SYSTEM TABLE    &lt;NA&gt;
  4  D:\\HEIS\\DataRAW\\80.mdb        &lt;NA&gt;          MSysNavPaneGroups SYSTEM TABLE    &lt;NA&gt;
  5  D:\\HEIS\\DataRAW\\80.mdb        &lt;NA&gt;  MSysNavPaneGroupToObjects SYSTEM TABLE    &lt;NA&gt;
  6  D:\\HEIS\\DataRAW\\80.mdb        &lt;NA&gt;       MSysNavPaneObjectIDs SYSTEM TABLE    &lt;NA&gt;
  7  D:\\HEIS\\DataRAW\\80.mdb        &lt;NA&gt;                MSysObjects SYSTEM TABLE    &lt;NA&gt;
  8  D:\\HEIS\\DataRAW\\80.mdb        &lt;NA&gt;                MSysQueries SYSTEM TABLE    &lt;NA&gt;
  9  D:\\HEIS\\DataRAW\\80.mdb        &lt;NA&gt;          MSysRelationships SYSTEM TABLE    &lt;NA&gt;
  10 D:\\HEIS\\DataRAW\\80.mdb        &lt;NA&gt;    R80P1 روستا?? 80 بخش ?ک        TABLE    &lt;NA&gt;
  </code></pre>
  
  <p>As you can see the name of the table in row 10 contains non-standard characters.
  The name of the table as shown in MS-Access is <code>R80P1 روستایی 80 بخش یک</code>. Because MS-Access tries to send the name in system's locale (which is Persian) it converts the data to Windows-Arabic encoding (codepage 1256) which does not contain a code for Persian Yeh (<code>ی</code>) (which is different than Arabic Yeh (<code>ي</code>)).</p>
  
  <p>It makes it impossible to read the data in this table from R, as we really do not have the name:</p>
  
  <pre><code>tbl &lt;- tbls[10,3]
  RD &lt;- sqlQuery(cns,paste0("Select Address from ",tbl))
  head(RD)
  </code></pre>
  
  <p>I get this error:</p>
  
  <pre><code>[1] "07002 17 [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect "            
  [2] "[RODBC] ERROR: Could not SQLExecDirect 'Select Address from R80P1 روستا?? 80 بخش ?ک'"
  </code></pre>
  
  <p>I have tried to solve this problem in various ways</p>
  
  <ol>
  <li>Rename tables in MS-Access, as my script is going to download 30+ files from internet, extract them and read them, this does not seem viable to do this manual renaming for one of the files (that has this weird naming) in the middle of process.</li>
  <li>Change the encoding when opening connection, I've tried this with all 374 encodings available from <code>iconvlist()</code>, does not solve the problem. An important case: opening ODBC connection using <code>UTF-8</code>, causes the problematic table names to be returned as <code>NA</code>.</li>
  <li>Try to read the table not by name but by index in database, but I don't know how to do that using RODBC (I know the index by <code>grepl</code>ing the initial part of name in Latin)</li>
  <li>Try to use <code>sqlFetch()</code> with a custom SQL query that uses regular expression on table name, I don't know how to do this or even if this is possible.</li>
  </ol>
  
  <p>Any suggestions?</p>
  
  <p>Edit:
  I have to add my <code>sessionInfo()</code>:</p>
  
  <pre><code>R version 3.2.2 (2015-08-14)
  Platform: x86_64-w64-mingw32/x64 (64-bit)
  Running under: Windows 8 x64 (build 9200)
  
  locale:
  [1] LC_COLLATE=Persian_Iran.1256  LC_CTYPE=Persian_Iran.1256    LC_MONETARY=Persian_Iran.1256
  [4] LC_NUMERIC=C                  LC_TIME=Persian_Iran.1256    
  
  attached base packages:
  [1] stats     graphics  grDevices utils     datasets  methods   base     
  
  other attached packages:
  [1] data.table_1.9.6 foreign_0.8-65   RODBC_1.3-12     yaml_2.1.13     
  
  loaded via a namespace (and not attached):
  [1] tools_3.2.2  chron_2.3-47
  </code></pre>
  
  <p>P.S. For those interested in struggling with the actual problem, the file I'm talking about is available for download from: <a href="http://www.amar.org.ir/Portals/0/amarmozuii/hazinedaramad/80.rar" rel="nofollow">http://www.amar.org.ir/Portals/0/amarmozuii/hazinedaramad/80.rar</a>
   (~18 MB)</p>