Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > visual basic > answers

Syntax Error in FROM clause

Member
  
Join Date: Mar 2008
Posts: 93
#1: Dec 22 '08
Can anyone tell me how to code the follow to avoid the Syntax Error in FROM cause. I know it is because of my apostophe but I can not change this file name. I got around it once before but can't find how I did it.

Thanks in advance

Expand|Select|Wrap|Line Numbers
  1.  
  2. DELETE *
  3. FROM tblRecs IN 'S:\Current\Montey's Restaurant, Inc\Montey's Restaurant\Montey's Restaurant.mdb';
  4.  
  5.  
danp129's Avatar
Expert
  
Join Date: Jul 2006
Posts: 250
#2: Dec 22 '08

re: Syntax Error in FROM clause

It's a good idea to mention what database you're using when asking for query help. You have a few problems from what I can tell.

1) Apostrophes should be doubled when there is a single apostrophe in the text you're looking for... So if you were wanting to find text in a column that has "Montey's Street" in it you would change it to "Montey''s Street".


2) I don't know that you can specify a table like that in the query (I never had but never used Access much) but I could be wrong. Typically you would open a database and run the query, or specify the database in a connection string and run the query through code. If you are working off an example that actually works, doubling the single apostrophes would fix it.

Expand|Select|Wrap|Line Numbers
  1. DELETE * 
  2. FROM tblRecs IN ('S:\Current\Montey''s Restaurant, Inc\Montey''s Restaurant\Montey''s Restaurant.mdb'); 
  3.  
QVeen72's Avatar
Moderator
  
Join Date: Oct 2006
Location: Bangalore
Posts: 1,385
#3: Dec 23 '08

re: Syntax Error in FROM clause

Hi,

Open the said database, and Execute the query:

Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As Database
  2. Set MyDB =OpenDatabase("S:\Current\Montey's Restaurant, Inc\Montey's Restaurant\Montey's Restaurant.mdb'")
  3. MyDB.Execute "DELETE *  FROM tblRecs "
  4. MyDb.Close
  5.  
Regards
Veena
Member
  
Join Date: Mar 2008
Posts: 93
#4: Dec 30 '08

re: Syntax Error in FROM clause

Expand|Select|Wrap|Line Numbers
  1.  FROM tblRecs IN ('S:\Current\Montey''s Restaurant, Inc\Montey''s Restaurant\Montey''s Restaurant.mdb'); 
worked perfectly thanks. Sorry with the holidays this is the first I got back to it.

Thanks again.
Member
  
Join Date: Mar 2008
Posts: 93
#5: Dec 30 '08

re: Syntax Error in FROM clause

Sorry spoke to soon. When I tried to run the query it said it was the wrong path. Still having a problem.
debasisdas's Avatar
Moderator
  
Join Date: Dec 2006
Location: Bangalore ,India
Posts: 7,507
#6: Dec 30 '08

re: Syntax Error in FROM clause

Quote:

Originally Posted by clloyd View Post

Sorry spoke to soon. When I tried to run the query it said it was the wrong path. Still having a problem.

did you try as suggested in post #3 ?
Member
  
Join Date: Mar 2008
Posts: 93
#7: Jan 7 '09

re: Syntax Error in FROM clause

Yes (Post #3) I received the error message "Invalid SQL Statement 'Delete' , 'Insert', 'Procecedure', 'Select', OR 'Update'
debasisdas's Avatar
Moderator
  
Join Date: Dec 2006
Location: Bangalore ,India
Posts: 7,507
#8: Jan 7 '09

re: Syntax Error in FROM clause

try the following

Expand|Select|Wrap|Line Numbers
  1. con.Begintrans         'con---ADODB connection object.
  2. con.execute "delete from table_name where condition"
  3. con.Committrans
  4.  
  5.  
QVeen72's Avatar
Moderator
  
Join Date: Oct 2006
Location: Bangalore
Posts: 1,385
#9: Jan 8 '09

re: Syntax Error in FROM clause

Hi,

Can you check the Path again..? If the error says, Wrong Path, then there must be some problem with the Path, (May be An Extra Space or so..)

Regards
Veena
Member
  
Join Date: Mar 2008
Posts: 93
#10: Jan 8 '09

re: Syntax Error in FROM clause

The path is correct. It is the apostrophe in the clients name that is causing the problem and I don't know how to get around it. You can not [ ] around the path or the name that I can find that will work.
QVeen72's Avatar
Moderator
  
Join Date: Oct 2006
Location: Bangalore
Posts: 1,385
#11: Jan 9 '09

re: Syntax Error in FROM clause

Hi,

Whatever I suggested in my First Post works Perfectly,
Open DAO database, and execute Query ,
I have Tested with All kind of special characters, Apostrophe, Space etc..

Regards
Veena
Member
  
Join Date: Mar 2008
Posts: 93
#12: Jan 12 '09

re: Syntax Error in FROM clause

Are you referring to


Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim MyDB As Database 
  3. Set MyDB =OpenDatabase "S:\Current\Montey's Restaurant, Inc\Montey's Restaurant\Montey's Restaurant.mdb'") 
  4. MyDB.Execute "DELETE *  FROM tblRecs " 
  5. MyDb.Close 
  6.  

