[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

More Microsoft Query Columns

Posted on 1999-11-09
5
Medium Priority
?
179 Views
Last Modified: 2006-11-17
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.  
0
Comment
Question by:Lagzy
  • 2
  • 2
5 Comments
 
LVL 2

Expert Comment

by:yu108352
ID: 2195984
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
 
LVL 2

Accepted Solution

by:
WonHop earned 150 total points
ID: 2198535
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
 

Author Comment

by:Lagzy
ID: 2203425
Thank you for the answer.  I will give it a try this weekend.
0
 

Author Comment

by:Lagzy
ID: 2203446
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
 
LVL 2

Expert Comment

by:WonHop
ID: 2203463
Thank you Laqzy.  I am glad I could help.  :o)
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Implementing simple internal controls in the Microsoft Access application.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question