Problem with Join

[jverri01]

Good Luck.

Hi again, Rabbit.

I started the work on the next piece of this puzzle I'm developing.

I laid down most of the code, have one issue with a JOIN statement.

Can you take a look at this, tell me if you see where my problem is? It is definitley in this line of code:

strSQL = "SELECT * FROM [tbl_SponsorList] INNER JOIN [tbl_SponProd] ON [tbl_SponsorList].[Sponsor_ID] = [tbl_SponProd].[Sponsor_ID] ORDER BY [Prod_Date]"
DoCmd.RunSQL strSQL

Hope you have a minute to assist.

Thanks!

J.

[Rabbit]

I split the thread seeing as how this is a new question.

[Rabbit]

Again, this is the same problem as the previous thread, you can't use a SELECT SQL statement with DoCmd.RunSQL. It just doesn't work no matter how you rephrase it.

To see the results of the select statement you have to do it through a query. This query can then be represented by a form if you so wish.

[jverri01]

Again, this is the same problem as the previous thread, you can't use a SELECT SQL statement with DoCmd.RunSQL. It just doesn't work no matter how you rephrase it.

To see the results of the select statement you have to do it through a query. This query can then be represented by a form if you so wish.

Sorry, this is not clear to me. Can you give me an example that I can work from?

[cyberdwarf]

You could try using an ADO recordset, using your SQL statement as the data source:-

Dim RS as ADO.Recordset
Set Rs = New ADODB.Recordset
strSql = "Select * From........."
Rs.Open strSql, ConnectionString

You can then MOVE thru' the recordset, get a record count, etc

HTH

Steve

[Rabbit]

Sorry, this is not clear to me. Can you give me an example that I can work from?

I can't give you an example because I have no idea what you want to use the SELECT statement for. I can only assume you want the user to view the results of the query. Is that the case?

If so you just make the query and open it in the code with
DoCmd.OpenQuery "NameOfQuery"

[jverri01]

Rabbit,

Basically, I just need to JOIN two unrelated tables (at a related field) so I can generate a query based on the results.

Forgetting the query piece for now - how can I use an INNER JOIN statement in VBA to join two tables?

If you look at my 3 lines of code, you can see how I was attempting to accomplish this.

Thanks again,

J

[Rabbit]

Rabbit,

Basically, I just need to JOIN two unrelated tables (at a related field) so I can generate a query based on the results.

Forgetting the query piece for now - how can I use an INNER JOIN statement in VBA to join two tables?

If you look at my 3 lines of code, you can see how I was attempting to accomplish this.

Thanks again,

J

You say you want to join two tables and generate a query based on the results.

Again, it's the same thing I've been trying to get across the whole time, a non-action SQL statement can not be run using RunSQL. The only SQL statements what work with RunSQL are Update, Insert, Create, Drop, etc. Basically SQL statements that actually affect the tables.

A select statement doesn't do anything to the tables, neither does a crosstab query. A join doesn't affect the tables either. Basically, using RunSQL on a non-action SQL statement accomplishes nothing. If you want to view the results of said SQL statement, create a query and then open it using code like I laid out earlier. I'm not even sure what you want. I assume you want to see the results of the SQL Statement.

Bottom Line: You can't use DoCmd.RunSQL to do what you want.