ADP Connection to Large Database

[Dean]

I have a SQL 2005 Database with around 5000 highly normalized tables. When
setting the Connection property of an ADP to that database, the ADP responds
VERY SLOWLY. Turning on Profiler shows that Access is trying to cache a lot
of information about the database, including object, constraints, etc.
Turning off the navigation bar has no affect. For the life of me, I can't
possibly imagine what Access is trying to cache that would be of any benefit
in a subsequent process.

Does anyone know of a way to turn off this behaviour? Short of a good
answer, ADP's simply do not work against databases with a lot of objects.

Thanks...

 

[SteveM]

The Access team currently recommend you use an MDB and ODBC with SQL Server
backends.

That is an extraordinary number of tables in your backend.
What are they for?
How do you maintain that?

Steve

 

[Albert D. Kallal]

I have not the ability to imagine 5000 normalized tables.

What makes the 5000 tables different? A date, a year? a product code?

I betting you could eliminate 4999 tables by adding ONE column that is the
table name,
and thus only need one table....

Further, do you REALLY need all 5000 tables linked at the SAME time?

Perhaps you should just setup a link to the one table you need......

I am trying REALLY REALLY REALLY REALLY hard to imagine that you need all
5000 tables.

how can you possibility manage an appcation with 5000 compete different
tables?

And, if they are the SAME structure, then you have ZERO concept of
normalized data......

Sounds to me like you should have 5000 different applications here.

If the table are similar structure, then adding one cilium to the table that
differentiates
this attribute means you can eliminate 4999 tables here.

Anyway, I would consider simply having a link to the one table you need to
work with.........

If the structures are all the same, then you don't have normalized data....

 

[Pieter Wijnen]

Sounds like SAP <g> (around 15.000 tables I think)

Pieter

 

[Dean]

I asure you Albert that the tables all make perfect sense, and as you say,
perhaps you don't have the ability to imagine it. It is in fact a mainstream
ERP system to which I want to connect an ADP. Seems pretty straight forward.

The reality is ADP's choke on as little as a thousand tables, which surely
you can imagine? ADP's have a single connection, so linked tables is an
irrelevant option and anything along those lines is plumbing, or worse
DAO/ODBC.

The real question here is why the Connection.OpenConnection method performs
such an operation?

Thanks for any help gang....
Dean

 

[Dean]

Thanks Steve. They are maintained with a CASE tool.

You're saying Microsoft recommends MDB's to connect to any SQL database? I
was hoping to use the file format designed for that purpose.

 

[Albert D. Kallal]

Well, I would test and try a mdb with linked tables...and see if that works
any better...


If it don't function well, then your using the wrong tool here....

I have no idea what tool you should be using...but if a mdb with linked
tables is slow, then you have to using something else....