Syntax Error in FROM clause

[clloyd]

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

 
DELETE *
FROM tblRecs IN 'S:\Current\Montey's Restaurant, Inc\Montey's Restaurant\Montey's Restaurant.mdb';
 
 

[danp129]

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.

DELETE * 
FROM tblRecs IN ('S:\Current\Montey''s Restaurant, Inc\Montey''s Restaurant\Montey''s Restaurant.mdb'); 
 

[QVeen72]

Hi,

Open the said database, and Execute the query:

Dim MyDB As Database
Set MyDB =OpenDatabase("S:\Current\Montey's Restaurant, Inc\Montey's Restaurant\Montey's Restaurant.mdb'")
MyDB.Execute "DELETE *  FROM tblRecs "
MyDb.Close
 

Regards
Veena

[clloyd]

 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.

[clloyd]

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

[debasisdas]

@clloyd
did you try as suggested in post #3 ?

[clloyd]

Yes (Post #3) I received the error message "Invalid SQL Statement 'Delete' , 'Insert', 'Procecedure', 'Select', OR 'Update'

[debasisdas]

try the following

con.Begintrans         'con---ADODB connection object.
con.execute "delete from table_name where condition"
con.Committrans
 
 

[QVeen72]

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

[clloyd]

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]

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

[clloyd]

Are you referring to

 
Dim MyDB As Database 
Set MyDB =OpenDatabase "S:\Current\Montey's Restaurant, Inc\Montey's Restaurant\Montey's Restaurant.mdb'") 
MyDB.Execute "DELETE *  FROM tblRecs " 
MyDb.Close 
 

?

I receive the following error as I mentioned before: Invalid SQL Statement; expected 'Delete', 'Insert', 'Procedure', 'Select', or 'Update'

[QVeen72]

Hi,

Remove * from the query :

MyDB.Execute "DELETE FROM MyTable"

Regards
Veena

[clloyd]

How does it then know what table to delete from?

[debasisdas]

that will delete all the records from the table

[clloyd]

So you are saying the code would read as follows:

Dim MyDB As Database  
Set MyDB =OpenDatabase "S:\Current\Montey's Restaurant, Inc\Montey's Restaurant\Montey's Restaurant.mdb'")  
MyDb.Close
 

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.

[clloyd]

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]

I think in your code MyTable is the name of the table.

[clloyd]

No matter what I do this code does not work. I will keep searching.

[debasisdas]

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

[clloyd]

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]

try something like this

Dim CON As New ADODB.Connection 
 
Private Sub Command1_Click() 
CON.Open "Provider=MSDAORA.1;Password=DEBASIS;User ID=DEBASIS;Data Source=DAS;Persist Security Info=True" --change to your own connectionstring
CON.BEGINTRANS
CON.EXECUTE "DELETE FROM TABLE1"  --USE YOUR DELETE STATMENT HERE
CON.COMMITTRANS
Endsub
 

[QVeen72]

Hi

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

Regards
Veena

[clloyd]

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