I have Office 2003 Professional. At work it is conventional to use Access for
some tasks. As most of you will appreciate, Access is a mere toy. Almost any
task that could (or more usually, could not) be achieved in Access can be
performed much more easily in Excel. We have a number of large databases (
300,000 records) that I want to rationalise and modernise in Excel. My
ultimate aim is to trash Access and do everything in one spreadsheet. Is this
possible with some kind soles code snippet? Please help me impress my boss!

It sounds to me that you are going the wrong way. What is wrong with using
Access? 300,000 records for Excel is excessive, for one thing a spreadsheet
only has 65,000+ rows.

If you want to impress your boss, find ways to extract summarised data
(small amounts, nowhere near 300,000 records) and use Excel's proper
functionality to analyse it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"KJ" wrote in message
...
I have Office 2003 Professional. At work it is conventional to use Access
for
some tasks. As most of you will appreciate, Access is a mere toy. Almost
any
task that could (or more usually, could not) be achieved in Access can be
performed much more easily in Excel. We have a number of large databases
(
300,000 records) that I want to rationalise and modernise in Excel. My
ultimate aim is to trash Access and do everything in one spreadsheet. Is
this
possible with some kind soles code snippet? Please help me impress my
boss!

Apologies should read 30,000 records

"Bob Phillips" wrote:

It sounds to me that you are going the wrong way. What is wrong with using
Access? 300,000 records for Excel is excessive, for one thing a spreadsheet
only has 65,000+ rows.

If you want to impress your boss, find ways to extract summarised data
(small amounts, nowhere near 300,000 records) and use Excel's proper
functionality to analyse it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"KJ" wrote in message
...
I have Office 2003 Professional. At work it is conventional to use Access
for
some tasks. As most of you will appreciate, Access is a mere toy. Almost
any
task that could (or more usually, could not) be achieved in Access can be
performed much more easily in Excel. We have a number of large databases
(
300,000 records) that I want to rationalise and modernise in Excel. My
ultimate aim is to trash Access and do everything in one spreadsheet. Is
this
possible with some kind soles code snippet? Please help me impress my
boss!

Same response.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"KJ" wrote in message
...
Apologies should read 30,000 records

"Bob Phillips" wrote:

It sounds to me that you are going the wrong way. What is wrong with
using
Access? 300,000 records for Excel is excessive, for one thing a
spreadsheet
only has 65,000+ rows.

If you want to impress your boss, find ways to extract summarised data
(small amounts, nowhere near 300,000 records) and use Excel's proper
functionality to analyse it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"KJ" wrote in message
...
I have Office 2003 Professional. At work it is conventional to use
Access
for
some tasks. As most of you will appreciate, Access is a mere toy.
Almost
any
task that could (or more usually, could not) be achieved in Access can
be
performed much more easily in Excel. We have a number of large
databases
(
300,000 records) that I want to rationalise and modernise in Excel. My
ultimate aim is to trash Access and do everything in one spreadsheet.
Is
this
possible with some kind soles code snippet? Please help me impress my
boss!

I agree Bob. Excel and Access both have there place. Let Access do what it
does best and let Excel do what it does best. Both tools working hand in
hand.

If you are dealing with 10's of thousands or in your case 100's of thousands
records, Access is the tool. Excel will work great as a reporting tool. Use
Access to get your subsets and then use Excel to finish up your reporting
requirements.

You are not going to create a report with 10+ thousand records, so use
Access to hold your records and use Excel to create meaningful reports based
upon data gleamed from these records.

--
Damon Longworth

Don't miss out on the 2005 Excel User Conference
Sept 16th and 17th
Stockyards Hotel - Ft. Worth, Texas
www.ExcelUserConference.com


"Bob Phillips" wrote in message
...
It sounds to me that you are going the wrong way. What is wrong with using
Access? 300,000 records for Excel is excessive, for one thing a
spreadsheet
only has 65,000+ rows.

If you want to impress your boss, find ways to extract summarised data
(small amounts, nowhere near 300,000 records) and use Excel's proper
functionality to analyse it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"KJ" wrote in message
...
I have Office 2003 Professional. At work it is conventional to use Access
for
some tasks. As most of you will appreciate, Access is a mere toy. Almost
any
task that could (or more usually, could not) be achieved in Access can be
performed much more easily in Excel. We have a number of large databases
(
300,000 records) that I want to rationalise and modernise in Excel. My
ultimate aim is to trash Access and do everything in one spreadsheet. Is
this
possible with some kind soles code snippet? Please help me impress my
boss!

OK €“ Here is the lowdown:-



weve gotten around 210 databases few enough to each have their own colunn.
CAN you BELIEVE how old fashioned they are. Theyre lucky that Ive joined
them



Anyway what I need ur help with is putting all of these dataqbases into one
sheet in one workbook. Most of these pesky DBs are 30-50k but a few, not more
than 20 are 100k and 5 are 300k. I plan for the other worksheets in the
same book to handle the overflow.

Because all my databases are next to each other we can simply mix and match.
WHO needs access??



Now the plan is to convert ALL the companies records before the bosses get
in. =Knowing them it wont be till after lunch on Monday!



By the way, can I colour the payroll and staff records BLACK to hide them
from prying eyes?

As soon as I get the code I need, I can uninstall Access everywhere in the
company and we can move into the 21st CENTURY!!



Thanks guys, you will have the satisfaction of knowing that you are helping
progress.


TIA!

KJ


"Damon Longworth" wrote:

