IN need of a larger Access accessible back end database

[sportsguy]

I have databases totalling nearly 30 gigs of data, and one access database can barely hold one year of data. Therefore, I have databases by year, Invoices_2009, Invoices_2008, etc.

Is there any PC software available which can create access 2007 readable databases larger than 2 Gigs with multiple tables, which I can store on my terabyte esata drive?

I tried MySQL, but couldn't get it to work. If anyone has experience with MySQL creating a datawarehouse on an esata drive to their laptop, I would like to communicate with you.

thanks

sportsguy

 

[Galaxiom]

Wow that is a vast database just for invoices. You must have a lot of transactions.
Either that or your database is not storing it efficiently.

 

[sportsguy]

The company is a two billion dollar company with an average transaction for one half of the company at $2,200 per service invoice. Couple that with a construction company running 50,000 construction projects a year, with billable and contracted services for 600,000 customers a year. . . and the average project lasts between 6 and 24 months.

and then throw in 5 years or so of data, including 11,000 to 12,000 employees out of 33,000 historical employees, and you start to understand why i want to find a bigger database.

I had one access program take 12 hours data retrieval on the production SQL server, so they gave me my own mirror server. the data retrieval now takes 45 to 60 minutes, and that is only for one month end of one particular slice of data.

so, any suggestions for bigger PC based database software app which access can read would be tremendously helpful.

thanks in advance.

sportsguy

 

[sportsguy]

oh, and i wrote the monthly forecasting applications 5 years ago in excel and access as the consolidation engine for 120 different office locations, (consolidations took 2 minutes) and then rewrote it in Hyperion/Oracle Essbase, system 9 web based software. . . where the consolidations take about 3-4 minutes. . .

sportsguy

 

[Galaxiom]

Far out. That is enormous.
I know nothing about this size of stuff.

I did come across one post that suggested there could be advantages to installing a Server OS with SQL Server in the virtual machine that comes with Windows 7. In particular the idea of maintaining a close simulation of the live working environment makes sense to me. But I really wouldn't know.

http://blogs.msdn.com/buckwoody/arc...stall-sql-server-native-or-go-virtual-pc.aspx

Nobody has answered to date but they have obviously thought about this kind of issue. Good luck.

 

[Atomic Shrimp]

That's a serious database, to be sure, but I still can't help thinking its physical size on disk and performance in conjunction with SQL Server might indicate some design issues.

 

[DCrake]

If it is a $2bn company then why are they looking on AWF for help on databases? I would have thought that a company of that size would have its own dedicated mainframe servers with terabytes of space and memory. Using the best search engines around. Never mind asking about Access.

David

 

[gemma-the-husky]

surely this is more of a data analysis issue - at this size of company you just cant be interested in individual invoices

so rather than reanalyising invoices each time - you would find a storage mechanism to collapse the invoices into the "atomic" reporting level you really require (eg area/locale/product) - (is that data warehousing?, by the way)

i think your org needs a new IT director if you havent fixed this

 

[sportsguy]

Yes, finance who can program to get answers.

I would agree with all the comments, with the exception of a new IT director, he has very limited funding to change anything, and has said so in the past. All invoices have multiple products and services attached. Construction invoicing is done by schedule of values, progress billing, not piece meal. and we go to the zipcode/county level at the lowest level of analysis.

The issue is that I work in finance, and the funding is all for systems to tactically run the place. I had to be the lone ranger in killing a commission plan because the data and systems implementation doesn't support it, and getting the system to be updated to support the business process may take at least a year, if its even approved.

The marketing department has the same information, but it takes them months to respond, and only respond if you are a VP or higher. . . and with no detail to support the number. . (at least i provide detail)

so enough digression, anyone have any suggestions or know anyone who has implemented a bigger data repository than access? several of us have tried, and haven't been successful with mySQL. SQL Express only has 4 gigs if i remember correctly.

sportsguy

 

[Atomic Shrimp]

Construction invoicing is done by schedule of values, progress billing, not piece meal. and we go to the zipcode/county level at the lowest level of analysis.

Does this mean you're storing full address fields at that table level?

 

[David Eagar]

At the risk of sounding churlish

The company is a two billion dollar company with an average transaction for one half of the company at $2,200 per service invoice. Couple that with a construction company running 50,000 construction projects a year, with billable and contracted services for 600,000 customers a year. . . and the average project lasts between 6 and 24 months.

and then throw in 5 years or so of data, including 11,000 to 12,000 employees out of 33,000 historical employees, and you start to understand why i want to find a bigger database.

I had one access program take 12 hours data retrieval on the production SQL server, so they gave me my own mirror server. the data retrieval now takes 45 to 60 minutes, and that is only for one month end of one particular slice of data.

so, any suggestions for bigger PC based database software app which access can read would be tremendously helpful.

thanks in advance.

sportsguy

You have got to be kidding! I would suggest 2 billion in sales (ie American bullshit eg General Motors, we are biggest so therefor we must be best) but 20 cents in data security / IT planning. Long before now, did it not occur to you people that you needed something a little more powerful than Access to run your compnay?????

 

[Drac]

I have databases totalling nearly 30 gigs of data, and one access database can barely hold one year of data. Therefore, I have databases by year, Invoices_2009, Invoices_2008, etc.

Is there any PC software available which can create access 2007 readable databases larger than 2 Gigs with multiple tables, which I can store on my terabyte esata drive?

I tried MySQL, but couldn't get it to work. If anyone has experience with MySQL creating a datawarehouse on an esata drive to their laptop, I would like to communicate with you.

I'd suggest having someone take a look at your database design, and seeing if perhaps normalizing it more fully might be possible. You'd be amazed at how that can sometimes compact a database system.

In fact, if you wish, I could do so (some 30 years' programming experience, 15 w/Access)... let me know if this interests you.

 

[gemma-the-husky]

if you havent got inhouse expertise, get a consultant in, and pay some money for a considered solution

i just dont believe a massive company cannot find resources to resolve this

 

[Atomic Shrimp]

Concur. I realise that's not the answer the OP wants, but if it's truly that big and important an operation/db, trying to fumble your lonesome way through it is too risky a plan for any of us to recommend.

 

[MSAccessRookie]

There are any number of experts out here who are familiar with My-SQL and SQL Server as well as Oracle and other larger Databases (including myself). Depending on how much information you are willing/able to share with us, we could help you here online. If that is not possible, then I agree with gemma that you probably want to hire an outside expert to review your current database design, and help you to determine the way to proceed.

 

[gemma-the-husky]

to my mind, its not so much whether we have experts here who can help, its more that a "mega" company should have an IT "plan" that will include infrastructure, software and so forth. If they havent got one, they need one, and its rubbish to suggest there are no funds for it. IT planning must be critical to any company of this size, and they need more than a bit of informal advice.

 

[boblarson]

to my mind, its not so much whether we have experts here who can help, its more that a "mega" company should have an IT "plan" that will include infrastructure, software and so forth. If they havent got one, they need one, and its rubbish to suggest there are no funds for it. IT planning must be critical to any company of this size, and they need more than a bit of informal advice.

Gotta agree with Dave on this. A company that size should not be caught "flat-footed" here like this. Something doesn't smell right about this.

 

[sportsguy]

I like the way people want to interpret the background for commenting, instead of answering the question.

First, the company doesn't run on MS Access. If it did, then I would agree with you ALL.
Second, if I wasn't around, the company would still operate just fine. I happen to like access's interface and reports as easier to use than writing SQL code, as well as I have used it since 1993. Whether I asked the question or not does not change the fact that the company runs just fine without me or access, and would continue so if i left today.
Third, there is no need to hire a consultant just because I asked around for some information. How people apply their own views of the world to a request for information on this board to start, would be a great study in web based psychology.

Fourth, my tables are normalized. The reason I gave the background information is so that people would understand the reason for my question is NOT a normalization question. A database at 7 million plus records runs into locking issues on the indexes when the database has three indexes and is only 300 megs is not a normalization issue. (AS ONE EXAMPLE of an issue I have, but not the only issue, and not an issue I haven't been able to work around.)

Five, get over yourselves about the size and quality of the company, and maybe just provide any insight to the question asked? if you can't that's fine, just move on to someone you can help.

thanks

sportsguy

 

[sportsguy]

and as a side note to all you who have determined that the IT department just doesn't smell right, the controller from my company went to a larger GE subsidiary, who I talk to regularly, and he is just shocked at how old and dysfunctional a GE subsidiary can be, as compared to the company he left, where we can answer any question asked within a day. (you just have to ask me, not just any jamoke in the company)

sportsguy

 

[Zaeed]

People have responded to your question, and have said to get a professional in to do it for you..

The simple fact that you're trying to use Access to handle such a large volume of data is a joke.. And furthermore, why is a Finance Manager (no offence to you) trying to build a database for your company..
If your company is willing to just give you another server, why wouldn't they invest in a proper system to handle the vast number of invoices you have to process.. Its like a company giving a worker a truck to fill with dirt, but instead of getting a digger to do it they make him build his own shovel.. If you honestly think this is good business practice then you're living in a fantasy world..

Get your company to have a dedicated PROFESSIONAL system built for you..