Solved

80040e37 Invalid object name 'OMID'.

Posted on 2009-06-27
10
339 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
datetime in sql 6 29
MSSQL: Replace text (typo) 7 33
How to use Full Text CONTAINS with Case in SQL 6 20
Query / Window function ? 3 18
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

895 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