I agree Bob. Excel and Access both have there place. Let Access do what it
does best and let Excel do what it does best. Both tools working hand in
hand.

If you are dealing with 10's of thousands or in your case 100's of thousands
records, Access is the tool. Excel will work great as a reporting tool. Use
Access to get your subsets and then use Excel to finish up your reporting
requirements.

You are not going to create a report with 10+ thousand records, so use
Access to hold your records and use Excel to create meaningful reports based
upon data gleamed from these records.

--
Damon Longworth

Don't miss out on the 2005 Excel User Conference
Sept 16th and 17th
Stockyards Hotel - Ft. Worth, Texas
www.ExcelUserConference.com


"Bob Phillips" wrote in message
...
It sounds to me that you are going the wrong way. What is wrong with using
Access? 300,000 records for Excel is excessive, for one thing a
spreadsheet
only has 65,000+ rows.

If you want to impress your boss, find ways to extract summarised data
(small amounts, nowhere near 300,000 records) and use Excel's proper
functionality to analyse it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"KJ" wrote in message
...
I have Office 2003 Professional. At work it is conventional to use Access
for
some tasks. As most of you will appreciate, Access is a mere toy. Almost
any
task that could (or more usually, could not) be achieved in Access can be
performed much more easily in Excel. We have a number of large databases
(
300,000 records) that I want to rationalise and modernise in Excel. My
ultimate aim is to trash Access and do everything in one spreadsheet. Is
this
possible with some kind soles code snippet? Please help me impress my
boss!

"KJ" wrote in message
...

we've gotten around 210 databases few enough to each have their own
colunn.
CAN you BELIEVE how old fashioned they are. They're lucky that I've joined
them

Not if you think the answer is to migrate to Excel!

Anyway what I need ur help with is putting all of these dataqbases into
one
sheet in one workbook. Most of these pesky DBs are 30-50k but a few, not
more
than 20 are 100k and 5 are 300k. I plan for the other worksheets in
the
same book to handle the overflow.

Because all my databases are next to each other we can simply mix and
match.
WHO needs access??

You do!

Now the plan is to convert ALL the companies records before the bosses get
in. =Knowing them it won't be till after lunch on Monday!

That's right, the workers do all the work, the bosses take all the credit!

By the way, can I colour the payroll and staff records BLACK to hide
them
from prying eyes?

Ugh?

As soon as I get the code I need, I can uninstall Access everywhere in the
company and we can move into the 21st CENTURY!!

That is not progress.

Aaron, where are you when we need you! Or are you Aaron just being perverse?

KJ,

I've got to agree with the other posts so far - Access is a fantastic tool.
Have you considered bringing the different tables from each database into
one consolidated location? That alone might make your life easier.

If that's not enough, you might try using linked data in Excel. Excel can
import data through a database query <Data - Get External Data - change it
in Excel and it changes the linked Access file, and vice versa.

You might want to play around with one of these ideas before wiping out the
old databases. Most managers don't like change, especially when they aren't
involved. Make copies of the databases before you play, just in case you
click the wrong button!

Eddie

Hey Eddie.

"Eddie" wrote in message
...
KJ,

I've got to agree with the other posts so far - Access is a fantastic
tool.

Well I think we start to differe here. I think it is a severely limited
tool, but it will manage thousands of data records better than Excel, has
built-in functionality to make data maintenance relatively simple.

Have you considered bringing the different tables from each database into
one consolidated location? That alone might make your life easier.

Doesn't that defeat the object of a relational database?

If that's not enough, you might try using linked data in Excel. Excel can
import data through a database query <Data - Get External Data - change
it
in Excel and it changes the linked Access file, and vice versa.

That's the way to go. Use Excel as a front-end to the database. Maintain the
data simply in Access, do you basic reports in Access, use Excel to add the
value for scenario testing etc.

You might want to play around with one of these ideas before wiping out
the
old databases. Most managers don't like change, especially when they
aren't
involved. Make copies of the databases before you play, just in case you
click the wrong button!

If he worked for me and wiped out Access databases to load all that data in
Excel, I would fire him immediately <bg

Hi Guys.

If there is any space on the sheets, can we code the word processing there?

GOODBYE Office, Hello EXCEL! Finally I have the sense that, with you help,
we are making real PRGORESS!

BTW We can wait to Tuesday to unload all the copies of Word, although,
really would like to unload Access AND word in one go.

Can I use the same vbs Kill routine I have?

TIA!

KJ



"Bob Phillips" wrote:

Hey Eddie.

"Eddie" wrote in message
...
KJ,

I've got to agree with the other posts so far - Access is a fantastic
tool.

Well I think we start to differe here. I think it is a severely limited
tool, but it will manage thousands of data records better than Excel, has
built-in functionality to make data maintenance relatively simple.

Have you considered bringing the different tables from each database into
one consolidated location? That alone might make your life easier.

Doesn't that defeat the object of a relational database?

If that's not enough, you might try using linked data in Excel. Excel can
import data through a database query <Data - Get External Data - change
it
in Excel and it changes the linked Access file, and vice versa.

That's the way to go. Use Excel as a front-end to the database. Maintain the
data simply in Access, do you basic reports in Access, use Excel to add the
value for scenario testing etc.

You might want to play around with one of these ideas before wiping out
the
old databases. Most managers don't like change, especially when they
aren't
involved. Make copies of the databases before you play, just in case you
click the wrong button!

If he worked for me and wiped out Access databases to load all that data in
Excel, I would fire him immediately <bg