Access 2000 File Format in Access 2003

[Chuck W]

Hi,

I have Access 2003 on my work PC. I just started here some I don't know if
the PC once had Access 2000 and was upgraded or it if was a fresh install of
2003. Whenever I create a new database or open an existing database, the
Access database is listed as being in Access 2000 File Format. It looks like
I can upgrade the database to 2002-2003 using Tools => DB Utilities =>
Convert DB => Access 2002-2003. I know there is a 2 GB file limit with
access but my databases start doing some odd things once they get to about
1.2 GB. I have been doing compact and repairs. I am wondering why it is
creating even new database that I create using Access 2003 into a 2000 file
format and is there an advantage to upgrading these DBs? I am with rare
exception the only one using them.

Thanks,

 

[Jeff Boyce]

Chuck

In a word, "default". Access 2003 uses Access 2000 format by default.

If you have an Access database with 1G+, even after compact & repair, you
might: 1) consider whether your data is causing 'bloat' (are you storing
images?), or 2) look into a more robust back-end (e.g., SQL-Server).

If you'll provide a more specific description of your data, folks here may
be able to offer more specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jerry Whittle]

Unless you are using something that is only in Access XP/2002 or Access 2003,
there is no real reason to use the newer format. The old Access 2000 format
is OK.

What are these "odd things"?

I am wondering why it is
creating even new database that
I create using Access 2003 into a 2000 file

What does this mean? Are extra database files being created during the
compact and repair that revert to the Access 2000 format when they were
originally Access 2003?

 

[Chuck W]

There have been two odd things that are happening with this database. I kept
getting an error that said "Compile Error. in query expression Left..". I
have two date fields (Admit and Discharge) that are stored as text values (ie
03012009). I need to import this data when finished massaging it into
another tool with these fields as date values so I created this query that
separates the left, middle and right parts of the string, adds / and then
concatenates them. After I ran a compact and repair my db went from 1.2 Bs
to 400 MB and the error message went away and the query worked. I would then
do a couple of other steps and import more tables causings my db to grow
again. I would then try to change a field format from text to date or text
to number and would get an error message stating that there was not enough
memory. After a compact and repair, it let me do this. My tables are
fairly large with 200-300 K records in them. Since I am doing a lot of
importing and running make and append tables, the db gets large quickly.

 

[Jerry Whittle]

You may need to frequent C&Rs during the process of manipulating all this data.

Another option would be to create another .mdb file just for importing and
manipulating data. If you divide up the tables properly, that would give you
4 GB effectively.