Link to home
Start Free TrialLog in
Avatar of Thomas Zucker-Scharff
Thomas Zucker-ScharffFlag for United States of America

asked on

MS Access trouble - backend seems to have odd tables

I asked another question about this same database here: https://www.experts-exchange.com/questions/29197626/After-making-changes-to-a-backend-my-frontend-seems-only-able-to-access-fields-in-a-temp-table-TMPCLP532891.html, now I am experiencing a different problem.  The frontend of this database is excruciatingly slow (think about 5-10 minutes at best) in loading or in doing anything and I think it is because it is not only linking to 2 different backends (with the same name and structure), one of the BEs has 2 tables that are named as a sequence of numbers and start and end with brackets.  When I open the BE that supposedly has these tables, they are not there.  

When I look in the FE I see 3 linked tables with funny bracket naming - 2 of these tables seem to have the same data (343 records), while the third has additional data (358 records).  All reflect the data in another table, tblDemog (which has 358 records).

Several other items about this Database pair:
  • The BE is housed on a network share that is undergoing some repairs
  • I use software called AutoFronted Updater to automate updating of the FE of the database and the software is housed on the same share.
  • This used to be a single database until I used the built-in splitter to split the database.
  • The database frontend has a custom ribbon

I have three basic questions (I actually have way more, but I can probably muddle through some of the others):

  • Is there any easy way to determine what dependencies there are on these tables?
  • How and why were these tables created in the first place?
  • How do I get rid of them?

I just went to relink the backend for another reason and received an error for each of the two tables referencedUser generated image
Avatar of Daniel Pineault
Daniel Pineault

What about creating a new blank db and importing everything and seeing if that resolve your present issue.
I would import only your well known objects to the new database and leave the weird and unknown tables.
Avatar of Thomas Zucker-Scharff

ASKER

I'll try the import route and let you know. when I reminded the linked tables, 2 more of the same type odd tables were created. I unlinked those tables, everything seems to be working.
One thing that is critical when re-linking to BE's is that you keep something open in the BE constantly.  

If you don't, the BE will repeatedly open and close, which carries a lot of overhead.   This can be as simple as keeping a recordset open on a table (and it can be empty;   SELECT * FROM tblXYZ WHERE 1=0) or when manually re-linking, relinking one table, then opening that table, then going back and do the reset in that BE.

 As the others have said, create a new blank DB for the BE and import everything into it.  Then do a Compact and Repair on it.   That will get rid of all the temp tables.

HTH,
Jim.
@Jim,

Thanks for chiming in, but I'm afraid you are already beyond me.  For instance, "critical when re-linking to BE's is that you keep something open in the BE constantly " sounds good to me, but using something like "SELECT * FROM tblXYZ WHERE 1=0 " is somewhat confusing to me.  

On another note, do I import everything from both linked backends, I would duplicate a lot that way?
No no. In the new database, you import only objects from the old frontend.

And forget for the moment about that open table. That you can have fun with when your frontend is working properly.
@Gustav,

The FE?  I thought you and others said the BE.  I will create the new DB now (already created a new BE by importing stuff from old BE).

BTW, in importing the frontend it gave reference errors.
Tom,

<<For instance, "critical when re-linking to BE's is that you keep something open in the BE constantly " sounds good to me, but using something like "SELECT * FROM tblXYZ WHERE 1=0 " is somewhat confusing to me.   >>

  So when you have linked tables in a FE to one or more BE's, each time something accesses a table in a BE, the BE database needs to be opened.   When you are done with that object (table, query, form that uses the table, record set in code, etc), if there are no remaining references to that BE, the BE is closed.

  So this repeated opening and closing of a BE can really add up.    Many developers then will either open a recordset in code (what I sort of showed before with the SELECT), or will open a hidden form at startup, which is bound to a table in the BE.  Whichever is used, you don't close that until the app is closed.

  In this way, the BE(s) are always held open, and you avoid the repeated open/close.   Depending on the app, how it is used (people going in and out of forms vs sitting on one form all day) and the network, it can be quite a performance boost.

 As far as rebuilding, you want to rebuild both the FE and the BE's.   Start with the BE's.  

1. Create a new blank database
2. Import all the tables into it.
3. This becomes the new BE.

 Do that for each BE.  You would do that out on the server share.

 Now on your station, create a new blank DB.  This will become the new FE.  First, link to the tables in your newly created BE(s).   Then import all objects from the old FE except for the linked tables.

 That gives you an entirely fresh setup.

 Also when importing, don't forget to use the "options" button, and make sure that relationships is checked, import/export specs, etc.   You want to make sure that everything is copied.

Jim.
Jim,

Thanks, that helps a lot.  I am importing the BE into a new table now and will place it, once complete in the server.  Then I will import the FE to a new database (That is where I saw the weird tables) first linking the external tables.  This sequence helps me understand what I need to do.  I do not do a lot of DB development :-)

Sorry for asking another question, which is probably simple, but is confounding me.  I followed Jim's instructions, but now the main form is showing as a tab instead of just showing as maximized within access (the custom ribbon should still show).

Also, it looks like I lost 16 records and some edits - can I just copy the lost records from the other backend (done and it seemed to work) and do I have to redo the edits (assuming I can figure out what was changed).
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That worked like a charm!  The edits I was referring to are one in which I changed the status or title of an individual which should have been changed in the BE but must have been changed in the other BE and not the one I had hoped (just like the records which I ended up copying were in the other BE and not the preferred one).  Everything seems to be fine now.  I have made a backup of the old BE and the old FE and am replacing them with the new ones.