error 3075 when running SQL in access 2007 vba

Question

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!

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
– hgulyan, Commented Jun 28, 2010 at 5:09
I think you'd do well to break that query into a few more lines. — Brian Hooper
– Brian Hooper, Commented Jun 28, 2010 at 15:22

Barranka · Accepted Answer · 2010-06-28 21:31:58Z

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

answered Jun 28, 2010 at 21:31

Barranka

21.1k14 gold badges72 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

error 3075 when running SQL in access 2007 vba

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related