Forms corrupting - so will create new FE - please tell me gotchas.

[ThomasAJ]

Some forms in my FE keep getting corrupted - I have done the 'loadtext',
'compact and repair', 'decompile' etc.

I have decided to create a new FE MDB so if the experienced guys could
mention any oversights or gotchas I would appreciate it very much. (It
contains many tables, queries, forms, etc so I want to get it right first
time - have not done this before.)

I will -
1. Create new MDB
2. Drag all objects into their respective categories (not sure about linked
tables but easy enough to find out.)
3. um...cannot think of anything else to do...surely that can't be right.

 

[Allen Browne]

It's quite straight forward. Suggested sequence:

1. Compact, then decompile, then compact your existing database.

2. Create a new (blank) database.
Turn off Name AutoCorrect.
Set minimal references (in the code window.)

3. Attach the tables (linked to the back end.)

4. Import the other objects (queries, forms, reports, macros, modules) from
the front end.

5. Compile the code (to ensure it's all understood okay.)

6. (Optional.) Lay out the Relationships window the way you want.

It's important to do #3 before #4.

 

[ThomasAJ]

With regards to point 6 - Lay out Relationship window.

I have never completely understood where it should be in a FE/BE design.
Currently I have it in the BE with 'some' in the FE.

If it's in the BE how do (or more correctly 'do they') - for example queries
- know about relationships.
--
Regards
Tom

It's quite straight forward. Suggested sequence:

1. Compact, then decompile, then compact your existing database.

2. Create a new (blank) database.
Turn off Name AutoCorrect.
Set minimal references (in the code window.)

3. Attach the tables (linked to the back end.)

4. Import the other objects (queries, forms, reports, macros, modules) from
the front end.

5. Compile the code (to ensure it's all understood okay.)

6. (Optional.) Lay out the Relationships window the way you want.

It's important to do #3 before #4.

 

[Allen Browne]

You must create and maintain relationships in the database that contains the
actual tables (i.e. the back end.) You cannot create a relationship with
referential integrity between attached tables in the front end.

So #6 is might be viewed as irrelevant. It's very relevant for me, because I
rely on that relationships diagram so much, for the big picture of what's
going on between the tables. In fact, I actually use this 'extended info'
version of the relationships report:
http://allenbrowne.com/AppRelReport.html

When you create queries in the front end, it actually reads the
relationships from the back end to decide how to join the tables. That
actually shows up as a performance problem in some cases, e.g. it can take
much longer to perform design operations (or pull up the relationships
window) if the back end is coming over a network drive, has other users in
it, etc, and it's worse in some versions of Access.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

With regards to point 6 - Lay out Relationship window.

I have never completely understood where it should be in a FE/BE design.
Currently I have it in the BE with 'some' in the FE.

If it's in the BE how do (or more correctly 'do they') - for example
queries
- know about relationships.

 

[ThomasAJ]

This is off topic so I understand if you don't reply.

You said "can take much longer to perform design operations (or pull up the
relationships window) if the back end is coming over a network drive".

I use Terminal Services for user access to my Access app. so there is no
data flowing over local and remote offices. It works wonderfully.

I frequent these forums a lot but don't see much/any mention of TS.

You, Allen, would be in touch with many developers and companies. Is TS used
much?

(I am not in IT full time any more and develop my database for use in a
company I own - plus my background is primarily IBM mid-range though I have
been 'using' Access since '92.)

 

[Allen Browne]

Yes: Terminal Services (or Citrix or ...) is a great solution where you
don't have a stable local network.

Albert Kallal has some comments on WANs, thin client, SharePoint etc:
http://www.members.shaw.ca/AlbertKallal/Wan/Wans.html

 

[John W. Vinson]

You said "can take much longer to perform design operations (or pull up the
relationships window) if the back end is coming over a network drive".

I use Terminal Services for user access to my Access app. so there is no
data flowing over local and remote offices. It works wonderfully.

I frequent these forums a lot but don't see much/any mention of TS.

I use Citrix routinely; in fact as soon as I get the current crop of questions
read I need to log on to the computer in Downey CA from my office here in
Idaho to fix a search form. It can be a bit slow but that may just be their
computer.

 

[David W. Fenton]

I frequent these forums a lot but don't see much/any mention of
TS.

You must have me killfiled.

 

[Tony Toews [MVP]]

I frequent these forums a lot but don't see much/any mention of TS.

You, Allen, would be in touch with many developers and companies. Is TS used
much?

Yes, very much so.

(I am not in IT full time any more and develop my database for use in a
company I own - plus my background is primarily IBM mid-range though I have
been 'using' Access since '92.)

Ah, AS/400 or whatever it's called these days. iSeries maybe?

Tony (who spent a half hour on a S/32 in 1978 or 1979)

 

[Tony Toews [MVP]]

I use Citrix routinely; in fact as soon as I get the current crop of questions
read I need to log on to the computer in Downey CA from my office here in
Idaho to fix a search form. It can be a bit slow but that may just be their
computer.

Sluggish is hitting my server at a friends house via modem connection.
But if on ADSL, at both ends, it's fast enough that I don't even know
I'm not local.

Assuming you're not on dialup the problem is likely the other end.
Any idea if he has sufficient RAM or CPU?

Tony

 

[Tony Toews [MVP]]

2. Drag all objects into their respective categories (not sure about linked
tables but easy enough to find out.)

Use the import menu item. Dragging and dropping could take a loooong
time.

If lots of objects, say more than 100, minimize the database container
window as otherwise Access wastes a lot of time refreshing the window
when importing objects.

If you had any startup options set you'll need to recreate those.

Tony