Using Access to manage large database

[justinasz]

Hi,

I am currently using MS Access + VBA to build reporting applications and also do adhoc reports in the company. However, from 2008 we are planning to change the way the source database is organised and the information in it will be drilled down a bit deeper. This will cause the size of the database to increase rather dramatically and it is quite likely that I will have few tables with 5M+ of rows. I am worried that Access will not be able to cope with it. It is not really about the size of the database itself, i.e. it is far away from 2GB, but I am afraid that calculations using these tables may get unreasonably slow.

I wonder what your opinion regarding this is and what other options you would offer. I was thinking about using FoxPro to manipulate the data before using it for analysis in Access. I assume FoxPro would be faster and more flexible for this but at the same time we cannot migrate to FoxPro or any other software as there are many people using the same databases and they are not trained on anything else except MS Office.

So basically I am looking for a solution to building an automated application which would manipulate/consolidate/split large database into smaller tables which later can be used for analysis in Access. It is important to understand that getting a seperate server for this task would be very unlikely at this stage, so all workload would fall on desktop computers with data being stored on central file servers.

Hope this explains the situation. Any ideas would be greatly appreciated.

Thanks!
JZ

[ADezii]

Hi,

I am currently using MS Access + VBA to build reporting applications and also do adhoc reports in the company. However, from 2008 we are planning to change the way the source database is organised and the information in it will be drilled down a bit deeper. This will cause the size of the database to increase rather dramatically and it is quite likely that I will have few tables with 5M+ of rows. I am worried that Access will not be able to cope with it. It is not really about the size of the database itself, i.e. it is far away from 2GB, but I am afraid that calculations using these tables may get unreasonably slow.

I wonder what your opinion regarding this is and what other options you would offer. I was thinking about using FoxPro to manipulate the data before using it for analysis in Access. I assume FoxPro would be faster and more flexible for this but at the same time we cannot migrate to FoxPro or any other software as there are many people using the same databases and they are not trained on anything else except MS Office.

So basically I am looking for a solution to building an automated application which would manipulate/consolidate/split large database into smaller tables which later can be used for analysis in Access. It is important to understand that getting a seperate server for this task would be very unlikely at this stage, so all workload would fall on desktop computers with data being stored on central file servers.

Hope this explains the situation. Any ideas would be greatly appreciated.

Thanks!
JZ

If your calculations are going to be that intensive and varied, you may wish to consider Upsizing to an Access Data Project in which Access Front End DBs would connect to SQL Server Back Ends. Your Users would maintain their familiarity with Microsoft Access, while the processing itself would be done within a SQL Server environment (Microsoft SQL Server 2000 Desktop Engine) residing on one of your existing File Servers (preferably the most powerful). You will not need the full blown version of SQL Server since the Engine itself is shipped with higher versions of Access, and the process would be relatively transparent to the Users. Here is a brief explanation of Access Data Projects by Microsoft:

A Microsoft Access project (.adp) is an Access data file that provides efficient, native-mode access to a Microsoft SQL Server database through the OLE DB (OLE DB: A component database architecture that provides efficient network and Internet access to many types of data sources, including relational data, mail files, flat files, and spreadsheets.) component architecture. Using an Access project, you can create a client/server application as easily as a file server application. This client/server application can be a traditional solution based on forms and reports, or a Web-based solution based on data access pages (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.), or a combination of both. You can connect the Access project to a remote SQL Server database, a local SQL Server database, or a local installation of SQL Server 2000 Desktop engine.

[justinasz]

HI again and thanks for advice!

Sounds really good to me, but excuse my lack of knowledge as I am relatively new to more advanced databases and have some more (silly?) questions...

As far as I understand, I will need to upsize my current Access database to SQL Server through Tools -> Database Utilities -> Upsizing Wizard and then base all my future projects on connection to that particular server. I tried testing how it works locally as I have MSDE 2000 installed on my laptop, but when I use the Upsizing Wizard it comes up with the famous 'Table was skipped, or export failed' error. I tried Googling it and it looks like it is a login problem (though I tried leaving the login blank, using the 'sa' login, also login I use for network). I requested the information on what configuration MSDE was installed on the machine with, so perhaps will get some more luck then.

However, I wonder if I am not missing anything else. I couldn't find any good manuals on how to use MSDE on internet - the "Using the Access 2000 Upsizing Tools" was rather useful giving some ideas on how it's done but it's not the most newbie-friendly document on earth. So just wondering if there is anything else I should know, e.g. do I need to do something extra such as special ODBC setup, etc?

