[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 474
  • Last Modified:

Access SQL Join Statement in VBA - Help

Hi

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.CompanyName FROM tbl001_Clients INNER JOIN tbl001_CompanyHeader ON tbl001_Clients.CompanyID = tbl001_CompanyHeader.CompanyID;

Any idea what's wrong here?

Thanks




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;"

Debug.Print strSQL

If objMyConn.State = adStateOpen Then
MsgBox ("You are in")
End If

rstClient.Open strSQL, objMyConn, , adLockReadOnly

If rstClient.State = adStateOpen Then
With rstClient
.MoveFirst
If (.State = adStateOpen) And (Not (.EOF)) Then
Do Until .EOF
strName = .Fields("CompanyName")
Debug.Print strName
.MoveNext
Loop
End If
End With
End If

Open in new window

0
HDLondon
Asked:
HDLondon
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
Zlatko KuzmanovskiprogrammerCommented:
Hi,
Please clarify:
a) "..it doesn't open..", or
b) "..it doesn't produce any output.."

The query looks fine.

Regards,
Zlatko.
0
 
HDLondonAuthor Commented:
Basically the "If rstClient.State = adStateOpen Then" line fails so it skips the section that looks into the recordset
0
 
Rey Obrero (Capricorn1)Commented:
you don't need those lines

rstClient.Open strSQL, objMyConn, , adLockReadOnly

  ' If rstClient.State = adStateOpen Then


If rstClient.EOF Then Exit Sub
With rstClient
       .MoveFirst
        Do Until .EOF
        strName = .Fields("CompanyName")
        Debug.Print strName
        .MoveNext
        Loop

End With
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
HDLondonAuthor Commented:
Thanks for the comments

I've tried the code suggested by capricorn1, but the Goto kicks in on the line:

If rstClient.EOF Then GoTo ExitScript

The rstClient.EOF statement says 'Operation is not allowed when the object is closed', so I guess the recordset isn't opening.

0
 
Rey Obrero (Capricorn1)Commented:
this isn't correct
Set objMyConn = OpenAccessDB("Z:\[DBNAME].mdb")

are you connecting to another DB?
0
 
HDLondonAuthor Commented:
sorry - i should have mentioned that I amended that line - in my version [DBNAME] is the path of our database on our shared network drive.

I think the fact that the following bit of code works ok means the database is opening ok.  Also, i've managed to open the recordset and get data out when there is no INNER JOIN in the strSQL.  It's only when there is a INNER JOIN that the recordset doesn't open.

If objMyConn.State = adStateOpen Then
MsgBox ("You are in")
End If
0
 
Rey Obrero (Capricorn1)Commented:
copy and paste this SQL statement

strSQL = "SELECT tbl001_Clients.CompanyID, tbl001_CompanyHeader.CompanyName "
strSQL = strSQL & " FROM tbl001_Clients INNER JOIN tbl001_CompanyHeader "
strSQL = strSQL & " ON tbl001_Clients.CompanyID = tbl001_CompanyHeader.CompanyID;"

0
 
Zlatko KuzmanovskiprogrammerCommented:
Hi,
It MUST open:
a- if there is a problem with the strSQL string, than an error should occur,
b- if strSQL string is correct, the recordset will open (your case), but may not produce any result..

Now, please tell what (if any) error is produced when using "JOINED" query ?

Thank you,
Zlatko.
0
 
JezWaltersCommented:
You're already at the first record when you open a recordset, so a simpler code structure would be as follows:

    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"
    rstClient.Open strSQL, objMyConn, , adLockReadOnly

    With rstClient
        Do While Not .EOF
            strName = !CompanyName
            Debug.Print strName
            .MoveNext
        Loop
    End With
0
 
JezWaltersCommented:
It would be useful to show some sample data from the tbl00_Clients and tbl001_CompanyHeader tables - or even post a sample database
0
 
HDLondonAuthor Commented:
OK - i've sorted it.  I forgot that I had 'On Error Resume Next' at the start, so i wasn't getting the error.  Turns out I wasn't referencing the table correctly.

Sorry to take up your time on such a simple problem, but I really appreciate your input.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now