Hello,

I am looking for some help with the following code:



The error Run-time error 3131 Syntax error in FROM clause.

Thank you for any help.

Rick

Well, I think you have more than one error in your SQL.
My first step would be to take a look at the Access help (Alt+F11 and then Alt+F1) for "Insert Into".
There you can find some very good examples and methods for using this.
I'm telling you this because I think you will find interesting to use a different kind of Insert Into SQL.
Also, it will be better if you use CurrentDb.Execute (db.Execute in your case) than the DoCmd.RunSQL
Regards,
Diego

Hi Rick,

What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

Try only specifying the field names and moving the rs!Name outside of the quotes.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Hi theDBguy,

I don't think that will work, but I may be wrong.

From my point of view, rpes is combining 2 different methods.

If he wants to pass line by line, the loop is right BUT he should use an Instert Into tbl (fld1, fld2, fld3, fldn) Values (val1, val2, val3, valn)

On the other hand if he wants to pass All the data he can still use the Insert Into Select without needing any loop.

Also he should have to put every variable outside of the string and concatenate it, something like:



All the best,

Diego

PS: That's the reason I recommend him to read the help (IMG:style_emoticons/default/wink.gif)

This post has been edited by dipetete: May 6 2012, 01:41 AM

Thank you for the replies so far,

The database is an Access 2010 database. The recordset that is returned is a list of linked tables. There is only one field. So the first return (rs!Name) in the loop would be tbl1. The next would be tbl2 - and so on. If you go through the SQL, all the rs!Name's would equal tbl1 on the first pass, tbl2 on the second, etc. I hope this helps.

Rick

I don't think this query is filling your recordset the way you want.

My advice is to use the query builder for creating a working query, and then use the SQL created by the query builder in VBA.
I can't see how those fields are related to each other, if what you are saying about that they just have one field is correct.
Regards,
Diego

Diego,

Thank you for taking this on. The recordset is correct. I used the query builder and the return is correct. The SQL in the loop is the problem.

The debugger shows a yellow arrow pointing at the line - "FROM " & rs!TName & ";"

While still in debug mode I moused over the line and it says, rs!TName = "lnk_SUPPORT PP 12"

That is correct except for the quotes. The table name is lnk_SUUPORT PP 12 not "lnk_SUPPORT PP 12"

I may also need to get brackets in there because of the space in the table name.

Thanks again.

Rick

Let's do this step by step.

First we need is to see how your SQL looks like.

For that do the following:

And put the result of the immediate window here.

Regards,

Diego

This post has been edited by dipetete: May 6 2012, 03:02 AM

Diego,

Nothing is returned



This is now what I have

This post has been edited by rpes: May 6 2012, 03:14 AM

Ok.

Let's do that again.

By now, forget everything related with execution.

First we need to know are 2 things:
-. The structure and syntax of the SQL
-. The resulting recordset.

So, just replace the expression you have for the one I put (it is wrong, but I want you to learn how to evaluate your own expressions)

Regards,

Diego

I copied your code into the immediate window and nothing was returned. I then replaced my SQL with yours and tried to run by clicking the command button and still get Syntax error in FROM clause.

Rick

Rick,

If you are doing what I told you, no syntax error will appear, because that is just going to return the SQL string into the immediate window.

Remove your DoCmd.RunSQL please.

Regards,

Diego

Ok, that's done. The immediate window shows the strSQL statement.

Rick

¿Can you please post it?

Here's the code:



Here's the Immediate Window:

INSERT INTO tblStep1 ( EID, Sun1, Mon1, Tue1, Wed1, Thu1, Fri1, Sat1, Sun2, Mon2, Tue2, Wed2, Thu2, Fri2, Sat2 ) SELECT rs!Name.F1, rs!Name.F3, rs!Name.F4, rs!Name.F5, rs!Name.F6, rs!Name.F7, rs!Name.F8, rs!Name.F9, rs!Name.F10, rs!Name.F11, rs!Name.F12, rs!Name.F13, rs!Name.F14, rs!Name.F15, rs!Name.F16 FROM rs!Name;

Ok.

Now change your strSQL for this:



This post has been edited by dipetete: May 6 2012, 04:38 AM

Done. I got a Compile Error with ";" highlighted. Says Expected: end of statement. If I change " FROM " & rst.Name ";" to " FROM " & rst.Name & ";" there's no compile error.

Rick

Great!

If you don't mind I would like to see your resulting SQL string (that of the immediate window), just in case.

Regards,

Diego

I get Run-time error 3265 - Item not found in this collection. I'm confused because the recordset only has one field, TName.
rs!F1 is not a field in the recordset. Between our replies I was able to get the proper name into the immediate window. The problem is the quotes.

I'll try to explain better. I'm using a copy of the database so as to not lose anything. The recordset equals table names. The first table in my recordset is lnk_CARGO SUPPORT PP 12.

The FROM clause needs to say FROM lnk_CARGO SUPPORT PP 12. What's being passed however is FROM "lnk_CARGO SUPPORT PP 12". For some reason I cannot get rid of the quotes.

Everything else works so the hangup is that FROM clause. So the statement "FROM " & rs!TName & ";" need to be fixed. I appreciated your patience with this.

Rick

No problem Rick,
I thought that was going to happen, so I was waiting for your response.
Comment your SQL (you know by putting an ' at the beginning of the line).
Put this in the loop.

Put the results of your immediate window here.
What we are going to do next is to print the Field names of your recordset.
...
Regards,
Diego