0

I wonder why I can't execute this SQL query in access 2007 through VBA, which can be executed when i create a query myself:

Private Sub SQL_Click()

Dim curDatabase As Database
Dim RS_REPORT As Recordset

Set curDatabase = CurrentDb
Set RS_REPORT = _
curDatabase.OpenRecordset("REPORT_CONTENT_ARCHIVE", dbOpenDynaset)

strStatement = "SELECT REPORT_CATEGORY1.DESCRIPTION, REPORT_CATEGORY2.DESCRIPTION, REPORT_CATEGORY3.DESCRIPTION, REPORT_RECOMMENDATION.RECOMMENDATION, REPORT_CONTENT_ARCHIVE.REPORT_UID, REPORT_CONTENT_ARCHIVE.CATEGORY1_ID, REPORT_CONTENT_ARCHIVE.CATEGORY2_ID, REPORT_CONTENT_ARCHIVE.CATEGORY3_ID" & _
"FROM (REPORT_CATEGORY1 RIGHT JOIN REPORT_CATEGORY2 ON REPORT_CATEGORY1.CATEGORY1_ID=REPORT_CATEGORY2.CATEGORY1_ID) RIGHT JOIN (REPORT_CATEGORY3 RIGHT JOIN (REPORT_CONTENT_ARCHIVE INNER JOIN REPORT_RECOMMENDATION ON (REPORT_CONTENT_ARCHIVE.CATEGORY3_ID=REPORT_RECOMMENDATION.CATEGORY3_ID) AND (REPORT_CONTENT_ARCHIVE.CATEGORY2_ID=REPORT_RECOMMENDATION.CATEGORY2_ID) AND (REPORT_CONTENT_ARCHIVE.CATEGORY1_ID=REPORT_RECOMMENDATION.CATEGORY1_ID)) ON (REPORT_CATEGORY3.CATEGORY2_ID=REPORT_RECOMMENDATION.CATEGORY2_ID) AND (REPORT_CATEGORY3.CATEGORY1_ID=REPORT_RECOMMENDATION.CATEGORY1_ID) AND (REPORT_CATEGORY3.CATEGORY3_ID=REPORT_RECOMMENDATION.CATEGORY3_ID)) ON (REPORT_CATEGORY2.CATEGORY2_ID=REPORT_CATEGORY3.CATEGORY2_ID) AND (REPORT_CATEGORY2.CATEGORY1_ID=REPORT_CATEGORY3.CATEGORY1_ID)" & _
"WHERE (((REPORT_CONTENT_ARCHIVE.REPORT_UID)=12));"



Set qryMRSA = curDatabase.CreateQueryDef("DATABASE_RECOMMENDATION_1", strStatement)


End Sub

I receive error of 3075, what is it?

Thanks!

Erik A
  • 31,639
  • 12
  • 42
  • 67
lokheart
  • 23,743
  • 39
  • 98
  • 169
  • please, add error handler to your sub and write please error description. On what line do you get that error? Also check your query string. It seems you're missing spaces before FROM and WHERE. – hgulyan Jun 28 '10 at 05:09
  • I think you'd do well to break that query into a few more lines. – Brian Hooper Jun 28 '10 at 15:22

1 Answers1

2

This may be something obvious, but if your SQL string is writen in your code exactly as you write it here, you need an aditional space before FROM and WHERE.

Another way to find out what is going on is to print the SQL string, using Debug.Print strStatement, and copying it to a new blank query object, and see if it works.

Hope this is helpful

Barranka
  • 20,547
  • 13
  • 65
  • 83