Access 2007 Validation taking minutes

[sportsguy]

Everytime I close, open, modify, etc, you get my drift,
either a linked table or an in database table, Access validation takes minutes before I can continue.

What can I do to reduce this time or process?

thanks

sportsguy

 

[jdraw]

What exactly is "Access validation"?
You haven't given us any info to go on.
Have you done a compact repair lately?

 

[sportsguy]

Let me rephrase:

I have a front end on my laptop, and a backend on an external ESATA drive.
I have many tables attached from the ext ESATA drive, which holds the production tables prior to loading to SQL SERVER

If I create a new development table in my front end database, with 15 fields and 1 record, and I switch from table view to design view to alter some field settings in the development table, MS Access does something for about 2-3 minutes on the ext ESATA drive. . .

Waiting 2-3 minutes for a view switch gets painful . . when I need to do 4 or 5. . . .

any suggstions?

 

[jdraw]

I'm not exactly sure of your set up. Are you saying you have a production set up on your machine with tables on an external drive and a FE on your HDD on your laptop, and at the same time you are doing development with a new table in your FE?
Where does SQL server fit in this mix?
Is everything running in the same version of Access?

I have a painful experience when moving from A2003 to A2010 and that painful delay when switching, but I don't think that's what you're talking about.

My first suggestion would be to separate your development and production environments. What's with a new table in your FE?

I must be missing something.

 

[sportsguy]

Simply speaking, I have a development FE. . . a production BE. . . and the production only means that the BE tables are optimized table designs full of data for reporting, and the reports are run out of a reporting FE. . . The BE data population is handled by another FE database, which links to the company production systems and the BE databases. i have dedicated FEs and BEs. makes life very easy. . .

in the development FE, i do table design. When the table design is optimized, the way that I want it, i push the table to a BE database, for data population and use. . . i don't junk up a reporting front end with design work, nor junk up BE with design work.

make sense so far??

Issue:
If I create a new FE table in my front end desvelopment database, with 15 fields and 1 record, and I switch from table view to design view to alter some field settings in the FEtable, MS Access does something for about 2-3 minutes with the BE tables . . .

Waiting 2-3 minutes for a view switch gets painful . . when I need to do 4 or 5. . . .

any suggestions? and thanks for reading and trying to help.

sportsguy

 

[jdraw]

Are you the only user? If not, does every user have his/her own copy of the FE on their machines? I can't think of anything that takes 2-3 minutes unless you have some other functions/macros that get invoked "somehow".

Perhaps someone else will read and comment.

 

[spikepl]

My comment is that we only have OP's story but few facts. "MS Access does something for about 2-3 minutes with the BE tables . . ." What are the specific symptoms that make you say that "BE tables " are involved?

To narrow down the actual issue either deconstruct your FE, throwing things out, or build a new one, one table/form at a time, to see when the problem disappears/reappears.

 

[sportsguy]

The specfic symptom is that the external hard drive is being accessed, while i am working on a table in the pc located hard drive.

I am the only one using the BE tables, so that is not an issue. There are no forms, just tables open. . .

open one local table, switch to design view and it takes several minutes for the hourglass to stop whirring. and i can continue. . .

If there is no answer as to why the database is accessing the back end, evey single time i switch views, that's fine, i just wanted to know if i could speed up my work by eliminating something. . .

thanks for reading

sportsguy

 

[jdraw]

If you are not using the BE tables, can you turn off the external drive? Does it still take several minutes to switch views on the FE table?

 

[sportsguy]

Not really, because that is where the FE table goes after development. . .

I am just hoping there is a setting or switch somewhere which turns off table validation. . . i hate that setting. . . :banghead:

 

[jdraw]

Still not sure what table validation is as you're describing things.

I must be missing the importance of this issue. You're working on a FE table, so you don't need to be connected to BE at the moment. Just work with the front end and the development of the table -- why involve the BE? Why not just turn of the external drive?
That would seem to remove the issue....