?

I receive the following error as I mentioned before: Invalid SQL Statement; expected 'Delete', 'Insert', 'Procedure', 'Select', or 'Update'
QVeen72's Avatar
Moderator
  
Join Date: Oct 2006
Location: Bangalore
Posts: 1,385
#13: Jan 13 '09

re: Syntax Error in FROM clause

Hi,

Remove * from the query :

MyDB.Execute "DELETE FROM MyTable"

Regards
Veena
Member
  
Join Date: Mar 2008
Posts: 93
#14: Jan 13 '09

re: Syntax Error in FROM clause

How does it then know what table to delete from?
debasisdas's Avatar
Moderator
  
Join Date: Dec 2006
Location: Bangalore ,India
Posts: 7,507
#15: Jan 14 '09

re: Syntax Error in FROM clause

that will delete all the records from the table
Member
  
Join Date: Mar 2008
Posts: 93
#16: Jan 14 '09

re: Syntax Error in FROM clause

So you are saying the code would read as follows:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As Database  
  2. Set MyDB =OpenDatabase "S:\Current\Montey's Restaurant, Inc\Montey's Restaurant\Montey's Restaurant.mdb'")  
  3. MyDb.Close
  4.  
I have numerous tables in this database. I still do not understand how it knows which table to delete from reading the code as I have it above. I am not an expert so if I am missing something I apologize.
Member
  
Join Date: Mar 2008
Posts: 93
#17: Jan 14 '09

re: Syntax Error in FROM clause

I went ahead and tried it removing MyDB.Execute "DELETE FROM MyTable" and still got the Invalid SQL Statement; expected 'Delete', 'Insert', 'Procedure', 'Select', or 'Update' error
debasisdas's Avatar
Moderator
  
Join Date: Dec 2006
Location: Bangalore ,India
Posts: 7,507
#18: Jan 14 '09

re: Syntax Error in FROM clause

I think in your code MyTable is the name of the table.
Member
  
Join Date: Mar 2008
Posts: 93
#19: Jan 15 '09

re: Syntax Error in FROM clause

No matter what I do this code does not work. I will keep searching.
debasisdas's Avatar
Moderator
  
Join Date: Dec 2006
Location: Bangalore ,India
Posts: 7,507
#20: Jan 15 '09

re: Syntax Error in FROM clause

do you understand the code ,you have written
I don't think you understand the difference between a database and a table.
Please use ADO and follow as per post#8
Member
  
Join Date: Mar 2008
Posts: 93
#21: Jan 15 '09

re: Syntax Error in FROM clause

No I don't understand post 8. I am not an expert programmer. I do understand however the difference between a table and a database though. That was a little harsh.
debasisdas's Avatar
Moderator
  
Join Date: Dec 2006
Location: Bangalore ,India
Posts: 7,507
#22: Jan 15 '09

re: Syntax Error in FROM clause

try something like this

Expand|Select|Wrap|Line Numbers
  1. Dim CON As New ADODB.Connection 
  2.  
  3. Private Sub Command1_Click() 
  4. CON.Open "Provider=MSDAORA.1;Password=DEBASIS;User ID=DEBASIS;Data Source=DAS;Persist Security Info=True" --change to your own connectionstring
  5. CON.BEGINTRANS
  6. CON.EXECUTE "DELETE FROM TABLE1"  --USE YOUR DELETE STATMENT HERE
  7. CON.COMMITTRANS
  8. Endsub
  9.  
QVeen72's Avatar
Moderator
  
Join Date: Oct 2006
Location: Bangalore
Posts: 1,385
#23: Jan 16 '09

re: Syntax Error in FROM clause

Hi

Where are you writing the Code..? in VB6 or in VBA...?

Regards
Veena
Member
  
Join Date: Mar 2008
Posts: 93
#24: Jan 22 '09

re: Syntax Error in FROM clause

VBA. Sorry to take so long I was on other projects.
Reply

« previous question | next question »

Similar Visual Basic 4 / 5 / 6 bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,383 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved