Problem running delete query on remote server

[John]

Hi

My app's connection is with an access database but I am running the below
query to delete records on a remote sql server;

DELETE [My Table].*
FROM [ODBC;Driver=SQL
Server;SERVER=mysqlserver.net;DATABASE=mydatabase;UID=myusername;PWD=mypassword].[My
Table]
WHERE [My Table].ID = 3

I am getting a 'Could not delete from the specified tables' error and
explanation is 'In order to evaluate an indexed property, the property must
be qualified and the arguments must be explicitly supplied by the user.'.
The Insert and Select queries on the remote server work fine from within
Access. Also as I can delete the records manually form the remote server I
presume there are no permissions issues. What is the problem and how can I
fix it? Detailed stack trace is given at the end below.

Thanks

Regards


System.Data.OleDb.OleDbException was unhandled
ErrorCode=-2147467259
Message="Could not delete from specified tables."
Source="Microsoft JET Database Engine"
StackTrace:
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior,
Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method) at
System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at
IV.clsStaffProcessSiteDB.ProcessStaffSiteDB() in C:\HS Comm Server 2008\HS
Comm Server\clsStaffProcessSiteDB.vb:line 216 at
IV.modMain.ProcessStaff() in C:\HS Comm Server 2008\HS Comm
Server\modMain.vb:line 23 at IV.modMain.Process() in C:\HS Comm Server
2008\HS Comm Server\modMain.vb:line 115 at
IV.frmHSCommServer.Events_Timer_Tick(Object sender, EventArgs e) in C:\HS
Comm Server 2008\HS Comm Server\frmHSCommServer.vb:line 52 at
System.Windows.Forms.Timer.OnTick(EventArgs e) at
System.Windows.Forms.Timer.TimerNativeWindow.WndProc(Message& m) at
System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg,
IntPtr wparam, IntPtr lparam) at
System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) at
System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32
dwComponentID, Int32 reason, Int32 pvLoopData) at
System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32
reason, ApplicationContext context) at
System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason,
ApplicationContext context) at
System.Windows.Forms.Application.Run(ApplicationContext context) at
Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at
Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at
Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[]
commandLine) at IV.My.MyApplication.Main(String[] Args) in
17d14f5c-a337-4978-8281-53493378c1071.vb:line 81 at
System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args) at
System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence
assemblySecurity, String[] args) at
Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at
System.Threading.ThreadHelper.ThreadStart_Context(Object state) at
System.Threading.ExecutionContext.Run(ExecutionContext executionContext,
ContextCallback callback, Object state) at
System.Threading.ThreadHelper.ThreadStart()
InnerException:

 

[Norman Yuan]

Not exactly an answer to your question. Just felt how strange the way you
access SQL Server from your .NET app (Win form, or ASP.NET?).

If I am not wrong, the access database (*.mdb) is just an frontend app that
linked to SQL Server backend via ODBC. In a sense, it is not much different
from your .NET app. So, your app reaches the SQL Server this way: your
app->the *.mdb file (Jet engine->ODBC)->SQL Server.

I am wondering, why you need to take such overloaded route froom your .NET
app to access the SQL Server, especially, from your deleting code, you are
not doing Access automation, you merely want to go to the SQLServer to
update it?

You can just use System.Data.SqlClient to directly connect your .NET app to
the SQL Server (which is the most optimized connection to SQL Server).

Hi

My app's connection is with an access database but I am running the below
query to delete records on a remote sql server;

DELETE [My Table].*
FROM [ODBC;Driver=SQL
Server;SERVER=mysqlserver.net;DATABASE=mydatabase;UID=myusername;PWD=mypassword].[My
Table]
WHERE [My Table].ID = 3

I am getting a 'Could not delete from the specified tables' error and
explanation is 'In order to evaluate an indexed property, the property
must be qualified and the arguments must be explicitly supplied by the
user.'. The Insert and Select queries on the remote server work fine from
within Access. Also as I can delete the records manually form the remote
server I presume there are no permissions issues. What is the problem and
how can I fix it? Detailed stack trace is given at the end below.

Thanks

Regards


System.Data.OleDb.OleDbException was unhandled
ErrorCode=-2147467259
Message="Could not delete from specified tables."
Source="Microsoft JET Database Engine"
StackTrace:
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior,
Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method) at
System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at
IV.clsStaffProcessSiteDB.ProcessStaffSiteDB() in C:\HS Comm Server 2008\HS
Comm Server\clsStaffProcessSiteDB.vb:line 216 at
IV.modMain.ProcessStaff() in C:\HS Comm Server 2008\HS Comm
Server\modMain.vb:line 23 at IV.modMain.Process() in C:\HS Comm Server
2008\HS Comm Server\modMain.vb:line 115 at
IV.frmHSCommServer.Events_Timer_Tick(Object sender, EventArgs e) in C:\HS
Comm Server 2008\HS Comm Server\frmHSCommServer.vb:line 52 at
System.Windows.Forms.Timer.OnTick(EventArgs e) at
System.Windows.Forms.Timer.TimerNativeWindow.WndProc(Message& m) at
System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32
msg, IntPtr wparam, IntPtr lparam) at
System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) at
System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32
dwComponentID, Int32 reason, Int32 pvLoopData) at
System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32
reason, ApplicationContext context) at
System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32
reason, ApplicationContext context) at
System.Windows.Forms.Application.Run(ApplicationContext context) at
Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at
Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at
Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[]
commandLine) at IV.My.MyApplication.Main(String[] Args) in
17d14f5c-a337-4978-8281-53493378c1071.vb:line 81 at
System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args) at
System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence
assemblySecurity, String[] args) at
Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at
System.Threading.ThreadHelper.ThreadStart_Context(Object state) at
System.Threading.ExecutionContext.Run(ExecutionContext executionContext,
ContextCallback callback, Object state) at
System.Threading.ThreadHelper.ThreadStart()
InnerException: