[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Help sending ADODC query results to .txt file

Posted on 2004-11-12
6
Medium Priority
?
210 Views
Last Modified: 2013-12-25
I have an application where I have an input form to add data to my database ("Sales").
I have a form where I display all the sales in a DataGrid ("DG1").
I use ADODC1 on each form to connect to the database.

How can I get the results from DG1 to a mulit-line text box or to a .txt file? I would like to see the results on paper that has the header with the information below it.
Like:
DATE       QTY        ITEM          DESCRIPTION         PRICE
-------------------------------------------------------------------
1/1/02       1          1234           Whatever               $ 0.00

Thanks,

Jeff
0
Comment
Question by:fanguru1
  • 3
  • 3
6 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 12567009
Jeff,

The first thing to do is to get rid of ADODC and use ADO directly.  You would avoid a lot of problems this way.

To display your data in a text file you would most likely use loop through the recordset and write to a file:

http://www.officecomputertraining.com/vbtutorial/tutpages/page24.asp

It maybe easier to output your data to Excel, if you are interested in this method, let me know.

Leon
0
 

Author Comment

by:fanguru1
ID: 12567068
Leon,

Excel would be fine. I'm just looking for an easy way to format the data for printing.

Thanks,

Jeff
0
 
LVL 29

Accepted Solution

by:
leonstryker earned 1500 total points
ID: 12567246
Here is a good example on how to do this:

http://www.vb-helper.com/howto_access_to_excel_2.html

Leon
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:fanguru1
ID: 12568178
Leon,
I took your advise and used the code from access to excel and modified the code to fit my app. Here is the code I used.

Private Sub Command2_Click()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim excel_app As Excel.Application
Dim Excel_Sheet As Excel.Worksheet
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=C:\My Documents\Programming\BS Tree Sales\treelotsales1.mdb"
conn.Open
Set rs = conn.Execute("SALES")
Set excel_app = CreateObject("excel.application")
excel_app.Workbooks.Open App.Path & "\test1.xls"
Excel_Sheet.Cells.CopyFromRecordset rs
Excel_Sheet.Cells.Columns.AutoFit
End Sub

I now get an error on the next to the last line : Excel_Sheet.Cells.CopyFromRecordset rs
My error is Object Variable or With Block Variable not Set!

Can you provide some guidance for me.

Thanks
0
 

Author Comment

by:fanguru1
ID: 12568475
Leon,

I got it to work. I had left out a section.

I would like to be able to input the data on the second or third row to start. I could then put some row headers in. Can you help with that.

Thanks
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 12569309
Change this:

Excel_Sheet.Cells.CopyFromRecordset rs

To this:

'/ This will place the data starting in the first column of the third row
Excel_Sheet.Range("A3").CopyFromRecordset rs

Leon
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

872 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