Sorry for perhaps too obvious questions, but any help would be really appreciated.

Thanks,
JZ

[justinasz]

And one more question coming back to FoxPro.

As far as I understand, FoxPro works in a very similar way to Access and needs to transfer all data in the table from server to local machine before making any calculations. I.e. it is nor server-side software and as a result it does not give much improvement in speed as compared to Access. Therefore it is more useful if you want better tools with closer DB integration for building applications but not for improving the speed.

Correct me if I am wrong in any of the statements above.

Thanks,
JZ

[FishVal]

Hi, JZ.

Just as additional option you may try to use MySQL server as backend. It performs faster than Access and MSSQL.
Here may download server, ODBC connector and various tools (including migration tool).

Regards,
Fish.

[ADezii]

HI again and thanks for advice!

Sounds really good to me, but excuse my lack of knowledge as I am relatively new to more advanced databases and have some more (silly?) questions...

As far as I understand, I will need to upsize my current Access database to SQL Server through Tools -> Database Utilities -> Upsizing Wizard and then base all my future projects on connection to that particular server. I tried testing how it works locally as I have MSDE 2000 installed on my laptop, but when I use the Upsizing Wizard it comes up with the famous 'Table was skipped, or export failed' error. I tried Googling it and it looks like it is a login problem (though I tried leaving the login blank, using the 'sa' login, also login I use for network). I requested the information on what configuration MSDE was installed on the machine with, so perhaps will get some more luck then.

However, I wonder if I am not missing anything else. I couldn't find any good manuals on how to use MSDE on internet - the "Using the Access 2000 Upsizing Tools" was rather useful giving some ideas on how it's done but it's not the most newbie-friendly document on earth. So just wondering if there is anything else I should know, e.g. do I need to do something extra such as special ODBC setup, etc?

Sorry for perhaps too obvious questions, but any help would be really appreciated.

Thanks,
JZ

when I use the Upsizing Wizard it comes up with the famous 'Table was skipped, or export failed' error. I tried Googling it and it looks like it is a login problem (though I tried leaving the login blank, using the 'sa' login, also login I use for network).

The Upsizing Wizard is not 100% guaranteed, somethings will simply not be converted to the newer format. Try using the Trusted Connection Option (using existing Windows Security), you shouldn't have any problem logging on.

I couldn't find any good manuals on how to use MSDE on internet

You seem to be correct on this point, as there does not appear to be any meat-n-potatoes documentation on this subject. Do a search on MSDE within Access, and you will obtain some valuable information on MSDE and related topics.

[ADezii]

And one more question coming back to FoxPro.

As far as I understand, FoxPro works in a very similar way to Access and needs to transfer all data in the table from server to local machine before making any calculations. I.e. it is nor server-side software and as a result it does not give much improvement in speed as compared to Access. Therefore it is more useful if you want better tools with closer DB integration for building applications but not for improving the speed.

Correct me if I am wrong in any of the statements above.

Thanks,
JZ

As far as I understand, FoxPro works in a very similar way to Access and needs to transfer all data in the table from server to local machine before making any calculations. I.e. it is nor server-side software and as a result it does not give much improvement in speed as compared to Access.

I was under this same impression also.

Therefore it is more useful if you want better tools with closer DB integration for building applications but not for improving the speed.

I'm not really sure on this point.

[justinasz]

Right... Managed to upsize it to existing database (master) so it might be just authorization question, but nevermind at this stage.

I came up with some more problems now... I didn't know that there is no possibility to build 'queries' once you move to ADP file. I realise that you can build views and procedures which basically is just the other name for queries which is used on MS SQL Server, but the problem is that it still is different from Access in terms of GUI, behaviour and possibilities. As I mentioned before, some of the users in my deparment will need to access these tables for building adhoc reports for their needs. As they are not trained on using MSSQL, we need exact same possibilities and feel-and-look as if it was normal Access.

What I was thinking about was developing certain scripts which would build consolidated and summarized tables from the original data and export them to csv/dbf/? files for further use in Access. The problem with that is that if anyone needs an extra column included, they would need to come back to me as they wouldn't be able to use the original tables on server on their own. I also wonder if the increase in speed (Access client-side vs Access & MS SQL server-side) is really that noticable that it would be worth the effort...

As always, I appreciate any feedback on my ideas and perhaps any best practice on how you do/would do this...

Cheers,
JZ

[justinasz]

Hi, JZ.

Just as additional option you may try to use MySQL server as backend. It performs faster than Access and MSSQL.
Here may download server, ODBC connector and various tools (including migration tool).

Regards,
Fish.

Sounds a good idea but I don't think it would receive a very warm welcome from local IS. Don't think they would want to install additional software, which is BTW not a standard in the company when there is similar software (MSSQL) which is already being used in the company...

Thanks for the idea anyway - always good to know the options!

[ADezii]

Right... Managed to upsize it to existing database (master) so it might be just authorization question, but nevermind at this stage.

I came up with some more problems now... I didn't know that there is no possibility to build 'queries' once you move to ADP file. I realise that you can build views and procedures which basically is just the other name for queries which is used on MS SQL Server, but the problem is that it still is different from Access in terms of GUI, behaviour and possibilities. As I mentioned before, some of the users in my deparment will need to access these tables for building adhoc reports for their needs. As they are not trained on using MSSQL, we need exact same possibilities and feel-and-look as if it was normal Access.

What I was thinking about was developing certain scripts which would build consolidated and summarized tables from the original data and export them to csv/dbf/? files for further use in Access. The problem with that is that if anyone needs an extra column included, they would need to come back to me as they wouldn't be able to use the original tables on server on their own. I also wonder if the increase in speed (Access client-side vs Access & MS SQL server-side) is really that noticable that it would be worth the effort...

As always, I appreciate any feedback on my ideas and perhaps any best practice on how you do/would do this...

Cheers,
JZ

Hello JZ, it appears as though you are trying to do a balancing act between User Flexibility vs Maximum Efficiency. This is not an easy task. As far as:

I also wonder if the increase in speed (Access client-side vs Access & MS SQL server-side) is really that noticable that it would be worth the effort...

There are so many variables that you would have to test this on your own then make a determination.

How about describing the exact process in greater detail, then maybe we can mantain the context within Access, but make some Optimization adjustments?

[MMcCarthy]

One solution would be to use an odbc connection to the MSSQL backend as this would allow you to keep an mdb file and essentially keep your existing database but using linked tables rather than local ones.

[sierra7]

If you want some documentation to help with Access/SQLServer migration you might try;

'Alison Balter's Mastering Access 2002 Enterprise Developement' , SAMS
There is a later verion but I don't have the details to hand. Also,

Microsoft Access Developer's Guide to SQL Server, Mary Chip & Andy Baron, SAMS

They are both 700 page tomes with lots of advice on planning the migration, what will be converted and what will not, plus lots of screen shots.

I'm not recommending these with any 'authority' (nor have I any commercial interest) and there may well be 'better' references out there, but they are quite an easy read, seem to know what they are talking about, and most of all gets you away from being hunched up over a screen for yet more hours per day!

[justinasz]

OK, I will try to explain what we are doing briefly but in some more details.

I am working in a head office of one business unit in a large logistics company and am responsible for reporting on sales results across 43 countries at the moment. All of the data is held on Terradata servers centrally and we use Cognos products to retrieve data from them. There's a whole another story why we use Cognos products and are not allowed to access the Terradata servers using any other software, but basically it is a company's policy and there's not much I can do about it. At the same time Cognos tools are not flexible enough for us to use them for building end-user reports. As a result, we have to download the raw data to local PC/network drives and use them in Access for further analysis. Currently we have few major reports which are produced on a regular basis and they are based on Access. We are changing a large part of the reporting suite for the next year, so we thought it is a good time to try and move to something more sophisticated than Access for processing information. At the same time it has to keep the Access feel-and-look and should not involve much costs as all what other people (which would use the data for adhoc reporting + building commercial and other reports) are trained in is... yes, Access.

So, the current situation is that we decided to go for smaller queries which would bring only previous couple of weeks of trade information on customer level and keep the history for at least two years locally as we need to do rather a lot of year-on-year comparisons. This would allow us to reduce the size of the extracts from Terradata servers, which, in turn, would decrease the bandwitch and time used for downloading data. However, we need to find a way how to process data locally more efficently. I ran few tests recently and apparently information for each quarter consists of some 500k+ rows, so the amount of data for 2yr would be arounf 4-5M rows and ~300MB. Plus there is another table of our full customer database which has 5M rows and ~300MB as well.

Obviously, Access with Jet engine would not be able to cope with such amount of data or even if it would, it would be terribly slow. Our IS manager offered to try FoxPro but I don't think it gives much improvement as processing is still client-side. MSSQL Server would be a good solution but I need to keep the ability to work with Access as if it was still the same Jet engine (in terms of look and features). The users in both, mine and other departments, should be able to access the source tables and work with them as if it was normally linked tables but the processing should be done server-side. Apparently I cannot achieve this by using ADP, but was thinking about the option of connecting the server to usual MDB files via ODBC. In this case, however, I do not really understand where the calculations are made - is it client-side or server-side?

Hope this all makes sense and will give you a clearer idea of what I am trying to achieve. Am looking forward to any ideas you might have - the advice I received so far is really useful and much appreciated!

Thanks a lot,
JZ

[Jim Doherty]

Right... Managed to upsize it to existing database (master) so it might be just authorization question, but nevermind at this stage.

I came up with some more problems now... I didn't know that there is no possibility to build 'queries' once you move to ADP file. I realise that you can build views and procedures which basically is just the other name for queries which is used on MS SQL Server, but the problem is that it still is different from Access in terms of GUI, behaviour and possibilities. As I mentioned before, some of the users in my deparment will need to access these tables for building adhoc reports for their needs. As they are not trained on using MSSQL, we need exact same possibilities and feel-and-look as if it was normal Access.

What I was thinking about was developing certain scripts which would build consolidated and summarized tables from the original data and export them to csv/dbf/? files for further use in Access. The problem with that is that if anyone needs an extra column included, they would need to come back to me as they wouldn't be able to use the original tables on server on their own. I also wonder if the increase in speed (Access client-side vs Access & MS SQL server-side) is really that noticable that it would be worth the effort...

As always, I appreciate any feedback on my ideas and perhaps any best practice on how you do/would do this...

Cheers,
JZ

Hi Justin,

I saw this thread late but this bit in reply 8#,

Right... Managed to upsize it to existing database (master) so it might be just authorization question, but nevermind at this stage

Arrgh! Justin you never upload anything into the master database from your application side. The master database in SQL server is exactly that, its own master database that controls what SQL Server does if you understand me. You need to create your own databases on the SQL server not upload into existing 'master'

The crux of the thread here it seems to me is the 'FEEL' of the thing as you said. Mary McCarthy's suggestion of having localised linked SQL Server tables to an mdb database is the most suitable for your purposes in my opinion.

Whilst I personally use ADP files most of the time because of the fact that they connect directly to the MS SQL Server and expose the views, stored procedures and tables in the GUI with no other interaction necessary by default this is all very well and good... because I know how they work and can write 'transact SQL' (Sql Servers own language) stored procedures,admi nister SQL Servers and so on..........but your users don't, they haven't been trained.

It is not for the faint hearted learning to write complex stored procedures from scratch, not to mention having to understand the less than friendly 'views' interface that exists in an ADP file much of the time together with the syntax differences.

To expose all of that which an ADP offers to users without training is investing time and expense for something which can be catered for using an mdb file that they are already familar with together with providing a slight adjustment for expaining the nature of linked SQL Server tables.

ADP files for me have absolute merit in the environment where you desire to read from AND input to a central database but in this instance you really are only reading data from the server. so 'passthrough' queries achieve the same resultset as any ADP file.

In short, this is one of those occasions where for me the MDB file would be the recommended approach for you, if only to stay within the feel of what you have currently have.

Practically speaking, If your users are the type of people who like to create their own objects ie queries (or views) and so on then using an ADP file for each user brings with it the overhead of piling up the server with any number of ad hoc saved views 'owned' and created by all sorts of different individuals who, lets be honest, never manage their own material (at the best of times) so you then have some poor dba scratching his or her head wondering what on earth he/she can get rid of object wise, across the organisation, when they become old.

If you give each user a separate mdb file and they so happen to screw up their local copy or create a miriad of objects in their own copy which they fail to control themselves your IT department can basically give them a standard default mdb file and tell them to be more careful next time (or if they are very helpful retrieve from backup) a win win situation for your helpdesk.

Keeping the thing distributed via mdb sounds to me the way to go on this BUT keeping the data centrally on the server so that you only have the need to extract the data from your terraservers across to a centrally sourced SQL Server (as opposed to distributing data to client machines, users can always create their own localised tables in an mdb file based on the linked data if they need to and do what they will with that in terms of ad hoc reports etc etc)

Hope this helps you a little.

Regards

Jim :)