80040e37 Invalid object name 'OMID'.

Hello All;

  I am use to being the one that answers questions, and now I am the asker (Again)

OK.
This is the statement
MessageDelSQL = "DELETE OMID FROM OrgMessage WHERE OMID="&DelRec&""
ObjConn.execute MessageDelSQL

This works as suppose to work with Access, so I know that all fields exist and the table name is accurate.

What I have read up on states that (Either the field or table name is misspelled) Not in this case.
The connection is accurate, as everything else works on the site.

Also, that to make sure that the User in the querystring has the proper permission to perform the action, And that person does.

So.
Would could the problem be?

Thanks all.
Carrzkiss
LVL 31
Wayne BarronAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Is this another SQL statement you are converting to MS SQL Server.  If so, you will need to change the syntax to this for a delete statement.
MessageDelSQL = "DELETE FROM OrgMessage WHERE OMID="&DelRec&""
ObjConn.execute MessageDelSQL

Open in new window

0
 
Anthony PerkinsCommented:
>>so I know that all fields exist and the table name is accurate.<<
You need to make sure the connection is pointing to the right server and database and that the table exists and is owned by dbo.

>>Also, that to make sure that the User in the querystring has the proper permission to perform the action<<
They would get a different error message.
0
 
Anthony PerkinsCommented:
Good point I did not catch that.  Yes, the syntax for DELETE and UPDATE are different in T-SQL.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Anthony PerkinsCommented:
And you can actually abbreviate it to (The FROM in DELETE FROM is optional, as is INTO in INSERT INTO)
MessageDelSQL = "DELETE OrgMessage WHERE OMID="&DelRec&""
0
 
Wayne BarronAuthor Commented:
Yep.
You ROCK!
Thanks.

So in SQL Server you only add in the FieldName in the WHERE and not before.

OK.
That is simple enough.
Thanks a bunch, worked like a charm.

Not really sure how much more I have to convert over.
Keep an eye out.

Carrzkiss
0
 
Wayne BarronAuthor Commented:
Thanks again.

Carrzkiss
0
 
Wayne BarronAuthor Commented:
Thanks AC.
mwvisa1: provided what I needed to get this one done.

I did some searching prior to posting the question here.
And I just totally missed the part about not having the FieldName in the SQL Statement.
I feel foolish now really.

Thanks guys.
Carrzkiss
0
 
Anthony PerkinsCommented:
>>So in SQL Server you only add in the FieldName in the WHERE and not before.<<
It is not that way in the MS Access SQL dialect either.  Here is the syntax for MS Access:

DELETE [table.*]
FROM table
WHERE criteria

0
 
Kevin CrossChief Technology OfficerCommented:
Glad that helped, Carrzkiss.

Regards,
Kevin
0
 
Wayne BarronAuthor Commented:
@AC
I have always done it with the fieldname in Access SQL.
Never got a bark back from it, Never knew that it was not done that way.
Learned from someone else's code on the Delete Statement. So was actually taught incorrectly.
(Learn something new every day don't we?)
Knowledge Is Power. More Knowledge, More Power!

Thanks Guys.
Carrzkiss
0
All Courses

From novice to tech pro — start learning today.