Problems creating subform

[BruceM]

I have an existing training records database. These are the three tables:

tblEmployees
EmployeeID (PK)
FirstName
LastName
etc.

tblTrainingSession
SessionID (PK)
Topic
Instructor
etc.

tblAttendance (junction table)
AttendanceID (PK)
EmployeeID (FK)
SessionID (FK)
SessionDate

There is a main form (frmSession) based on tblTrainingSession, and subform
(fsubAttendance) based on tblAttendance. fsubAttendance has a combo box
with tblEmployees as its row source, bound to EmployeeID.

All is well with this, but perhaps background will help with my question.

I am trying to expand this database's scope to include employment summaries,
which include Employer, StartDate, and EndDate for current and former
employers. I have created a table (tblSummary) to include this information.

tblSummary
SummaryID (PK)
EmployeeID (FK)
Employer
StartDate
EndDate

I have built a form (frmEmployee) based on tblEmployees, and a subform
(fsubSummary) based on tblSummary. I have established relationships between
PK fields and their namesake FK fields, including EmployeeID in tblEmployees
and in tblSummary. After I inserted the subform into frmEmployee, when I
attempt to start a subform record I receive the error message that there was
a problem communicating with the OLE server or ActiveX control. After doing
some research and learning that corruption could be a problem, I went to
another copy of the database and tried again. This time I had no
difficulties, and so far everything is behaving -- I have employment summary
records associated with each employee. It's the "so far" part that has me
worried. Is this a problem waiting to crop up again? Is there anything I
am doing that is increasing the risk of corruption? By the way, I don't
have Norton antivirus (I understand the error message can result from
conflicts with Norton).

 

[Nikos Yannacopoulos]

Bruce,

In my experience, this kind of problem often occurs as a result of heavy
development work on a database copy. It is usually solved by
decompliling abnd recompiling (hard way) and / or importing all tadabase
objects in a new, blank database (easy way). It's generally a good idea
to at least the former regularly during development (prevention is
always preferable to treatment), on top of frequent backups.

HTH,
Nikos

 

[BruceM]

Thanks for the reply. I was indeed doing a lot of development work. I have
learned the value of backups, and perform them as soon as I have
incorporated a new element of the database. It helped me in this case.

When you say that it is a good idea to do the former during development, do
you mean it is a good idea to decompile and recompile? If so, is the
procedure to enter the following at the command line (all on one line)?

"C:\Program Files\Microsoft Office\Office\msaccess.exe" /decompile
"c:\MyPath\MyFile.mdb"

If that is what you meant, do I recompile at a code window or at the command
line? By the way, what is compiling and decompiling?

I compact and repair during the development process, which as I understand
it is a good idea. Do I understand correctly?

 

[Nikos Yannacopoulos]

Thanks for the reply. I was indeed doing a lot of development work. I have
learned the value of backups, and perform them as soon as I have
incorporated a new element of the database. It helped me in this case.

Good for you! Most of us learned this the hard way!

When you say that it is a good idea to do the former during development, do
you mean it is a good idea to decompile and recompile? If so, is the
procedure to enter the following at the command line (all on one line)?

"C:\Program Files\Microsoft Office\Office\msaccess.exe" /decompile
"c:\MyPath\MyFile.mdb"

If that is what you meant, do I recompile at a code window or at the command
line? By the way, what is compiling and decompiling?

Decompile is run from a comman line prompt, not from within Access /
VBA. What it does is, it removes the compiled (executable) code from an
..mdb file. The result is that the next time you run the file, the source
code is re-compiled, while you have gotten rid of any executable code
rubbish that were left behind during development (deleting the source
code for a procedure, or a whole module, doesn't remove its compiled
code, I believe).
To be honest, I usually just do the "import-into-a-new-mdb" trick, and
it does the job just fine. Effectively, it produces the same result, in
that compiled code is not carried accross.

I compact and repair during the development process, which as I understand
it is a good idea. Do I understand correctly?

Definitely a good idea, though not enough. It compacts the data itself
(releases unused space from deleted records or even whole tables) and
re-indexes the tables, but does not get rid of the executable code
garbage. Importing all objects into a new .mdb, on the other hand, does
both, to the best of my knowledge. Yet, i still compact and repair all
the time during development (it's very fast when there's little data,
like the case usually is during development), while I would only create
a new copy at the end of the day. For one thing, there's no such thing
as one precaution too many!

Nikos

 

[BruceM]

Thanks for your reply. I also learned the hard way, but learn I did.
When you refer to importing into a new database, do you mean that I would
create a new blank database, then Import > Get External Data and point to
the old database?
I appreciate your taking the time to explain a bit about compiling. I just
was not at all clear on what the process accomplishes.

 

[Nikos Yannacopoulos]

When you refer to importing into a new database, do you mean that I would
create a new blank database, then Import > Get External Data and point to
the old database?

Yes. The wizard will allow you to import all object types. The only
other thing to do manually is chnage any of the default settings that
were changed in the original (including references in code).

Nikos

 

[BruceM]

Thanks for the reply. I had been having difficulties with an error message
that "Microsoft Access can't find the macro ...". This was puzzling to me,
because I did not have a macro by that name. In fact, I don't have any
macros, but only VBA procedures. After doing some research I realized that
the problem was due to a custom toolbar that I had not imported. It is
anybody's guess why they couldn't have included in the error message the
possibility that the problem could be with a missing menu or toolbar. I
changed the import options to include menus and toolbars, and this time
receive Error #0. Upon clicking OK I learned that a user-defined function
was not available. I had used a form resizing utility (for different screen
resolutions), and the reference to that was missing. Once I restored the
reference everything worked. I guess that's what you meant by the manual
changes that would be necessary. By the way, I will avoid that resizing
utility in the future whenever possible. It's too bad that forms can't
adapt to different screen resolutions without a third-part utility, but the
resizing utility creates its own challenges.

 

[Nikos Yannacopoulos]

Bruce,

Glad you resolved it, and thanks for sharing your experiences, some of
us are likely to benefit!

Regards,
Nikos