generating reports from a database

[irkle]

i am creating a client/server patient record database with vb6 as the front end and access 97 as the back end. one feature i would like to implement is the ability to generate reports and letters using data from the database. the database works as follows: patient data is entered and saved on a form. there is a search facility which finds records and displays the results in a form also. i would like to add a button to this form which generates a letter (ideally in word) which contains data displayed in the current form. i have tried using mail merge to do this, but i was hoping there was a better method for extracting data and displaying it in word.

any ideas?

 

[irkle]

also, is it possible to use the access reports feature to generate a report from within vb?

 

[irkle]

to clarify the problem a little - there are lots of tables in the database, and the reports need to be of the following format:

nameOfField: current value


for each patient stored in the database ie. i want to generate a list of field/value pairs.

 

[gwigton]

How are you connecting to the database with the vb front end, with a Data Enviroment or by hard coding it? In either case you could simply use the VB Data Report, or what I have done in the past to get a report results saved in Word is create the report in Access so that it includes all of the required data and then through code have your button open it up and save it in a .rtf format. An example would be if I wanted to generate a the lab activitys of all the students for a certain instructor I simply created the report that included all the instructors and the student information in that instructors group. Then in code I wrote a module that stated the following. Keep in mind that I haven't included anything about your connection since I'm not sure how you are doing this, and that you will have to take out the wrap around after you cut and paste it.

'first declare and set the Access object
Dim objAccess As Access.Application
Set objAccess = New Access.Application

'set the connection to the database if you are coding it rather than the Data Enviroment

With objAccess
objLatt.OpenCurrentDatabase strAppPath 'strAppPath represents the path to the database
.Visible = False 'opens the database without the user seeing it
.DoCmd.OpenReport "table_Name", acViewPreview, , "Where_argument_column_Name= "Where_argument", acHidden 'This opens the report in view preview and uses a where condition and shows the report as hidden so the user doen't see it.
.DoCmd.OutputTo acOutputReport, "rtf_file_Name", ".rtf", "locationPath" 'This saves the output of the report into a .rtf file of your naming choice
.CloseCurrentDatabase 'make sure to close both the database and the connection
End With

Set objAccess = Nothing

You will also have to add a reference to the MS Access Library. Let me know if you need me to elaborate more.

 

[irkle]

hi,

i have connected to the database by hardcoding it using ado... is it still possible to use the data environment to create a report? i have used the following code to connect:

Dim conn As New ADODB.connection
Dim Woon As New ADODB.Recordset
conn.Open Login.connection

and this code in a configuration file so that i can change the location of the database easily when i install it in it's final location:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\configure\database.mdb;Persist Security Info=False

is it possible to get the data environment to read this configuration file also, as i can't really hard code the location of the database as this will change when it is installed in the final location.

thanks for the help!

 

[gwigton]

On all of my VB apps that use a database on servers I create a module that first tests the location of the database. If it is the first time that the app runs it prompts me for a location by using a form that has a drive, directory, and the file list components. Once the user has found the correct database then clicks ok then it prompts them one more time verifying that the path is correct. If they select ok on that prompt it saves the path to the registry. (You could also change this by editing the registry remotely)

This gives you the ability to test this location each time the app is started again. You can use the Data Enviroment by refreshing the connection within the Data Enviroment but you will be facing the same issue of what if the database is moved, so this would be one way to cure that issue while still using your ADO connection. Just be sure to place the path of the database into a global variable.