Board index » delphi » Can't use CurrentUser function in Access query with BDE

Can't use CurrentUser function in Access query with BDE

I have defined an Access database with some row-restricted views, where the
row restriction is a join on a table with the User's id. This enables the
recordset to be unconditionally restricted, by user id. I have tested this
behavior in Access and it works fine.

here is a simplified example:

SELECT * FROM MyTable
WHERE UserName = [CurrentUser];

Running this query displays only records where the UserName field contents
are the same as the username of the logged in user.

Unfortunately, when I try to run these queries in the Borland Database
Engine version 4 (with native support for Access), I get an error message
saying something like "1 parameter expected, 0 supplied". In other words,
it seems like the BDE somehow accesses the SQL text, and thinks that a
parameter value needs to be supplied. the behavior happens in my program,
and also when trying to run the query in DBExplorer, such as by SELECT *
FROM MyQuery.

Can anyone offer any suggestions or workaround? Engine-level restriction is
really quite important to our application.

Also, I am really surprised that Database Explorer doesn't even list Access
queries, as it does list Views of SQL databases.

Also 2: The Borland Datapump Wizard is completely useless for trying to
port to Access -- it mostly generates DDL that causes Access to {*word*88}. I
ended up writing my own DB generation within Access, from modified DDL
taken from my master SQL script.

(If responding by email, please note the anti-spam encoding)

--
Brad Aisa     web archive: http://www.interlog.com/~baisa/
email (anti-spam encoded): baisa"AT SYMBOL"interlog.com

"The highest responsibility of philosophers is to serve as the
guardians and integrators of human knowledge."   -- Ayn Rand

 

Re:Can't use CurrentUser function in Access query with BDE


CurrentUser is an access runtime FUNCTION and you aren't
using the runtime or the fullblown access - so no automatic
function.  Since you can't use security anyway, there would
only ever be 'admin' in currentuser anyway.

Try Wnetgetuser and fill the parameter yourself.

Re:Can't use CurrentUser function in Access query with BDE


And so you see Brad, that Borland's native support for Access, really
isn't. The support is actually only support for the tables, not true
support for the Access RDBS.

There is a work around though. CurrentUser() is an Access function based on
the Access security model. Don't use it. Instead, create a form with users
and passwords hard coded into a module.  Use this to validate the logon,
and hide it after a successful logon. You can now refer to the unbound text
box containing the user id:

SELECT * FROM MyTable
WHERE UserName = [Forms]![LogonForm]![UserName];

would be the syntax in Access, I'm not sure of the exact syntax for you
app. The above is for running a query directly from code. BUT if you are
running a querydef object, and I suspect you are from the error message,
you must realize that ALL parameters must be supplied in code. Example:
(watch out for newsreader line wraps)

Dim db As Database
Dim qdef As QueryDef
Set db = CurrentDB()
Set qdef = db.QueryDefs("YourQuery")
qdef.Parameters("Your Parameter Name") = Forms!YourForm!YourField
  ' You can also use qdef.Parameters(0), zero being the first parameter, or
even an array of parameters

qdef.Execute
qdef.Close

--

                   Arvin Meyer

              On-Site Solutions

 "Developing results-oriented databases for companies
   that demand a tangible return on investment."

e-mail: ons...@esinet.net
phone:  (804) 973-9140
http://www.esinet.net/cabg/consult/onsite.html
--------------------------------------------------------------------

Brad Aisa <ba...@ERASETHISinterlog.com> wrote in article
<5qplvo$...@news.interlog.com>...

Quote
> I have defined an Access database with some row-restricted views, where
the
> row restriction is a join on a table with the User's id. This enables the
> recordset to be unconditionally restricted, by user id. I have tested
this
> behavior in Access and it works fine.

> here is a simplified example:

> SELECT * FROM MyTable
> WHERE UserName = [CurrentUser];

> Running this query displays only records where the UserName field
contents
> are the same as the username of the logged in user.

> Unfortunately, when I try to run these queries in the Borland Database
> Engine version 4 (with native support for Access), I get an error message
> saying something like "1 parameter expected, 0 supplied". In other words,
> it seems like the BDE somehow accesses the SQL text, and thinks that a
> parameter value needs to be supplied. the behavior happens in my program,
> and also when trying to run the query in DBExplorer, such as by SELECT *
> FROM MyQuery.

> Can anyone offer any suggestions or workaround? Engine-level restriction
is
> really quite important to our application.

> Also, I am really surprised that Database Explorer doesn't even list
Access
> queries, as it does list Views of SQL databases.

> Also 2: The Borland Datapump Wizard is completely useless for trying to
> port to Access -- it mostly generates DDL that causes Access to {*word*88}. I
> ended up writing my own DB generation within Access, from modified DDL
> taken from my master SQL script.

> (If responding by email, please note the anti-spam encoding)

> --
> Brad Aisa     web archive: http://www.interlog.com/~baisa/
> email (anti-spam encoded): baisa"AT SYMBOL"interlog.com

> "The highest responsibility of philosophers is to serve as the
> guardians and integrators of human knowledge."   -- Ayn Rand

Other Threads