Printing SQL Results

jfisher88
11-08-2004, 01:22 PM
I am using VB as the interface to an Access DB.
When I run a query on the database what is the best way to print my results?

I have tried making a form the size of a piece of paper and put a DBgrid on it that filled the page and then used print form. That works OK but the text dosen't come out as clear as if you were using a word processor. It looks like text when you do a print screen which that's all that it probly is.

what is a better way to do this?

VBJoe
11-08-2004, 05:35 PM
I've attached a .zip file that contains a Form I created that will do what you need. I've used it for some time now for creating quick and easy reports in a Web Browser Control. The Form has a couple easy methods that you'll need to use:

1) Navigate - works much like the Navigate method of the Web Browser. Pass it a file or URL and it will open in the report window
2) BuildReport - pass a SQL query and the connection string, and it will build the report from the query. This is the one you need.
3) BuildReportFromListView - pass a ListView control to this method, and it will build and HTML report from the contents of the ListView. Remember that the ListView must be in Report mode for this method to work.

Before adding the Report Form to your project, make sure that you've added the "Microsoft Windows Common Controls" and "Microsoft Internet Controls" components. They're needed by the Report Form.

Normally, I don't give out code, but I've used this one so many times and found it so useful that I'll make an exception.

To use the Report Form, it's as easy as this:

Dim sql As String, frm As frmReport
Set frm = New frmReport

sql = "SELECT FirstName, LastName, UserName, Phone FROM Users ORDER BY LastName"

frm.BuildReport sql, YourConnectionString, "User List"
frm.Show

If you have any questions, feel free to ask.

jfisher88
11-09-2004, 09:19 AM
I tried to implement your code and form into my project and I found that there is on problem. I'm using DAO and you are using ADO. Is there a way to use your code with DAO? My project is all setup for DAO and that's all that I am really familiar with. I have just recently learned how to use a database and VB together all self taught. Me personally, I found that DAO was easier to get the results that I was looking for. I am aware that ADO is the latest and greatest way to go, but DAO seemed easier. I must also admit that although I have used VB for a few years now, I'm still a novice.

If you can help that would be greatly appreciated.
Thanks in advance for your help.
Jason

loquin
11-09-2004, 11:29 AM
ADO is not just the "ADO is the latest and greatest way to go." It's BEEN the best approach to database access since before the release of VB6, 5+ years ago. That's a LOONNNGGGG time in software development years! This means that it's going to be difficult to find timely and pertinent responses to DAO questions. I've forgotten a great deal of the DAO detail that I once knew, and I won't willingly revisit it - it's just not germane for me to do so.

Since most programmers would not willingly take steps backwards, it would probably be more effective for you to finally abandon DAO, and make the move to ADO. (Just stay away from data controls - data OBJECTS are the way to go!)

VBJoe
11-09-2004, 01:55 PM
Sorry jfisher, I don't have the time to implement a DAO solution. (and I'm not sure I would if I had the time. :rolleyes: )

Like loquin pointed out, ADO is the standard now for VB6 development. I started learning VB during the infancy of ADO, so my DAO knowledge is probably less even then yours. If I were you, I'd look to moving over to ADO, ASAP. There are some great tutorials pinned to the top of the Database and Reporting Forum page, and we can answer any questions you might have.

To test the Report Form out, and get a basic idea of what it can do, create a new project. Add a reference to the ADODB Library (MDAC 2.x library - 2.7 or 2.8 are preferred. If you don't have the latest, download it here (http://www.microsoft.com/downloads/details.aspx?FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c&displaylang=en)). Make sure you add frmReport to the project. On the Main Form (Form1 probably), place a button, then add this code to that button's Click event procedure:

Private Sub Command1_Click()
Dim sql As String, ConnectionString As String
Dim frm As frmReport
Set frm = New frmReport

'Make sure you put the correct DB path here:
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\data.mdb"

sql = "SELECT * FROM SomeTable" 'Create a valid query here

frm.BuildReport sql, ConnectionString, "Report Title"
frm.Show
End Sub

jfisher88
11-11-2004, 08:39 AM
VBJoe
I have just tried you example. It's just what I am looking for. Now I just hope that I can impliment it into my app.
Thanks again!

jfisher88
11-11-2004, 09:25 AM
When I set sql = "SELECT * FROM services" the report loads everything in the DB.

When I set sql = "SELECT * FROM services WHERE [SEM PERMIT] LIKE" & " '*" & form8.Combo1.Text & "*'" & " ORDER BY [address]" the report has not data.

This is the exact statement without the variables:
SELECT * FROM services WHERE [SEM PERMIT] LIKE LAPEERCO ORDER BY [address]

This SQL statement works when I send it to the DAO object so I know it works.

Is there some other way I have to write my SQL Statement?

jfisher88
11-11-2004, 12:02 PM
Nevermind, I got it to work thanks a million!
I Love it
Jfisher88

VBJoe
11-11-2004, 02:45 PM
Oh, I forgot to mention something. The Report Form works well with computers that have IE6 installed, and seems to work okay with IE5.5 (but I haven't tested it extensively). If your end user has an older version of IE, then you'll need to test the Report Form thoroughly, or have them upgrade to the most recent version of IE.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum