Two Plus Two Older Archives  

Go Back   Two Plus Two Older Archives > General Gambling > Computer Technical Help

Reply
 
Thread Tools Display Modes
  #1  
Old 12-05-2005, 04:58 PM
danzasmack danzasmack is offline
Senior Member
 
Join Date: May 2005
Location: running goot
Posts: 291
Default Access 2003

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)
Reply With Quote
  #2  
Old 12-05-2005, 05:09 PM
OrcaDK OrcaDK is offline
Member
 
Join Date: Nov 2004
Posts: 42
Default Re: Access 2003

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.
Reply With Quote
  #3  
Old 12-05-2005, 05:16 PM
danzasmack danzasmack is offline
Senior Member
 
Join Date: May 2005
Location: running goot
Posts: 291
Default Re: Access 2003

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.
Reply With Quote
  #4  
Old 12-05-2005, 05:56 PM
OrcaDK OrcaDK is offline
Member
 
Join Date: Nov 2004
Posts: 42
Default Re: Access 2003

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.
Reply With Quote
  #5  
Old 12-05-2005, 06:21 PM
TheTROLL TheTROLL is offline
Senior Member
 
Join Date: Feb 2005
Location: London
Posts: 103
Default Re: Access 2003

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.
Reply With Quote
  #6  
Old 12-05-2005, 06:28 PM
Meech Meech is offline
Member
 
Join Date: Nov 2004
Location: Meechigan
Posts: 59
Default Re: Access 2003

[ 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.
Reply With Quote
  #7  
Old 12-05-2005, 06:29 PM
Meech Meech is offline
Member
 
Join Date: Nov 2004
Location: Meechigan
Posts: 59
Default Re: Access 2003

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

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 02:10 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.