Solved

80040e37 Invalid object name 'OMID'.

Posted on 2009-06-27
10
338 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:Wayne Barron
  • 4
  • 4
  • 2
10 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 24730025
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24730027
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24730029
Good point I did not catch that.  Yes, the syntax for DELETE and UPDATE are different in T-SQL.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24730031
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
 
LVL 30

Author Comment

by:Wayne Barron
ID: 24730032
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 30

Author Closing Comment

by:Wayne Barron
ID: 31597635
Thanks again.

Carrzkiss
0
 
LVL 30

Author Comment

by:Wayne Barron
ID: 24730041
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24730042
>>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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24730048
Glad that helped, Carrzkiss.

Regards,
Kevin
0
 
LVL 30

Author Comment

by:Wayne Barron
ID: 24730061
@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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now