Debug Indicates a Specified Table is not defined

[Ed Bitzer]

The following query used in my VBA code will not compile indicating the Table Security is not available. I can sucessfully run this query under the Design window and the same SQL shows values for all Tables and Fields requested.



Code:
strSql = "SELECT mtShrs.ID, mtShrs.Shrs, mtShrs.Payments, mtSelectedRates.Rate, mtShrs.Pays, SECURITY.Invested INTO mtProjectedforMonth FROM mtShrs INNER JOIN SECURITY ON mtSelectedRates.fgnID = SECURITY.ID INNER JOIN mtSelectedRates ON mtShrs.ID = mtSelectedRates.fgnID;"

Set MySet = MyDb.OpenRecordset(strSql) 'open the query
MySet.MoveFirst
MySet![Pays] = Security![Invested]

My code after MoveFirst cycles down the rows displaying all selected fields from the three tables.

Appreciate

Ed

[Welshgasman]

Just what exactly are you trying to accomplish?

[isladogs]

Try removing Security in the final line of the code above

[Ed Bitzer]

As for what I am trying to accomplish it is to query the three folders for specified fields. Works find if I run in the Immediate Window with its SQL syntax or with the SQL syntax created in the Query Design Windows. I also need to refer to the Security table updating the Invested field so no choice there.

I have discovered that Security is a reserved word and wrote this program about 30 years ago. I also read that if you enclose in square brackets this will make it clear to the program. Have tried every permutation and combination of brackets, no brackets, explanation make and period - all to no avail.

Ed

[CJ_London]

assuming this is access sql, think you need some brackets in your from clause which highlights an issue

.....FROM (mtShrs INNER JOIN SECURITY ON mtSelectedRates.fgnID = SECURITY.ID) INNER JOIN mtSelectedRates ON mtShrs.ID = mtSelectedRates.fgnID;"

try

..... FROM (mtShrs INNER JOIN mtSelectedRates ON mtShrs.ID = mtSelectedRates.fgnID) INNER JOIN SECURITY ON SECURITY.ID = mtSelectedRates.fgnID;"

or


..... FROM (mtShrs INNER JOIN mtSelectedRates ON mtShrs.ID = mtSelectedRates.fgnID) INNER JOIN SECURITY ON SECURITY.ID = mtShrs.ID;"


Never tried it but surprised openrecordset works with an action query, would usually use mydb.execute (sqlstr)

[Welshgasman]

Wouldn't that be replace Security with MySet ?

[Welshgasman]

As for what I am trying to accomplish it is to query the three folders for specified fields. Works find if I run in the Immediate Window with its SQL syntax or with the SQL syntax created in the Query Design Windows. I also need to refer to the Security table updating the Invested field so no choice there.

I have discovered that Security is a reserved word and wrote this program about 30 years ago. I also read that if you enclose in square brackets this will make it clear to the program. Have tried every permutation and combination of brackets, no brackets, explanation make and period - all to no avail.

Ed

No, I think Colin hit the nail on the head.
Nothing wrong with your query if you can run it in the SQL window?

It is because you are reference a domain called Security in your code and that domain is not defined as such. It should be MySet I believe.

I do not see Security at https://learn.microsoft.com/en-us/tr...reserved-words or at http://allenbrowne.com/AppIssueBadWord.html

Daniel Pinault has even created an automated way to check. https://www.devhut.net/ms-access-reserved-word-checker/

[June7]

Build with Design View and Access will include appropriate parens and JOIN syntax. Then copy/paste from SQL View.

I don't see how Query builder could produce that SQL. The Designer does not create that syntax for me. I tried that syntax from SQL View and it will not execute.

So how did you get that SQL? Shouldn't it look like:

SELECT mtShrs.ID, mtShrs.Shrs, mtShrs.Payments, mtSelectedRates.Rate, mtShrs.Pays, SECURITY.Invested INTO mtProjectedforMonth
FROM mtShrs INNER JOIN (SECURITY INNER JOIN mtSelectedRates ON SECURITY.ID=mtSelectedRates.fgnID) ON mtShrs.ID=mtSelectedRates.fgnID;

Also, SELECT ... INTO creates a table. Cannot use that to Set a recordset.

An UPDATE action query could be used to set a field value of existing record(s). Possibly no need for looping recordset.

Why do you need to duplicate Invested data to Pays?

[Ed Bitzer]

I believe CJ Londen did hit a major type mistake on my part - failed to carry the parenthesis in the om the first FROM statement. However still having the problem with debugging stopping one the first reference to the Security table after opening and Moving First. I am sorry about my subject to witch should have been Variable not defined. I know I am improperly posting code but cannot find the icon mentioned so bear with me. I have also added the "Dim's" to the corrected code below:

Dim MyDb As Database
DimMySet As Recordset
Dim tmpShrs As Recordset
Dim strSql As String
Set MyDb = CurrentDb()
strSql = "SELECT mtShrs.ID, mtShrs.SecType, mtShrs.Shrs, mtShrs.FaceValue, mtShrs.Payments, mtSelectedRates.Rate, mtShrs.Pays, SECURITY.Invested INTO mtProjectedforMonth FROM (mtShrs INNER JOIN mtSelectedRates ON mtShrs.ID = mtSelectedRates.fgnID) INNER JOIN SECURITY ON SECURITY.ID = mtShrs.ID;"

MyDb.Execute (strSql) 'Tried London's suggestion first
Set MySet = MyDb.OpenRecordset (strSql) ' then my original again - produced error
MySet!MoveFirst MySet![Pays] =[Security]]![Invested ' This is the security that is highlighted on the error display
I read that MyDb.execute(sqlstr) is used to execute action queries that modify data or database structure without returning a recordset modify data or database structure without returning a recordset.
I also read that for Set MySet = MyDb.OpenEcordset(strsql) is used to execute select queries or queries that return records. It retrieves the data and populates the Recordset object allowing you to iterate through the recordset.

I am persistent and even though 92, "we" will get there.
Ed

[CJ_London]

MyDb.Execute (strSql) 'Tried London's suggestion first

assuming your sql is valid, your destination table would be created/populated. Are you saying it isn’t?

and have you tried building the query in the QBE and then looking at the sql [QUOTE]I also read that for Set MySet = MyDb.OpenEcordset(strsql) is used to execute select queries or queries that return records. It retrieves the data and populates the Recordset object allowing you to iterate through the recordset.[\QUOTE]

agreed, but your sql is not a select query, it is an action query

[Welshgasman]

It is the # icon? :(

Read post #7 again, please.

[isladogs]

Just for clarification, my suggestion in post #3 was:

strSql = "SELECT mtShrs.ID, mtShrs.Shrs, mtShrs.Payments, mtSelectedRates.Rate, mtShrs.Pays, SECURITY.Invested INTO mtProjectedforMonth FROM mtShrs INNER JOIN SECURITY ON mtSelectedRates.fgnID = SECURITY.ID INNER JOIN mtSelectedRates ON mtShrs.ID = mtSelectedRates.fgnID;"

Set MySet = MyDb.OpenRecordset(strSql) 'open the query
MySet.MoveFirst
MySet![Pays] = !Invested

However, I agree with the earlier comments that using SELECT . . . INTO won't work. You haven't created the table and don't have data for your recordset.

[June7]

Ed, did you read ALL of post 8?

[Ed Bitzer]

I can see that although I have been programming in Access for just about when it was first issued (self taught with a couple of night school classes along the way) and simply because I needed some database programs for my small manufacturing company and I as Chief Metallurgist did not have sufficient justification to hire trained programmer. After I retired 32 years ago it became a hobby and this was a early program I wrote tat handled all my finances - a custom Quick Books for us. I offer all this peripheral information to show why I appreciate all the effort all of you are giving to help - and it is taking some time for me to digest so much info.

This reply is directed to to June7 and his suggestions/questions in the 8th tread message.. Yes I creating my SQL using DesignView and first testing the SQL created there by selecting DataSheet View. The latter in mycase with my data generates 415 records including all the fields specified from the three Tables - Security, mtShrs and mtSelectedRates.

Now June7 reminds that this is an action query am now Executing the query string copied for the Designer - Specifically:
strSql = "SELECT mtShrs.ID, mtShrs.SecType, mtShrs.Shrs, mtShrs.Payments, mtShrs.FaceValue, mtShrs.Pays, mtSelectedRates.Rate, SECURITY.Invested
FROM (mtShrs INNER JOIN mtSelectedRates ON mtShrs.ID = mtSelectedRates.fgnID) INNER JOIN SECURITY ON mtSelectedRates.fgnID = SECURITY.ID;"
'open the action query '
MyDb.Execute (strSql)
MySet.MoveFirst
MySet![Pays] = [Security]![Invested]

with the last line here where the Error still occurs indicating t"External name not defined" and highlighting Security.

I have corrected the necessary brackets in the first FROM statememts and used Execute, as several have noted, to do just that with my action query.

Why is just the table Security table causing the error the other two tables, mtShrs and mtSelectedRates, accepted?

Ed

[June7]

Don't use Execute to open a recordset object. Execute is only for action SQL.

Now that the SQL is just a SELECT (without INTO), use SET to establish recordset as you originally did.

Did not respond to my question about duplicating data.

If you MUST duplicate data, why not just an UPDATE sql instead of looping recordset?