Getting Access vba connection string to identify the user

[blue875]

Ok, I've tried searching, and haven't found the answer, so it must mean
everyone else but me already knows this. I have an Access 2000 form
that I want to connect to an Access 2000 table in the same database.
What I am able to do is create a connection string, explicitly giving
my admin-level userId and password, and it works. But when I try to
create a connection string that doesn't use this explicit definition, I
get permission issues, and no data. How do I get the form (and vba
code) to use the user's permission level to query the tables? We are
using an .MDW file to define user accounts, and the person who created
this app did things the hard way: gave each individual user their own
permissions, instead of user groups.

[David W. Fenton]

bl*****@carolina.rr.com wrote in
news:11*********************@h48g2000cwc.googlegro ups.com:

Ok, I've tried searching, and haven't found the answer, so it must
mean everyone else but me already knows this. I have an Access
2000 form that I want to connect to an Access 2000 table in the
same database. What I am able to do is create a connection string,
explicitly giving my admin-level userId and password, and it
works. But when I try to create a connection string that doesn't
use this explicit definition, I get permission issues, and no
data. How do I get the form (and vba code) to use the user's
permission level to query the tables? We are using an .MDW file
to define user accounts, and the person who created this app did
things the hard way: gave each individual user their own
permissions, instead of user groups.

Just create a link to the table and then bind the form to the
appropriate recordsource.

There is no code needed for this, nor a need to specify a connection
string, except, perhaps in creating the link to the table (if it's
an ODBC data source, for instance).

Sounds to me like you're making things too complicated.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[blue875]

Thanks for the advice. Now let me expand the problem, and ask a couple
other questions as well:

What if the form I have has several tabs, and each tab needs
information from multiple tables? They are all currently linked
tables, but I don't think I can specify one table to any tab, since
multiple tables are needed for each tab. Do I just create a query as
the rowsource for each one?

In this scenario, how much performance gain should I see by using an MS
SQL Server backend instead of an Access database? If an average
"form-fill" time is currently 3-5 seconds, what could I expect from
this move?

Is it generally faster to set the rowsource to the form than using VBA
to connect and query?

Thanks again for your help. I've been working on Access forms for
about six months now, so I still have some things to learn.

David W. Fenton wrote:

bl*****@carolina.rr.com wrote in
news:11*********************@h48g2000cwc.googlegro ups.com:

Ok, I've tried searching, and haven't found the answer, so it must
mean everyone else but me already knows this. I have an Access
2000 form that I want to connect to an Access 2000 table in the
same database. What I am able to do is create a connection string,
explicitly giving my admin-level userId and password, and it
works. But when I try to create a connection string that doesn't
use this explicit definition, I get permission issues, and no
data. How do I get the form (and vba code) to use the user's
permission level to query the tables? We are using an .MDW file
to define user accounts, and the person who created this app did
things the hard way: gave each individual user their own
permissions, instead of user groups.

Just create a link to the table and then bind the form to the
appropriate recordsource.

There is no code needed for this, nor a need to specify a connection
string, except, perhaps in creating the link to the table (if it's
an ODBC data source, for instance).

Sounds to me like you're making things too complicated.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

bl*****@carolina.rr.com wrote in
news:11*********************@74g2000cwt.googlegrou ps.com:

What if the form I have has several tabs, and each tab needs
information from multiple tables? They are all currently linked
tables, but I don't think I can specify one table to any tab,
since multiple tables are needed for each tab. Do I just create a
query as the rowsource for each one?

A subform on each tab, each with its own recordsource?

In this scenario, how much performance gain should I see by using
an MS SQL Server backend instead of an Access database?

Your question is like asking "how long is a piece of string?" There
is simply no way to answer it with the information given. However, I
would say that I've seen and used the design you describe in dozens
of Access databases using Jet back ends that had no performance
problems whatsoever, even with 100s of thousands of records and a
couple dozen simultaneous users.

If an average
"form-fill" time is currently 3-5 seconds, what could I expect
from this move?

I have no idea. That sounds incredibly slow to me to begin with. How
many records are involved?

Is it generally faster to set the rowsource to the form than using
VBA to connect and query?

The fastest is a bound form with a defined recordsource is going to
be fastest. But you have to decide whether your tab form should have
a recordsource at all. Are you presenting data that's child records
of a parent record? If not, then I don't know why you're using a
tabbed form in the first place.

Thanks again for your help. I've been working on Access forms for
about six months now, so I still have some things to learn.

Sounds to me like you're trying to make the whole thing too hard.
Just use plain old point-and-click techniques, with forms and
subforms with recordsources set at design time.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/