Can't edit corrupted query, "Syntax Error in FROM Clause"

[Christopher W. Douglas]

I am having an interesting problem with Access 2000 and Visual Basic.NET.

I am developing an application in VB.NET that uses SQL queries to read an
Access database. To test the queries I am building in the code, I cut and
paste the SQL statement into the Access query builder. If I run the query
in Access (the results are showing in Datasheet view), then run the section
of code (in VB.NET) that calls the same table, I get a lock error from
VB.NET:

Could not lock table '_temp_AircraftToWeapon'; currently in use by user
'Admin' on machine 'DOUGLAS'.

This is what I expect, since Access has that table currently displayed.
However, if I go to Access, close the query, then try to open it (either in
Datasheet view or SQL view), I get a "Syntax Error in FROM Clause" message
box in from of the query window. So, I can see the query behind the error
message box, but when I click "OK", the query window closes. I have tried
repairing the database and importing the query into another database, but it
is still corrupted.

Is there any way to repair this query? I appreciate any help.

This is the offending SQL statement:

SELECT t.Aircraft_ID, t.Weapon_ID, r.mtw_id AS RecordExists, r.[AW]
FROM _temp_AircraftToWeapon AS t
LEFT JOIN (SELECT DISTINCT Aircraft_ID, Weapon_ID, mtw_id, [AW] FROM
Region_Valid) AS r
ON t.Aircraft_ID = r.Aircraft_ID AND t.Weapon_ID = r.Weapon_ID

If it helps, I am using Access 2000 and Visual Studio 2003 on Windows Server
2003.

 

[Michel Walsh]

Hi,



Remove the [] around the AW field name, in the virtual table.


Hoping it may help,
Vanderghast, Access MVP

 

[Christopher W. Douglas]

Thanks for your reply, but the query is not the problem. I can't EDIT the
query, when this error occurs. The error message "Syntax Error in FROM
Clause" appears in FRONT of the query window, and when I click "OK", BOTH
the error massage and the query window close. There is no way for me to
edit the query in its current state, even when I import the query into a
different database.

btw, the reason [AW] is in brackets is because there is another field [AT]
that DOES have to be in brackets because AT is a reserved word. The way I
am building the query in code, it is easier to put brackets on these fields.

--
Christopher W. Douglas
SRS Technologies, Inc.
christopher (dot) douglas (at) srs (dot) com

Hi,



Remove the [] around the AW field name, in the virtual table.


Hoping it may help,
Vanderghast, Access MVP

I am having an interesting problem with Access 2000 and Visual Basic.NET.

I am developing an application in VB.NET that uses SQL queries to read an
Access database. To test the queries I am building in the code, I cut and
paste the SQL statement into the Access query builder. If I run the query
in Access (the results are showing in Datasheet view), then run the
section
of code (in VB.NET) that calls the same table, I get a lock error from
VB.NET:

Could not lock table '_temp_AircraftToWeapon'; currently in use by user
'Admin' on machine 'DOUGLAS'.

This is what I expect, since Access has that table currently displayed.
However, if I go to Access, close the query, then try to open it (either
in
Datasheet view or SQL view), I get a "Syntax Error in FROM Clause" message
box in from of the query window. So, I can see the query behind the error
message box, but when I click "OK", the query window closes. I have tried
repairing the database and importing the query into another database, but
it
is still corrupted.

Is there any way to repair this query? I appreciate any help.

This is the offending SQL statement:

SELECT t.Aircraft_ID, t.Weapon_ID, r.mtw_id AS RecordExists, r.[AW]
FROM _temp_AircraftToWeapon AS t
LEFT JOIN (SELECT DISTINCT Aircraft_ID, Weapon_ID, mtw_id, [AW] FROM
Region_Valid) AS r
ON t.Aircraft_ID = r.Aircraft_ID AND t.Weapon_ID = r.Weapon_ID

If it helps, I am using Access 2000 and Visual Studio 2003 on Windows
Server
2003.

 

[Douglas J. Steele]

If you've got a reference set to DAO, see whether you can reset the SQL that
way.

Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

strSQL = "SELECT t.Aircraft_ID, t.Weapon_ID, " & _
"r.mtw_id AS RecordExists, r.AW " & _
"FROM _temp_AircraftToWeapon AS t " & _
"LEFT JOIN (SELECT DISTINCT Aircraft_ID, " & _
"Weapon_ID, mtw_id, AW FROM " & _
"Region_Valid) AS r " & _
"ON t.Aircraft_ID = r.Aircraft_ID AND " & _
"t.Weapon_ID = r.Weapon_ID"

Set qdfCurr = CurrentDb().QueryDefs("MyMisbehavingQuery")
qdfCurr.SQL = strSQL

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Thanks for your reply, but the query is not the problem. I can't EDIT the
query, when this error occurs. The error message "Syntax Error in FROM
Clause" appears in FRONT of the query window, and when I click "OK", BOTH
the error massage and the query window close. There is no way for me to
edit the query in its current state, even when I import the query into a
different database.

btw, the reason [AW] is in brackets is because there is another field [AT]
that DOES have to be in brackets because AT is a reserved word. The way I
am building the query in code, it is easier to put brackets on these fields.

--
Christopher W. Douglas
SRS Technologies, Inc.
christopher (dot) douglas (at) srs (dot) com

Hi,



Remove the [] around the AW field name, in the virtual table.


Hoping it may help,
Vanderghast, Access MVP

I am having an interesting problem with Access 2000 and Visual Basic.NET.

I am developing an application in VB.NET that uses SQL queries to read an
Access database. To test the queries I am building in the code, I cut and
paste the SQL statement into the Access query builder. If I run the query
in Access (the results are showing in Datasheet view), then run the
section
of code (in VB.NET) that calls the same table, I get a lock error from
VB.NET:

Could not lock table '_temp_AircraftToWeapon'; currently in use by user
'Admin' on machine 'DOUGLAS'.

This is what I expect, since Access has that table currently displayed.
However, if I go to Access, close the query, then try to open it (either
in
Datasheet view or SQL view), I get a "Syntax Error in FROM Clause" message
box in from of the query window. So, I can see the query behind the error
message box, but when I click "OK", the query window closes. I have tried
repairing the database and importing the query into another database, but
it
is still corrupted.

Is there any way to repair this query? I appreciate any help.

This is the offending SQL statement:

SELECT t.Aircraft_ID, t.Weapon_ID, r.mtw_id AS RecordExists, r.[AW]
FROM _temp_AircraftToWeapon AS t
LEFT JOIN (SELECT DISTINCT Aircraft_ID, Weapon_ID, mtw_id, [AW] FROM
Region_Valid) AS r
ON t.Aircraft_ID = r.Aircraft_ID AND t.Weapon_ID = r.Weapon_ID

If it helps, I am using Access 2000 and Visual Studio 2003 on Windows
Server
2003.

 

[Michel Walsh]

Hi,

The virtual table may not work if you have to use [ ] inside it (the
parser seems to become lost). You can maybe use a saved query (rather than a
virtual table) and use that saved query name.



Vanderghast, Access MVP

Thanks for your reply, but the query is not the problem. I can't EDIT the
query, when this error occurs. The error message "Syntax Error in FROM
Clause" appears in FRONT of the query window, and when I click "OK", BOTH
the error massage and the query window close. There is no way for me to
edit the query in its current state, even when I import the query into a
different database.

btw, the reason [AW] is in brackets is because there is another field [AT]
that DOES have to be in brackets because AT is a reserved word. The way I
am building the query in code, it is easier to put brackets on these
fields.

--
Christopher W. Douglas
SRS Technologies, Inc.
christopher (dot) douglas (at) srs (dot) com

Hi,



Remove the [] around the AW field name, in the virtual table.


Hoping it may help,
Vanderghast, Access MVP