More Microsoft Query Columns

Using microsoft Query (wizard) on Excel 97/2000, how can I make a query with 200+ columns/fields (I'm brining in information from Access)?  Currently, every time I try to bring over 85-90 columns/fields from Access into Excel the query never happens.  And I need over 200 columns brought over at once.  
LagzyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

yu108352Commented:
Why don't you use multiple queries?  If you have a unique identifier for each record, you can hide it (to avoid repetition).  You can write a small VB macro to refresh these queries.
0
WonHopCommented:
You can this code from Access to Export to Excel.  This on is set to copy two queries into the same workbook and 2 different sheets.

Just copy the one query or table you are using.

Dim objApp As Excel.Application
Dim objBook as Excel.Workbook
Dim objsheet1 as Excel.Worksheet
Dim objsheet2 as Excel.Worksheet

Dim db as Database
Dim rst1 as Recordset
Dim rst2 as Recordset

Set objApp = New Excel.Application
objApp.Visible = True  'If you want to see it work.
Set objBook = objApp.Workbooks.Add
Set objSheet1 = objBook.Worksheets(1)
Set objSheet2 = objBook.Worksheets(2)

objSheet1.Name = "FirstTable"
objSheet2.Name = "SecondTable"

Set db = Currentdb()
Set rst1 = db.OpenRecordset("qryFirstTable", dbOpenSnapShot)
Set rst2 = db.OpenRecordset("qrySecondTable", dbOpenSnapShot)

objSheet1.Range("A1").CopyFromRecordset rst1
objSheet2.Range("A1").CopyFromRecordset rst2



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LagzyAuthor Commented:
Thank you for the answer.  I will give it a try this weekend.
0
LagzyAuthor Commented:
To yu108352:  I have tried multiple queries.  And while they work OK enough in Excel 97, they seem to cause problems in Excel 2000 and want to have a system that will avoid problems in both 97 & 2000.  Thank you anyway.
0
WonHopCommented:
Thank you Laqzy.  I am glad I could help.  :o)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.