Problem Executing Saved Access Query in .Net Application

[RMittelman]

I'm trying to execute a query in an Access database from my VS 2015 application. Here is code:

class Program
{
static void Main(string[] args)
{
var dt = LookupOdbc("table");
Console.WriteLine(dt == null ? "null" : dt.ToString());
Console.Write("Press any key to exit...");
Console.ReadKey();
}
public static DataTable LookupOdbc(string dst)
{
const string OdbcConn = @"Dsn=MS Access Database;dbq=C:\folder\DBName.accdb;defaultdir=C:\folder;driverid=25;fil=MS Access;maxbuffersize=2048;pagetimeout=5;uid=admin";
string Status;
string LastError;

try
{
Status = $"Select_Data_Odbc: Getting Connection, connString={OdbcConn}";
using(var conn = new OdbcConnection(OdbcConn))
{
if(conn == null)
{
LastError = "No Connection";
Console.WriteLine(LastError);
return null;
}

Status = "Select_Data_Odbc: Opening connection";
conn.Open();

using(var command = conn.CreateCommand())
{

if(dst == "table")
{
command.Parameters.Add("NAID", OdbcType.Int).Value = 4;
command.Parameters.Add("NALastName", OdbcType.VarChar).Value = "Abrams";
command.CommandType = CommandType.Text;
command.CommandText = "Select * From tblNAMaster Where NAID < ? AND NALastName = ?";

}

else
{
command.Parameters.Add("ID", OdbcType.Int).Value = 4;
command.Parameters.Add("LastName", OdbcType.VarChar).Value = "Abrams";
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "GetMembers";

}
using(var dr = command.ExecuteReader())
{
using(var dt = new DataTable("NAMaster"))
{
dt.Load(dr);
return dt;
}
}
}
}
}

catch(OdbcException ex)
{
LastError = "ODBC Error: "+ ex.Message;
Console.WriteLine(LastError);
return null;
}
catch(Exception ex)
{
LastError = "Error: " + ex.Message;
Console.WriteLine(LastError);
return null;
}

}
}

If I execute the program as above, it returns a DataTable with the expected records.

If I change the calling argument to something besides "table", it should execute the saved query in the database (which works properly within Access, after entering the appropriate parameters).

Instead, it returns this error:

[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

I'm using Office 365, with 64-bit versions of applications installed on my PC. I've tried setting target platform to all available choices, but it doesn't work. Any ideas why I can't execute a saved query using ODBC to the Access database?




Ron Mittelman

Continue reading...