Beginner's questions

[Dina Perros]

As I try to convert a current "Excel database system" to a real Access
database, I have created a simple database that covers my needs (way much
better than Excel), but there are two basic issues that I need advice on.
I am using MS-Access 2003.

1. On some tables I have set the primary keys as autonumber-fields (and in
many cases used in relationships). I cannot use a manually entered
"field_ID" for primary key because the autonumber gives me flexibility when
importing records from excel tables or other recources.
The question is: If I ever have to rebuild the tables and reset the
relationships between tables (in case of a database corruption) will it be
possible to do this without loosing the relationships between the existing
data ?

2. I have set all the forms in the database to "lock current record" and I
have split the database to front/back end. The object is that the database
will be used at maximum by 2 or 3 users simultaneusly in a network
environment. Is this safe or I have to make other settings to prevent the
database from corruption? Do you have any other recommendations regarding
this?

I have some experience in Excel VBA, pretty good understanding of the
MS-Access interface and basic knowledge of the Access-VBA environment. Can
you suggest some good books for begginer/intermediate level Access
programming?

I apologise for the many and naive questions,

Thank you
Dina

 

[Guest]

Good afternoon, Dina,

Actually that is a very well written post, and please do not worry. Even as
fairly advanced users, We are all learning, and I've found this website to be
a significant resource. Those are terrific questions.

I primarily do development for stand alone systems, so I feel less qualified
to address some of your questions. I too import a lot of data from Excel,
primarily monthly data and would be glad to explain to you how I setup my
application. But I am not sure exactly what you are trying to do w/ your
imports. I import stats from a monthly Excel spreadsheet and then run queries
on those stats. I experiemented w/ linking files to Excel and also trying to
use relationships, but I also experienced some difficulty; so I can
understand where you are coming from. I would be glad to be what assistance I
can. Be free to reach me in this post or my direct email at
(e-mail address removed).

To answer your question about books, while I am not sure what your
experience level is, I found 2 books to be terrific resources: The first is
titled BEGINNING ACCESS 2003 VBA, a programming book by Denise Gosnell, by
Wiley publishing, includes updates and technical support at wrox.com. She
does a terrific job of explaining various aspects of VBA and ways of doing
things in Access. I would revue the book first before buying just to make
sure meets your needs and level, there's a lot of good stuff in there, and
some good case studies too. I also found USING MICROSOFT OFFICE ACCESS 2003,
SPECIAL EDITION, by Roger Jennings to be really useful. This is a really big
and pretty exhaustive reference book. From a programming standpoint, I find
Denise's book a little easier to use, but there's a tremendous amount of
info, including programming concepts in Roger's book too, plus more. Well,
thanks and let me know if I can be further assistance.
Cordially,

 

[John Vinson]

As I try to convert a current "Excel database system" to a real Access
database, I have created a simple database that covers my needs (way much
better than Excel), but there are two basic issues that I need advice on.
I am using MS-Access 2003.

1. On some tables I have set the primary keys as autonumber-fields (and in
many cases used in relationships). I cannot use a manually entered
"field_ID" for primary key because the autonumber gives me flexibility when
importing records from excel tables or other recources.
The question is: If I ever have to rebuild the tables and reset the
relationships between tables (in case of a database corruption) will it be
possible to do this without loosing the relationships between the existing
data ?

Yes. Of course, if you have corruption which destroys data in your
tables, you'll need to recover that data from a current backup, or
manually reenter it. Good backups are ESSENTIAL - and "current" means
"if I have to reenter all this by hand I'll be grumpy for the rest of
the afternoon" rather than "there's no WAY I can possibly reenter all
this by hand!!!"

2. I have set all the forms in the database to "lock current record" and I
have split the database to front/back end. The object is that the database
will be used at maximum by 2 or 3 users simultaneusly in a network
environment. Is this safe or I have to make other settings to prevent the
database from corruption? Do you have any other recommendations regarding
this?

User contention for editing records in a split database is rarely or
never the cause for corruption. Look instead to bad network interface
cards, noisy networks (DON'T try a split database across a WAN or a
wireless link), and users who are too free with rebooting during
Access sessions. See

http://www.granite.ab.ca/access/corruptmdbs.htm

for some suggestions about preventing and treating database
corruption.

I have some experience in Excel VBA, pretty good understanding of the
MS-Access interface and basic knowledge of the Access-VBA environment. Can
you suggest some good books for begginer/intermediate level Access
programming?

There are several; one I particularly like is _Microsoft Access 2003
Inside Out_ by my colleague John Viescas. For advanced developers (or
those who have ambitions in that direction) the sine qua non is
_Access 2002 Developer's Handbook_ by Getz, Litwin and Gilbert, in two
big thick pricey volumes (which have repaid their purchase price many
times over for me).


John W. Vinson[MVP]