Need help with SQL

[jalopez]

I read a few weeks ago a post in which someone published a link to a site that offered a SQL generator that uses english like language to produce a SQL query. I don't remember who published it, but, PLEASE I need that link now.

I have to make a lot of queries using SQL SELECT statements and I do not have the time to use Access to generate buggy code and I neither have the time to guess the queries.

-------------------------------------------------------------------------------
The first query I need to produce should be able to get all the records from a table that contains images but the images should be of the same user. I mean:

Table 1
------------------
RecID
CustName


Table2
-------------------
RecID
Name
Description
Picture


Query Case:
-------------------
I need a query to get all the pictures from Table2 where the Table2.Name = Table1.CustName ordered by Table2.Description ASC.


VB Case:
----------------
When I get the pictures in the query, they should appear listed by description in a listbox, and when I click on a name in the listbox I should be able to see the picture in a imagebox. Only the images from the selected custommer should appear in the listbox.

[seoptimizer2001]

'Declarations
Dim mstrPicture() as String
 
Private Sub Form_Load()
Dim i as Integer
Dim sql as String
Dim cn as New ADODB.Connection
Dim rs as New ADODB.Recordset
 
cn.Open("connectionstring")
 
sql = "select b.picture, b.description from table2 a " _
   & " inner join table1 b on b.name = a.custname " _
   & " order by b.description"
 
set rs = cn.Excecute(sql)
 
While Not rs.EOF
   List1.AddItem rs!description
   Redim Preserve mstrPicture(i) 
   mstrPicture(i) = rs!picture
   i = i + 1
   rs.MoveNext
Wend
End Sub
 
Private Sub List1_Click()
   Picture1.Picture = mstPicture(List1.ListIndex)
End Sub

This would assume that the full path to the picture is stored in the picture field.

[ksw]

Don't forget to close your recordset and connection objects and set the variables to nothing. It can cause you problems if you don't.

Also, what does the array mstrPicture do for you? Won't this code work just as well without it?

--KSW

[seoptimizer2001]

The array saves all the file locations of the pictures based on the listindex of the description, so you don't have to go back to the database for each one. And obviously close your connections. I am just rattling this off here.

[jalopez]

Thank you very much.

I appreciate the time you have taken to answer this stupid question. Although this was not what I did it gave me a cool idea of what to do. Thanks.