SQL to VBA Error 3075 - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

SQL to VBA Error 3075

Joe Rapp

3 years ago

Below is some code that is returning the RunTime Error '3075' Syntax Error (missing operator).  I built the SQL to VBA code converter from Richard's Query SQL to VBA course from YouTube and pasted it to the Sub Routine.  My thoughts were that converting an UPDATE query is not allowed in VBA, maybe converting Me.recordsource to DoCmd.RunSQL would work.  I am researching Microsoft Support on How to create Account Sets in FRx.  Hopefully, y'all can provide some insight as well.  Thanks.

   DoCmd.RunSQL "INSERT INTO tbl004_USCanCombined_Full ( Orig, OST, DZIPLOW, DZIPHIGH, DST, CLS, MC, L5C, M5C, M1M, M2M, M5M, M10M, M20M, M30M, M40M )" & _

      "SELECT tbl002_DZips.Orig, tbl002_DZips.OST, tbl002_DZips.DZIPLOW, tbl002_DZips.DZIPHIGH, tbl002_DZips.DST, tbl002_DZips.CLS, tbl002_DZips.MC, tbl002_DZips.L5C, tbl002_DZips.M5C, tbl002_DZips.M1M, tbl002_DZips.M2M, tbl002_DZips.M5M, tbl002_DZips.M10M, tbl002_DZips.M20M, tbl002_DZips.M30M, tbl002_DZips.M40M" & _

      "FROM tbl003_USStatesDim INNER JOIN tbl002_DZips ON tbl003_USStatesDim.StateAbbre = tbl002_DZips.DST;"

	Kevin Robertson @Reply 3 years ago
The first thing I would do is revise your field names so you can tell at a glance what that field represents. FirstName is a good field name. DST, for example, doesn't tell us what that field represents.

	Alex Hedley @Reply 3 years ago
Does the SQL work first in the Query editor?

	Kevin Yip @Reply 3 years ago
The SQL is missing some spaces. Change "SELECT..." to " SELECT...", and "FROM..." to " FROM...".

Gregory Clancey @Reply

3 years ago

It is typical to forget the spaces at the end of SQL lines (or beginning of next) when concatenating strings in construction. It might be best to reformat the code to avoid this. I use a form such as. . .
   CntrlName.RowSource = _ &
      "SELECT firstField, secondField " & _
      "FROM tableName " & _
      "ORDER BY . . .

By using this long way I minimize the common error. The terminating space character delimits the line of code.
Kudos to Kevin for seeing this detail.

Joe Rapp OP @Reply

3 years ago

I am answering all questions to my question here. The field names are defined from a file that is imported and they need to be exported in that format as well.  I understand you were not aware of this and it is something I may tinker with.

Yes, the query works in the query editor.

To make sure I understand.  Example ...M40M) " & _. If so, I will try this as well.

I will review this tomorrow and start working on it.  Any other insights are appreciated.  Thank you.

	Joe Rapp OP @Reply 3 years ago
Okay. So I sent Alex and Kevin R. $5 or 3.72 in gbr. Not sure where everyone is located, but if I could send Venmo's out that would be great. Let me know. Thanks.

	Joe Rapp OP @Reply 3 years ago
Thank you all. Your suggestions worked and then as I continued in another part of the application, I ran across run-time 2342 and 7874. I figured those out on my own and the application is working up to this point. I will continue building it out knowing there are good sources of assistance here.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View