database size and linked tables

[LonnieJohnson]

Do linked tables add to the size of a database and if so how?

Thanks in advance.

ProDev, Builders of Affordable Software Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[jsteph]

For most intents and purposes, no.

Are you experiencing db growth with just linked tables?

If so, there are numerous causes, most that can be cleared up with a compaction.
--Jim

 

[LonnieJohnson]

Yeah. There are to db's. One only has two tables. The other has 6 queries and links to the two tables in the other database. The one with the queries and the linked tables is twice the size of the one with only two native tables.

I suspect that queries carry a lot of weight in a database's size. Is that true, if so do you know why?

ProDev, Builders of Affordable Software Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[jsteph]

Which version of Access? I know that in 2003 and previous, there was a specific issue with dynamic sql.

The issue was that for if you had, for example, an import routine that used a query to import data, say, one date at a time, and you dynamically updated the sql for each date (or whatever parameter), such as one would in a non-parameter or a pass-thru, then each text of that sql would be invisibly saved to the .mdb.

So if you were doing an iteration for 365 days of data, and the sql was an insert for a table with 50 fields, that sql would be multiplied 365 times. This happened to me for an access front-end I used to move data from a db which had in the hundreds of millions of rows, and the sql was iterated on an indexed time field, 1 iteration for every minute (each minute had anywhere from 100 to 1000 rows) for a year. No data was being saved even temporarily to the access db--it was only acting as a middleman between two sql databases. The db would swell until it errored with an oversize limit before the process was done--a compact repair brought it back to minimal size.
--Jim

 

[LonnieJohnson]

Nope. They are simple static select querys. I had read something about the sql of a query takes up space.

Thanks for the quick response.

ProDev, Builders of Affordable Software Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[jsteph]

It's been my impression that only the text and a few property pages worth of data are saved to the mdb for a stored querydef, and the fields & properties for a linked table. In my example above I ended up using a

CreateQueryDef with a blank name, which specifically does not save the sql.

How much space is the .mdb taking up with just these queries and linked tables? Also, are there any forms or reports, possibly with a bitmap background?
--Jim