Why is this code not working? Sorry for the generic question....
I am tasked with generating reports with reference information that needs to be drawn from an access database and an excel spreadsheet.
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.
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.
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.
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.
The spreadsheet I am working at the base with is this:
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):
Sub getUserID()
Dim myTable As ListObject
Set myTable = Sheets("Client Codes").ListObjects("Lookup")
If myTable.ListRows.Count >= 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 " & Chr(13) & "" & Chr(10) & "FROM `C:\database\here\test.accdb`.Clients Clients" & Chr(13) & "" & Chr(10) & "WHERE (Clients.LastName='" & Range("b1").End(xlDown) & "') AND (Clients.FirstName='" & Range("c1").End(xlDown) & "')")
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" & Chr(13) & "" & Chr(10) & "FROM `C:\spreadsheet\here\test.xlsx`.`Businesses$` `Businesses$`" & Chr(13) & "" & Chr(10) & "WHERE (`Businesses$`.Operation='" & Range("b1").End(xlDown) & "')")
End With
End Sub
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.
For reference, the database has 3 fields (ID, LastName, FirstName); and the spreadsheet has 1 column (Operation).
Really the confusion is focused here:
- How to 'add' the information based on a query to the listobject to a pre-existing table
- How to do this both with an access database and an Excel spreadsheet
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.
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.
EDIT: Code based on Ian's response....generates the following error message: 'run time error -2147467259, could not find installable ISAM'
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: https://support.microsoft.com/en-us/kb/209805 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: Error: "Could Not Find Installable ISAM"
The last one is obviously the biggest target (and has the most error about it).
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 = '" & firstName & "' AND LastName = '" & lastName & "';"
'& ... ' 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
