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 BarronAuthor, Web DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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, Web DeveloperAuthor 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, Web DeveloperAuthor Commented:
Thanks again.

Carrzkiss
0
Wayne BarronAuthor, Web DeveloperAuthor 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, Web DeveloperAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.