Basic Design Theory

[Cameron Piper]

All,

I am presently working on a rather large undertaking for
my first MS Access database. Since I am a newby, I have
a few design questions that should be simple, but none
the less I need answers to. The database that I am
designing will be used in a real estate office and will
track be tracking client data, financial data, office
tasks, and a large volume of property information.

1. I need to allow users remote access to the database,
through a VPN (so users can work from home). It is
important that the information stay up to date for all
users. If someone makes a change it should show up
automatically at another users computer. The users have
high speed access to the VPN. What is the best way to
set up the database? Access Project, or a different
system?

2. I have many fields that will be combo boxes and I
have been able to find anevent procedure that will allow
me to add fields to a combo box. Is it better to use
this type of procedure or to create an entirely seperate
table for the item?

That is about all that I have for right now, but I am
sure that I will be able to think of more for later. I
would appreciate greatly any help that anyone could give
me on these matters.

Cameron Piper
Coldwell Banker Burnet Realty
(e-mail address removed)

 

[Douglas J. Steele]

Unfortunately, Access really isn't meant to be used with VPN: even the
fastest VPN connection isn't usually good enough. You might want to consider
a web-based interface.

I'm not quite sure I follow your second question. Are you asking whether
it's better to type literal values as the source for the combo box or use a
table? I'd recommend using a table.

 

[Cameron Piper]

Doug,

Are you telling me that even a microsoft Access Project
would not work if the user is connected remotely?

I suppose more information on the actual setup would
help. The corporation provides the agents access to an
office network. The agents that will be using the
database have two computers currently connected, and are
anticipating the need for a few more in the next few
years. The agents also have high speed access to that
same network through a remote connection. Given this
information, is it possible to set up an access project,
where each user is given a copy of the front end, and the
back end SQL server information is stored on a drive on
one of the office machines.

You mentioned that a web based system would be best for
this sort of application. Do you mean to say that the
users would be best served if I were to set up a database
utilizing data access pages. Of if I were to switch to
an entirely different program that is web based.

Question 2 was regarding whether it was better to have
bunches of 1 column 10 row tables in the database, or if
it is better to use the lookup wizard when designing the
field and defining 5-10 choices and then allowing the
users the ability to add more fields later. Bottom line,
which system is better for database management and
overall database performance?

I appreciate any more help that you can give me. I
apologize for my lack of experience and for asking
questions that you have most likely answered hundreds of
time. I really do appreciate all that the MVP's do.

Cameron Piper
Coldwell Banker Burnet Realty
(e-mail address removed)

 

[Douglas J. Steele]

Yes, giving more details helped! <g>

If you're only using an Access front-end against a SQL Server back-end, then
yes, you should be able to do it with VPN. To be honest, I've never used
ADPs, so I'm not sure whether that would be better than a MDB.

I'll reiterate my answer for the second question: I think it's better to
have lookup tables for your combo boxes.

 

[Kevin3NF]

Terminal Services works great for my remote users, and avoids having to
learn the quirkiness of an ADP. The FE stays in house on the TS box.

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

 

[Cameron Piper]

Kevin,

I apologize for my naivety, but I am having a little bit
of trouble understanding your most response to my
questions. Help me understand what Terminal Services are
and how I would work them into a database design that is
just underway.

Cameron

 

[Indifferent]

Hi in reply

to your question on basic design i think that you sound very confident in your work and should not add that many combo boxes as it will cuase confussion in table is update for that particular field.

try to maintain a working database rather than a over convient one