Using VBA to create a Select Query for Two Tables

Hi jpgalasso,

The thing is you cannot use runsql to execute select queries, only update/append/insert/alter queries.  Steve's right we need to know what you're actually trying to do.

Kelly

RunSQL is only used for INSERT, DELETE and UPDATE query types, not for SELECT.
Depending on what you are doing with the results you could create a recordset and bind a form to the recordset so users could view the data on a form. If you want to view the results ina report you will need to create a querydef which is what you would need to do if you want to export (easiest way to export) the data also.

for a form ...

in declaration section of form's module
Private mrstForm As DAO.Recordset


in whatever event causes you to build the recordset ... perhaps Open
Private Sub Form_Open()
Set rst = CurrentDB.OpenRecordset("   your SQL goes here   ")
Set Me.Recordset = mrstForm
End Sub

'don't forgetr to cleanup when the form closes
Private Sub Form_Close()
mrstForm.Close
Set mrstForm = Nothing
End Sub

if you want to open a report of the SQL you can save a query def
I would have a saved query that could be modified and your report uses the saved query as it's record source
CurrentDB.QueryDefs.SQL = " ... your sql here ..."

DoCmd.OpenReport "ReportNameHere", acPreview


Steve

heres the SQL statement you want

SQL = "SELECT FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1;"

As far as running it, just make the query and save it in access

then you can run

queryName = "dude"
DoCmd.OpenQuery queryName

bramsquad

Steve and Kelly,

I need to create a table with the results. I then need to perform other functions on the Table and ultimately capture summary output on a form.

I hope that provides the kind of information that you are looking for.

Thanks much for your help.

Jim

You will need INSERT INTO ... It will be easier for you if you build this in a query yourself (so the field names will be correct) and then save it and run it copy the SQL here and let us know which parts of the SQL need to be dynamic.

Steve

If I understand your question, I need the entire process to be dynamic since I cannot require User intervention. The field names would be something like: "Field1", "Table1_Field2", "Table2_Field2", "Table1_Field3", "Table2_Field3" (the results could be captured by either a Query or a Table).

Thanks again.

Jim

bramsquad,

I don't know if you are still out there, but I tried your statement as follows:

Dim SQL As String
Dim qdef As QueryDef

SQL = "SELECT FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1;"

Set qdef = CurrentDb.CreateQueryDef("qryTest", SQL)

But I get the following error "The SELECT statement includes a reserved word or an argument name that is mispelled or missing, or the punctuation is incorrect."

I should note that Table1 has some Fields that are not in Table2. While I do not believe that is causing the error, I believe I need to somehow refer to the common fields in addition to Field1 (i.e. Field2 and Field3) of both Table1 and Table2. That is, I would like "qryTest" to include the following five fields: Field1, Table1:Field2, Table2:Field2, Table1:Field3, and Table2:Field3).

Thank you for any help.

Jim