title: Getting user ID from a Spreadsheet and Access database
body: <p>Why is this code not working? Sorry for the generic question....</p>
  
  <p>I am tasked with generating reports with reference information that needs to be drawn from an access database and an excel spreadsheet.</p>
  
  <p>Basically in my role I'm responsible for providing service to people who live in a community; the record of all the people I provide service for is contained in an access database. There's reference information; address, name, situation, and other information needed for regular reports to funders or the board of directors.</p>
  
  <p>I also provide service to local businesses; this information is contained within a spreadsheet, and not a database. The information could be put into a relational database, with the two related together; but there is resistance at the organization for significant changes to the system, nor is there really the knowledge of how to do this.</p>
  
  <p>So I'm trying to move forward with a spreadsheet - if I provide service to person A or organization B, that this spreadsheet will check both the access database, and the excel spreadsheet to see whether that person or organization is entered; if it is, it should populate a table with that information, and assign it a unique code.</p>
  
  <p>The unique code is determined on the basis of the database; whether or not the person or organization has been entered into the database before.</p>
  
  <p>The spreadsheet I am working at the base with is this:</p>
  
  <p><a href="https://i.stack.imgur.com/k8Wu1.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/k8Wu1.png" alt="enter image description here"></a></p>
  
  <p>The bottom table I am looking to be a 'lookup' table. Its name is Lookup. The code I want to run with it looks like this (but obv not is this):</p>
  
  <pre class="lang-vb prettyprint-override"><code>Sub getUserID()
  
      Dim myTable As ListObject
  
  
      Set myTable = Sheets("Client Codes").ListObjects("Lookup")
          If myTable.ListRows.Count &gt;= 1 Then
              myTable.DataBodyRange.Delete
          End If
  
  With Sheets("Client Codes").ListObjects("Lookup").Add(SourceType:=0, Source:=Array(Array("ODBC;DSN=MS Access Database;DBQ=C:\database\here\test.accdb;DefaultDir=F:\Housing;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeo"), Array("ut=5;")), Destination:=myTable.Range(Cells(1, 1)))
      .CommandText = Array("SELECT Clients.ID, Clients.LastName, Clients.FirstName " &amp; Chr(13) &amp; "" &amp; Chr(10) &amp; "FROM `C:\database\here\test.accdb`.Clients Clients" &amp; Chr(13) &amp; "" &amp; Chr(10) &amp; "WHERE (Clients.LastName='" &amp; Range("b1").End(xlDown) &amp; "') AND (Clients.FirstName='" &amp; Range("c1").End(xlDown) &amp; "')")
  End With
  
  With Sheets("Client Codes").ListObjects("Lookup").Add(SourceType:=0, Source:=Array(Array("ODBC;DSN=Excel Files;DBQ=C:\spreadsheet\here\text.xlsx;DefaultDir=c:\spreadsheet;DriverId=1046;MaxBufferSize=2"), Array("048;PageTimeout=5;")), Destination:=myTable.Range(Cells(1, 1)))
      .CommandText = Array("SELECT `Businesses$`.Operation" &amp; Chr(13) &amp; "" &amp; Chr(10) &amp; "FROM `C:\spreadsheet\here\test.xlsx`.`Businesses$` `Businesses$`" &amp; Chr(13) &amp; "" &amp; Chr(10) &amp; "WHERE (`Businesses$`.Operation='" &amp; Range("b1").End(xlDown) &amp; "')")
  
  End With
  End Sub
  </code></pre>
  
  <p>The hope is to be able to query the database on the basis of either a persons first and last name, or to query the spreadsheet on the basis of organization name; and if there is a value that is found, to add some information to the table 'Lookup'. If nothing is found, then I will know its a new entry, and enter in the information as such. </p>
  
  <p>For reference, the database has 3 fields (ID, LastName, FirstName); and the spreadsheet has 1 column (Operation).</p>
  
  <p>Really the confusion is focused here:</p>
  
  <ol>
  <li>How to 'add' the information based on a query to the listobject to a pre-existing table</li>
  <li>How to do this both with an access database and an Excel spreadsheet</li>
  </ol>
  
  <p>Any suggestions on other ways how this can be done would be appreciated; pull information from multiple data sources into one table so that it can be validated in that table.</p>
  
  <p>EDIT: If I did this through Access or another database program, I would do an INNERJOIN on two tables; one of people, the other of businesses. I'm looking to keep excel though - I find it to be more user friendly.</p>
  
  <p>EDIT: Code based on Ian's response....generates the following error message:
  'run time error -2147467259, could not find installable ISAM'</p>
  
  <p>Research on the internet seems to indicate the following:
  1) People have gotten this error before
  2) There might not be a proper DLL installed - not certain this is the case, because I'm trying to access access from excel, and it doesn't seem like there is a DLL for access here: <a href="https://support.microsoft.com/en-us/kb/209805" rel="nofollow noreferrer">https://support.microsoft.com/en-us/kb/209805</a>
  3) There might be issue of how the connection string is framed. The data source might need to be in quotes, the JET OLEDB needs to be used not ACE, the connection string needs to be extended to include 'extended properties' here: <a href="https://stackoverflow.com/questions/512143/error-could-not-find-installable-isam">Error: &quot;Could Not Find Installable ISAM&quot;</a> </p>
  
  <p>The last one is obviously the biggest target (and has the most error about it). </p>
  
  <pre><code>Option Explicit
  Sub getUserID()
  
  
      Dim cmd As New ADODB.Command
      Dim conn As New ADODB.Connection
      Dim rs As New ADODB.Recordset
      Dim strConn As String
      Dim strSQL As String
      Dim firstName As String
      Dim lastName As String
  
  
      firstName = "John"
      lastName = "Smith"
  
      strConn = "Provider = Microsoft.ACE.OLEDB.12.0;'DataSource=F:\Housing\bpTest.accdb'"
  
  
      conn.Open strConn
  
      strSQL = "SELECT * FROM Table Where FirstName = '" &amp; firstName &amp; "' AND LastName = '" &amp; lastName &amp; "';"
  
  
      '&amp; ... ' or You could put your InnerJoin SQL here
  
      rs.Open strSQL, conn, adOpenDynamic, adLockOptimistic
  
      If rs.EOF Then 'If the returned RecordSet is empty
  
          MsgBox ("No record found")
  
  
      Else
  
          MsgBox (rs.Index)
  
  
      End If
      end sub
  </code></pre>