Size limit for MDB files?

[Charles E Finkenbiner]

Hi All,

I was wondering the point where an MDB file might start to have data
integrity problems. What is the benefit/cost of putting all tables/data
in 1 file compared to breaking it up between several files.

I think 'Referential Integrity' would be the first thing broken if you
use several files, at least I think so. What other problems should I
avoid if I were to use several files.


Thanks for any input,

Charles

 

[Allen Browne]

Charles, you would not want to do that unless you are pushing 1GB compacted.

Yes, RI is the first casualty. Another factor is the validation of multiple
back end files you run on startup to check the data is there, and to
reconnect if necessary.

If you have non-text data (sound, pictures, ...) in OLE Object fields, your
database size will bloat quickly, so you want to replace them with links to
the data instead. If you have 1GB+ of text data, some of those tables must
have a lot of records.

 

[Charles E Finkenbiner]

Hi,

I am only in the design phase of my project. I do see it growing
quickly and was only wondering if there were things I should do now,
before they become a problem.


Thanks for your input,

Charles

 

[Allen Browne]

Ah, good. If you foresee well-normalized tables with many millions of
records, you might want to consider using SQL Server as the back end. You
can still use Access as the front end if appropriate.

 

[Charles E Finkenbiner]

Hi,

You read my mind. I am going to transfer the whole project to SQL. I
am using Access XP (2000 format) to define the tables, etc... because I
do not have an SQL environment on my computer.

From what I read I should be able to transfer it from Access to SQL, right?


Thanks,

Charles

 

[Allen Browne]

Search for info on "upsize" to change from Access to Sql Server. There are
some differences.

You are aware that you can actually create a SQL Server back end through
Access 2000 if you create a new Project instead of a new database? I'm not
suggesting this is the best approach, but it could give you experience with
SQL Server tables.

 

[Tim Ferguson]

You read my mind. I am going to transfer the whole project to SQL. I
am using Access XP (2000 format) to define the tables, etc... because I
do not have an SQL environment on my computer.

Just to add to Allen's advice, you can install MSDE from your Office disks,
which is a fully-featured(*) version of SQL Server that you can use for
design and development. There are some gotchas in moving from Jet to SQL
Server, so if you plan to end up with the big boys, I'd recommend starting
there.

(*) throttled by numbers of users/ connections; all the commands, triggers,
views etc are exactly as the full product.

Hope that helps


Tim F

 

[Charles E Finkenbiner]

Hi,

Thanks for the information. I am learning Access and did not know I
could do that from Access. Since I do have MSDE installed and running I
looked around the help files some more and found that I could create a
SQL database using Access projects. Thanks, this will save me the
trouble of converting later.

The entire project will be placed on the Web, on a Windows host, and the
only interface to the data will be web pages. Is there anything else
you can suggest to help me reach this goal?


Thanks,

Charles

 

[Allen Browne]

You won't be able to use Access forms on the web, or display Access reports.
DAPs don't really cut it either. So if that's where the app will end up, you
might want to consider building the interface in a browser, and using
ASP.NET or PHP to get the data.

 

[Charles E Finkenbiner]

Hi,

I am only using Access to build the table layouts. It seems that I can
use Access to create SQL tables. I had planned on using ASP and ADO on
the web server to get the data to the web page interface.

I hope I am on track, so far at least.


Thanks for your input,

Charles