Solved: How Much can Access hold?

[debauch]

Hello,

Our current workplace is in the process of switching from an Oracle server to MS Accss. Our server was being stored off site, and in building this new process, I am worried about database size, and MS Access limitations.

Two quick questions :
1. How many records can one MS database hold? I am looking to load approx. 2,000,000 records each year (and archive at year-end). with 20 columns of data.

2. What (approx.) size would that database look like 1 gig, 10 gig? I am ok with queries running a little slow, as our current Oracle queries travel 4000 mi. :)

Is there alternative options that you might suggest to MS Access for this size database? I would prefer to stay away from Oracle, as the VB gets a little tricker.

[geekgirlau]

File size is restricted to 2 gig, but you will be experiencing difficulties WAY before you reach this limit. I couldn't see the limit on the number of records, although from memory this is about 3M.

My personal preference for what you are describing would be a SQL Server back end, with an Access front end. Access is a nice tool to quickly develop your front end functionality, and allows you to easily make use of the security and speed of data processing in SQL.

[debauch]

I was afraid of that answer.

Can you pass Access data to an Oracle table easily?

[geekgirlau]

You can use Oracle tables, but IMO Oracle is not a friendly tool to use, and the security model is a lot trickier to work with from Access.

[stanl]

I was afraid of that answer.

Can you pass Access data to an Oracle table easily?

You can link via Access to any Oracle table with either a DSN or directly with the OLEDB Provider. Oracle does not have a native boolean data type and Oracle BLOBS and CLOBS are not easily fit into Access. Probably SQL Server 2005 Express (it is a free download) is your best guess as there are abundant articles and procedures for transferring data between the 2.

.02 Stan

[debauch]

How easy would it be to append MS Access data into Oracle or SQL Server? My VBA macro creates the feed, and loads it into the Access table, maybe an append would make any pass of data to Orc/SQL Ser xpress easier?

[geekgirlau]

Should be fairly straightforward. If you have a link to the tables, you can use a standard Access query to append data.

[debauch]

Ok, thanks. I've got a good start to my project. I might be back with some Access module questions. :)