How do I read delete and write records in Access modules?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am getting myself confused. I have a table tblIndex which I want to copy to
tblIndexbkp, and then delete all its records. I then want to read each record
from tblAlumnus and add 1 to 4 records to tblIndex depending on the presence
or absense of certain fields. Can anyone tell me how to do this in the
simplest way?
 
I am getting myself confused. I have a table tblIndex which I want to copy to
tblIndexbkp, and then delete all its records. I then want to read each record
from tblAlumnus and add 1 to 4 records to tblIndex depending on the presence
or absense of certain fields. Can anyone tell me how to do this in the
simplest way?
Click to expand...

Not without some more background, no.

You can run an Append query to append tblIndex to tblIndexbkp; then run a
query

DELETE * FROM tblIndex;

to empty it. Then you can run some (unspecifiable given the information you
posted) append query or series of append queries to repopulate tblIndex.

This seems like a very convoluted set of operations; what real-life Entity
(person, thing or event) does tblIndex represent? What use will be made of it?

John W. Vinson [MVP]
 
John W. Vinson said:
Not without some more background, no.

You can run an Append query to append tblIndex to tblIndexbkp; then run a
query

DELETE * FROM tblIndex;

to empty it. Then you can run some (unspecifiable given the information you
posted) append query or series of append queries to repopulate tblIndex.

This seems like a very convoluted set of operations; what real-life Entity
(person, thing or event) does tblIndex represent? What use will be made of it?

John W. Vinson [MVP]
This a database for collecting data on myself and my classmates. Most of the data is stored in tblAlumnus. The problem is that while we were in high school, a number of us had nicknames that were better than known than our real names. In the 45 years since graduation, most of the women have married and changed their surnames. One of our classmates was Sharon Tilley. She was better know as Cricket. Her last name is now Cartwright. I want tblIndex to have 4 entries pointing at Sharon Cartwright's entry in tblAlumnus: Sharon Tilley, Cricket Tilley, Sharon Cartwright, and Cricket Cartwright. the index table is bound to frmIndex which lists the records in dataset view in name order. On each line is a command button which opens frmPersonal showing the selected record. The form is working perfectly, but I am building the index records manually every time I change or add records to tblAlumnus. This is bound to give some errors.
Click to expand...

I tried to code immediate changes to the index everytime a name changed, but
I'm not knowledgable enough to do that. I thought if I had a macro or a
module that would rebuild it whenever I wanted, that would solve the problem.
What I plan to do is:

Copy tblIndex to tblIndexbkp 'to create a backup copy in case of trouble.
Delete all records from tblIndex 'why I need a backup
Open tblAlumnus as readonly
Open tblIndex as edit
Read tblAlumnus
Do until EOD
Write LastName, FirstName, MiddleName, AlumnusID to tblIndex
If PrevSurName is blank then
Else
Write PrevSurName, FirstName, MiddleName, AlumnusID to tblIndex
If Nickname is blank then
Else
Write PrevSurName, Nickname, MiddleName, AlumnusID to tblIndex
EndIF
EndIF
If Nickname is blank then
Else
Write LastName, Nickname, Middlename, AlumnusID to tblIndex
EndIF
Read tblAlumnus
EndDo
EndRoutine

There is probably an easier way. Would you tell me what it is?
 
I tried to code immediate changes to the index everytime a name changed, but
I'm not knowledgable enough to do that. I thought if I had a macro or a
module that would rebuild it whenever I wanted, that would solve the problem.
What I plan to do is:

Copy tblIndex to tblIndexbkp 'to create a backup copy in case of trouble.
Click to expand...

You're far more likely to have corruption on your entire .mdb database than to
have corruption on a single table. I'd suggest backing up your .mdb file
before you even OPEN it to do this operation.
Delete all records from tblIndex 'why I need a backup
Open tblAlumnus as readonly
Open tblIndex as edit
Read tblAlumnus
Do until EOD
Write LastName, FirstName, MiddleName, AlumnusID to tblIndex
If PrevSurName is blank then
Else
Write PrevSurName, FirstName, MiddleName, AlumnusID to tblIndex
If Nickname is blank then
Else
Write PrevSurName, Nickname, MiddleName, AlumnusID to tblIndex
EndIF
EndIF
If Nickname is blank then
Else
Write LastName, Nickname, Middlename, AlumnusID to tblIndex
EndIF
Read tblAlumnus
EndDo
EndRoutine

There is probably an easier way. Would you tell me what it is?
Click to expand...

Two suggestions:

Run one very simple Append query:

INSERT INTO tblIndex(LastName, FirstName, MiddleName, AlumnusID)
SELECT NZ(PrevSurName, LastName), NZ(Nickname, Firstname), Middlename,
AlumnusID) FROM tblAlumnus;

and don't use any recordset or code at all; or, even better, create a Select
query

SELECT NZ(PrevSurName, LastName) AS OldLastName, NZ(Nickname, Firstname) AS
OldNickname, Middlename, AlumnusID) FROM tblAlumnus;

and name it qryIndex, and don't bother deleting tblIndex or recreating it AT
ALL.

You can do anything with qryIndex that you would do with tblIndex: base a
Report on it, base a Form on it, export it, etc. The only thing you wouldn't
be able to do is edit the OldLastName or OldNickname fields.

John W. Vinson [MVP]
 
John W. Vinson said:
You're far more likely to have corruption on your entire .mdb database than to
have corruption on a single table. I'd suggest backing up your .mdb file
before you even OPEN it to do this operation.


Two suggestions:

Run one very simple Append query:

INSERT INTO tblIndex(LastName, FirstName, MiddleName, AlumnusID)
SELECT NZ(PrevSurName, LastName), NZ(Nickname, Firstname), Middlename,
AlumnusID) FROM tblAlumnus;

and don't use any recordset or code at all; or, even better, create a Select
query

SELECT NZ(PrevSurName, LastName) AS OldLastName, NZ(Nickname, Firstname) AS
OldNickname, Middlename, AlumnusID) FROM tblAlumnus;

and name it qryIndex, and don't bother deleting tblIndex or recreating it AT
ALL.

You can do anything with qryIndex that you would do with tblIndex: base a
Report on it, base a Form on it, export it, etc. The only thing you wouldn't
be able to do is edit the OldLastName or OldNickname fields.

John W. Vinson [MVP]
Doesn't this add a lot of new records to tblIndex every time it is run?
Click to expand...
What is the NZ function? I've never seen it before and I can't find it in
"help" or in any books I have.
 
What is the NZ function? I've never seen it before and I can't find it in
"help" or in any books I have.
Click to expand...

It's in the VBA help file, not the Access user-interface help. Open the VBA
editor (ctrl-G) and look for help there.

Pretty simple:

NZ(<value>, <optional argument>)

returns the value of the optional argument if value is NULL, and just returns
the value otherwise. If you leave out the second argument it returns 0 if the
value is numeric, and an empty string "" if it is text.

John W. Vinson [MVP]
 
John W. Vinson said:
It's in the VBA help file, not the Access user-interface help. Open the VBA
editor (ctrl-G) and look for help there.

Pretty simple:

NZ(<value>, <optional argument>)

returns the value of the optional argument if value is NULL, and just returns
the value otherwise. If you leave out the second argument it returns 0 if the
value is numeric, and an empty string "" if it is text.

John W. Vinson [MVP]
Click to expand...
Since you were within SQL I didn't think to look in VBE Help.

John, thanks so much for all the help and patience.

Jerry A. McNabb
 
Back
Top