Originally Posted by
Ajax
Don't think you understand what Access is. It provides a front end development environment (for forms, reports, code) which links to one or more backends - often ACE with its 2Gb limit, but could be sql server, oracle or any other odbc database. Depends how big a record is but I suspect ACE would not have the capacity for 10's of millions of records. Largest db I had using ACE was around 8 or 9 million records. The point is, you don't need to import the data to access, just reference the source directly. If this is not possible for some reason then OK, you need to import to somewhere, but probably not ACE.
that is down to one or more of poorly designed tables/indexing/queries, poorly written code or slow network if backend is on a server. Moving to Visual Studio will not address these issues although it may force you to rethink how you want to achieve something. Moving the backend to SQL Server et al and making use of the enhanced capabilities of these databases may go some way to address these issues. The db I referred to above took around 2 minutes to analyse a months data with comparisons to previous month/year etc. and generate numerous reports. It took longer to load the data in the first place.
You have an Excel background. Databases (any database) requires a completely different way of thinking. Simplistically, Excel combines presentation and data storage in one view. databases just store data, you use Access/Visual studio to present that data. On data storage, Excel 'tables' tend to be 'short and wide', databases tables are 'tall and thin'. Excel loads everything to memory, databases pass out data as requested through queries. Databases adhere to normalisation rules whilst Excel is about as far away from normalisation as you can get.