Populate a table based on another table

[TraciAnn via AccessMonster.com]

Researching my subject produces a bunch of "You don't want to do that....
redundant data...etc...etc."

I DO want to do this.

I designed my database so the active records would be based on the value of a
single value in a table Filter.Project

This is a numeric value which I would manually change anytime I needed to
"switch" projects. Almost all queries within the db does a DLookup("[Project]
","[Filter]") to display the related records. This works like a charm.

Now, I have users who would like to change this on the fly and add another
filter field.

To simplify matters for the user I thought I would change the structure just
a little, allowing users to select a Project Name rather than trying to
remember projectID's. I also want to use a text column as a string in
situations it will apply.

Project table
ProjectID (PK)
ProjectName
String

Filter table
FilterID (PK)
ProjectID (FK Project)
String

My thought is that Project will contain the full list of projects (currently
at 5) and Filter will be a single record table.

When the User opens the db the opening form will be a popup modal asking the
user to select the project. They choose the project name which updates the
other two fields (ProjectID & String). From that point forward, all queries
are pointing to those variables.

Does this make sense?

The db is split with a SQL BE but this table will be placed on the local FE,
so I don't see a problem with multiple users wanting to see different
projects at the same time.

Your input is appreciated!

 

[Clifford Bass]

Hi TraciAnn,

I have used that method. It may be faster to keep the filter table in
the back end. You could test it to be sure. In order to allow multiple
users to use the table in the back end without one user causing problems for
another user, add a user name column to the filter table that defaults to the
current user's name. How you do that will vary from one type of SQL back end
to another. Actually you can get rid of the FilterID column and just make
the user name column the primary key. This will ensure that each user can
only have one row in the table. Then set up a view that allows each user to
see only his/her own record. Use that view in place of the table in all of
the queries that use the table.

Good Luck,

Clifford Bass

 

[TraciAnn via AccessMonster.com]

Hi Clif! Thanks for the info!

Hmmm. I understand the logic and it looks good. I just may be a wee bit
challenged on putting it together.

add a user name column to the filter table that defaults to the
current user's name.

I am building the tables in an Access BE for Dev but then will push them to
SQL as soon as all tests well. Once the tables are pushed to SQL, "I'm in
Istanbul", meaning - it will all be foreign. However, while on the Ac side I
assume I add the username column, make it the PK and set the default value
(in the table) to "CurrentUser()". Correct?

Then set up a view that allows each user to
see only his/her own record. Use that view in place of the table in all of
the queries that use the table.

I understand what a "view" is in SQL but I've never done this. We have a SQL
developer here that will help me with that though. I will just need to handle
things on the Ac side.

For Dev and testing purposes, how should I handle this until I migrate to SQL?


Thanks for your help Clif!

 

[Piet Linden]

Hi Clif! Thanks for the info!

Hmmm. I understand the logic and it looks good. I just may be a wee bit
challenged on putting it together.


I am building the tables in an Access BE for Dev but then will push them to
SQL as soon as all tests well. Once the tables are pushed to SQL, "I'm in
Istanbul", meaning - it will all be foreign. However, while on the Ac side I
assume I add the username column, make it the PK and set the default value
(in the table) to "CurrentUser()". Correct?


I understand what a "view" is in SQL but I've never done this. We have a SQL
developer here that will help me with that though. I will just need to handle
things on the Ac side.

For Dev and testing purposes, how should I handle this until I migrate toSQL?

Thanks for your help Clif!

--

You might want to get a copy of Chipman and Baron's book, "Microsoft
Access Developer's Guide to SQL Server"... granted, it's an old book
now, but the fundamental choices you need to make about designing your
DB and how to go about it are covered thoroughly, and you can get it
really cheap on Amazon. Well worth the price, I think.

The other part of the upsizing that you need to be aware of is the
difference between Access queries and SQL Server Views and Stored
Procedures. The book covers them pretty well. One thing that might
help you with the upsizing is this... I know it's not perfect, but it
does most of the heavy lifting for you...
http://www.amazecreations.com/datafast/GetFile.aspx?file=DataFastUtility.zip

Once you know what to fix in the converted objects, the database
should convert quickly and easily.

 

[Clifford Bass]

Hi TraciAnn,

Developing it in an Access BE may be problematic as it does not allow
for setting the default value of a table field to CurrentUser(). I usually
find it best to develop directly with the target back end because there are
things that will work with an Access BE that will not with some other BE.
And of course, the other way around. Or things that will work, but in order
to get them to work you have to go about it differently. This may be less
true of SQL Server as the back end. So what I do is use a development
version of the database in the back end server and also have a completely
separate, production version of the database in the back end server. Each
change you make to the development back end you record in a script which then
can be used to make the same changes to the production back end. Doing it
this way can avoid the issue of how to migrate future, post release changes
from an Access BE to some other BE. (I yield to others expertise, especially
with SQL Server, if they know otherwise.)

Also, if you do not have security set up with various users in Access,
the current user will be "Admin" regardless of who the user actually is. You
may have to do the coding in a form--probably in your filter form. The
filter form could be set to filter on the current user. And forms will allow
for setting a default value to CurrentUser(). However, when the user is
connected to the SQL [Server?] back end, I do not think that CurrentUser will
return the SQL [Server] user name. But then in SQL [Server] you should be
able to set the default of a column to the user name. It may require a
before-insert type trigger and/or stored procedure (I am not qualified to
state specifically).

A view in Access is simply a (saved) query. Actually this is true in
other databases. Views are simply stored/saved queries which can be used
instead of tables. Often they are used as a way to limit a person to
specific columns and/or rows of data from a table. The user is then given
access to the view/query, but not to the table. So in what we are describing
here no one would have access to the filters table, only the view. And the
view restricts access based on the user. So you might have something like
for the view to the Access filter table:

select *
from Filter
where User_Name = CurrentUser();

Hopefully that is helpful,

Clifford Bass

 

[TraciAnn via AccessMonster.com]

You might want to get a copy of Chipman and Baron's book, "Microsoft
Access Developer's Guide to SQL Server"

Oh my...if you only knew the library I have acquired over the last 3 months!!
<lol>

Thanks for the suggestion; I'll check it out.

One thing that might
help you with the upsizing is this... I know it's not perfect, but it
does most of the heavy lifting for you...
http://www.amazecreations.com/datafast/GetFile.aspx?file=DataFastUtility.zip

I am developing in Ac07 using Ac03 format. I will Upsize to SQL 2k only to
convert to '08 in mid July.
Given this information, is there anything else of which I should be aware?

The DataFast files are in 97 and '2k. What do you know about using it for '07?

 

[TraciAnn via AccessMonster.com]

Clifford,

Each change you make to the development back end you record in a script which then
can be used to make the same changes to the production back end.

"Script" as in "Code" or "Document"?

I use the same method as you (only in Access) I have both a FileNameDEV_FE
and a FileNameDEV_BE. The FE I copy to the production FileName. As for the BE,
I document the changes I make to DEV and then manually make the changes to
the production BE at 2:00 in the morning when I know nobody is accessing the
file. If anyone has a better method, I would love to hear what it is.

Also, if you do not have security set up with various users in Access,

Yes, the first thing I do with a new project is setup an mdw so all objects
are owned by my administrator logon rather than the default admin.

Thank you for the detailed explanation. Since I'm only a day or two from
upsizing I will finish up in Access and see what happens once it is in SQL.
I'll probably have to plan on quite a bit of clean up.

Regarding the Filter table and form, can help me think this through?

I think this is the strucure I need but I'm having trouble with the Filter
table.

Project table
ProjectID (PK)
ProjectName (User friendly description)
ProjectNumber (LongInteger, AllowDups, Actual number used to filter the
projects)
FilterString (text/optional used to include in queries and/or code for
projects that require an additional filter beyond just the ID)

Filter table
UserName (PK)
ProjectID (FK from Project table)
ProjectNum (from Project table)
FilterString (Also from Project table)

frmFilter
Filter.UserName (Hidden)
cboProjectID (Selects ProjectID by ProjectName)
ProjectNum (What do I do here? DLookup based on cboProjectID=Filter.ProjectID?
)
FilterString (??? probably same as above)

Or maybe all I need in Filter table is the Username and ProjectID then do a
DLookup of Project.ProjectNum and Project.FilterString based on Filter.
ProjectID?

Thanks again for your input! You've been a great help!

 

[Clifford Bass]

Hi TraciAnn,

The script, at least in my situation with the non-Access back end that
I use, is just a text file with SQL commands. So it may look like this:

create table Some_Table (
Some_Field smallint not null,
Other_Field char(5));

alter table Other_Table alter column Char_Field char(25);

And so on. It is typed by hand into a scripting tool from which I also
can execute the commands. So when I write the SQL script to modify the
development database, I simply save it for executing against the production
database later on. You actually can do these types of commands in the SQL
view of the query designer. Unfortunately it limits you to one statement at
a time. I am not aware of a script tool for Access--however, I have not
really looked. I have seen script tools for SQL Server, Oracle and other
databases.

The way I deal with changes to Access-only databases where the back end
is also Access is this: I create links to the tables in both the production
and development back ends in the development front end. The production table
links are clearly named as such so as to avoid confusion. Maybe something
like "zz_Prod_tblTable_Name". I then write a series of queries in the
development front end that will delete the current data from the development
back end, and then load all of the data from the existing production back end
into the development back end. The queries modify the data where necessary
as part of the import process. This has the advantage that I can reload the
data at any time, multiple times during development. Especially useful if
you do something stupid with the development data. This also avoids the need
to repeat by hand the changes made to the development back end. A process
that can be prone to errors. When ready to deploy I simply run the queries.
Then the queries and links to the old production tables can be deleted. Then
I retire the old back end, moving it to a "retired" location and copy out the
new development back end, making it the new production back end. And a copy
of the new development front end becomes the new production front end. Of
course changing the table links to point to the new production back end and
doing a compact and repair. I do it by hand as I do not have to deal with
multiple clients. If I had multiple clients, I would code those manual steps
in VBA so as to automate it. My clients would then run the automated
conversion process.

For the filter table, all you really need is the user name and the
project ID. You can then link the project table to the filter table in a
query when you need to get the project number and filter string. Unless you
are allowing the users to modify the project number and filter string
independently from the values in the project table. In which case, to
(re)set them in the filters table, the DLookup() should do the trick.

Hoping that helps,

Clifford Bass