Access 2003

[danzasmack]

I have a large DB of financial data. When i compact it, the Db is about 500MB, but when I, say, add a column to a table, the DB can go to 2GB very quickly. The amount of data i add in comparison to the size of the DB is very small. Howver, i am editing a recordset to do so.

Also, as you may know, if an access DB gets to a certain size (over 2GB about) you will simply get the error "invalid argument" when you try to add a record.

Any advice in managing this besides compacting every day. (when i compact it goes down to about 500mb)

[OrcaDK]

I would seriously consider using something other than Access. Access is not, and has never been, made for large scale databases. If you have access to the licenses, MSSQL might be an option, otherwise you might want to look into MySQL og PostgreSQL.

[danzasmack]

That's what i told my boss. However...

How does MSSQL compare to access? The reason we use excel now is because we use DAO to import data from the database into excel. Are things like this available for MSSQL?

How about coding, etc. - I know SQL code from Access, is that sufficient.

[OrcaDK]

Unless you use any special Access functions, everything is supported in MSSQL, including a lot more. Queries are now called Views, you've got Stored Procedures and a lot more other features. You can easily import data from the DB into Excel, both by code, but also by using the Excel wizards.

MSSQL is vastly faster, and it's file footprint does not increase the same way as with Access.

The only downside is that unlike Access, the MSSQL databases cannot just be opened by a doubleclick. You have to use Enterprise Manager to edit the databases, and you have to have a central server that hosts them. Furthermore the Enterprise Manager isn't meant for typing in data like you could do in Access.

[TheTROLL]

As an intermediate step, try moving the data tables to SQL Server and creating Linked Tables in the Access database. This way the queries, VBA etc in the Access file will still work, but it will stop expanding in size when you add records. You can then gradually move the logic to SQL Server views/procs as you learn about it.

[Meech]

[ QUOTE ]
The only downside is that unlike Access, the MSSQL databases cannot just be opened by a doubleclick. You have to use Enterprise Manager to edit the databases, and you have to have a central server that hosts them. Furthermore the Enterprise Manager isn't meant for typing in data like you could do in Access.

[/ QUOTE ]

However, you can use Access as a front-end for MSSQL just fine using the Microsoft Access "project" feature.

You can design tables, queries, stored procedures, almost everything. You can also double click on tables -- see data, edit data, etc.

[Meech]

Since he has Access 2003 Access Projects are vastly superior to linked tables.