I'm having trouble obtaining a recordset in VBA. I'm coding in MS Outlook and have established a connection with our Access database. I can generate a recordset with no joins without any issues, but when I try to include a join the recordset doesn't open.
I have two tables:
tbl001_CompanyHeader - This contains a list of all companies we speak to. CompanyID is the primary key and it also has a CompanyName field
tbl001_Clients - This is a list of companies that are classified as clients. It has a CompanyID field, but no CompanyName field.
I just need a simple join to create a recordset that has the CompanyID's and CompanyName's of the companies in the tbl001_Clients table.
I've attached the code, and the results of the debug.print (i.e. strSQL) is as follows:
SELECT tbl001_Clients.CompanyID, tbl001_CompanyHeader.Compa
nyName FROM tbl001_Clients INNER JOIN tbl001_CompanyHeader ON tbl001_Clients.CompanyID = tbl001_CompanyHeader.Compa
Any idea what's wrong here?
Set objMyConn = OpenAccessDB("Z:\[DBNAME].mdb")
Set rstClient = CreateObject("ADODB.Recordset")
strSQL = "SELECT tbl001_Clients.CompanyID, tbl001_CompanyHeader.CompanyName " & "FROM tbl001_Clients " & _
"INNER JOIN tbl001_CompanyHeader " & "ON tbl001_Clients.CompanyID = tbl001_CompanyHeader.CompanyID;"
If objMyConn.State = adStateOpen Then
MsgBox ("You are in")
rstClient.Open strSQL, objMyConn, , adLockReadOnly
If rstClient.State = adStateOpen Then
If (.State = adStateOpen) And (Not (.EOF)) Then
Do Until .EOF
strName = .Fields("CompanyName")