Exporting reports from VB

  • Thread starter Thread starter Keith
  • Start date Start date
K

Keith

I generate a report from VB and would like to export (as
snapshot) the report as well after the report has been
generated. I haven't a clue how to do it from VB and I've
been searching through help files with no luck so far. Any
help would be appreciated.

Thanks.
 
Keith:

Dim objAccess as Object
Const acReport = 3
Const acFormatSnapshot = "Snapshot Format (*.snp)"

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase("C:\some dir\some file.mdb")
objAccess.DoCmd.OutputTo acReport, "YourReportName", acFormatSnapshot, _
"c:\some dir\some output file.snp"
DoEvents
objAccess.Quit
Set objAccess = Nothing
 
Excellent! Thank you.
-----Original Message-----
Keith:

Dim objAccess as Object
Const acReport = 3
Const acFormatSnapshot = "Snapshot Format (*.snp)"

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase("C:\some dir\some file.mdb")
objAccess.DoCmd.OutputTo acReport, "YourReportName", acFormatSnapshot, _
"c:\some dir\some output file.snp"
DoEvents
objAccess.Quit
Set objAccess = Nothing
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg





.
Click to expand...
 
Oops, spoke to soon. Here's my code with the "OutputTo"
line added. When it exports the report it contains the
entire table. I need to export the report I get with
the "UserID" field as the filter.

Set objAccess = CreateObject("Access.Application")
With objAccess
.OpenCurrentDatabase filepath:=dbName
.Visible = False
.DoCmd.OpenReport rptName, , , "[UserID] = " &_
PrintUserID
.DoCmd.OutputTo acReport, rptName,_
acFormatSnapshot, "C:\Data\" &_ dataUser.Recordset!Name_
& ".snp"
.Quit
End With
Set objAccess = Nothing
 
Keith:

OutputTo has no built in way to filter a report as you've found. The only
real way to do it, is to, within Access, create a module that has a module
level sting variable to hold the SQL based filter you want to apply.

Then create one function in the same module that you can call from VB to set
the SQL filter into the string var. Create a second function that returns
the filter, that the report can call in its On open event. In the report's
on Open event add code like this:

Dim strFilterReturn$
strFilterReturn = funcGetFilter()
If len(strFilterReturn) > 0 Then
Me.Filter = strFilterReturn
Me.FilterOn = True
End if

Last in your VB code, use the Application.Run method (objAccess.Run) to run
the function you've created in Access and pass it the SQL filter you want to
apply.

HTH
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

Keith said:
Oops, spoke to soon. Here's my code with the "OutputTo"
line added. When it exports the report it contains the
entire table. I need to export the report I get with
the "UserID" field as the filter.

Set objAccess = CreateObject("Access.Application")
With objAccess
.OpenCurrentDatabase filepath:=dbName
.Visible = False
.DoCmd.OpenReport rptName, , , "[UserID] = " &_
PrintUserID
.DoCmd.OutputTo acReport, rptName,_
acFormatSnapshot, "C:\Data\" &_ dataUser.Recordset!Name_
& ".snp"
.Quit
End With
Set objAccess = Nothing
-----Original Message-----
Excellent! Thank you.

.
Click to expand...
Click to expand...
 
Would it be easier to do a "SELECT INTO" the records into
a new table, run the DoCmd.OutputTo, then delete the
table? There would only be a max of about 150 records for
the report. Would doing this have an affect on performance?

Thanks for all your help.

Keith
-----Original Message-----
Keith:

OutputTo has no built in way to filter a report as you've found. The only
real way to do it, is to, within Access, create a module that has a module
level sting variable to hold the SQL based filter you want to apply.

Then create one function in the same module that you can call from VB to set
the SQL filter into the string var. Create a second function that returns
the filter, that the report can call in its On open event. In the report's
on Open event add code like this:

Dim strFilterReturn$
strFilterReturn = funcGetFilter()
If len(strFilterReturn) > 0 Then
Me.Filter = strFilterReturn
Me.FilterOn = True
End if

Last in your VB code, use the Application.Run method (objAccess.Run) to run
the function you've created in Access and pass it the SQL filter you want to
apply.

HTH
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

Oops, spoke to soon. Here's my code with the "OutputTo"
line added. When it exports the report it contains the
entire table. I need to export the report I get with
the "UserID" field as the filter.

Set objAccess = CreateObject("Access.Application")
With objAccess
.OpenCurrentDatabase filepath:=dbName
.Visible = False
.DoCmd.OpenReport rptName, , , "[UserID] = " &_
PrintUserID
.DoCmd.OutputTo acReport, rptName,_
acFormatSnapshot, "C:\Data\" &_ dataUser.Recordset!Name_
& ".snp"
.Quit
End With
Set objAccess = Nothing
-----Original Message-----
Excellent! Thank you.

-----Original Message-----
Keith:

Dim objAccess as Object
Const acReport = 3
Const acFormatSnapshot = "Snapshot Format (*.snp)"

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase("C:\some dir\some file.mdb")
objAccess.DoCmd.OutputTo acReport, "YourReportName",
acFormatSnapshot, _
"c:\some dir\some output file.snp"
DoEvents
objAccess.Quit
Set objAccess = Nothing
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg


message
I generate a report from VB and would like to export (as
snapshot) the report as well after the report has been
generated. I haven't a clue how to do it from VB and
I've
been searching through help files with no luck so far.
Any
help would be appreciated.

Thanks.


.

.
Click to expand...
Click to expand...


.
Click to expand...
 
Keith:

Actually the temp table idea would be slower. That's because you are
creating a lot more network traffic to pull the records accross to the local
app via jet and sent them back into a temp table. By doing a simple filter
(although the implementation's a little conveluted) you only pull the
records across once.
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

Keith said:
Would it be easier to do a "SELECT INTO" the records into
a new table, run the DoCmd.OutputTo, then delete the
table? There would only be a max of about 150 records for
the report. Would doing this have an affect on performance?

Thanks for all your help.

Keith
-----Original Message-----
Keith:

OutputTo has no built in way to filter a report as you've found. The only
real way to do it, is to, within Access, create a module that has a module
level sting variable to hold the SQL based filter you want to apply.

Then create one function in the same module that you can call from VB to set
the SQL filter into the string var. Create a second function that returns
the filter, that the report can call in its On open event. In the report's
on Open event add code like this:

Dim strFilterReturn$
strFilterReturn = funcGetFilter()
If len(strFilterReturn) > 0 Then
Me.Filter = strFilterReturn
Me.FilterOn = True
End if

Last in your VB code, use the Application.Run method (objAccess.Run) to run
the function you've created in Access and pass it the SQL filter you want to
apply.

HTH
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

Oops, spoke to soon. Here's my code with the "OutputTo"
line added. When it exports the report it contains the
entire table. I need to export the report I get with
the "UserID" field as the filter.

Set objAccess = CreateObject("Access.Application")
With objAccess
.OpenCurrentDatabase filepath:=dbName
.Visible = False
.DoCmd.OpenReport rptName, , , "[UserID] = " &_
PrintUserID
.DoCmd.OutputTo acReport, rptName,_
acFormatSnapshot, "C:\Data\" &_ dataUser.Recordset!Name_
& ".snp"
.Quit
End With
Set objAccess = Nothing

-----Original Message-----
Excellent! Thank you.

-----Original Message-----
Keith:

Dim objAccess as Object
Const acReport = 3
Const acFormatSnapshot = "Snapshot Format (*.snp)"

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase("C:\some dir\some
file.mdb")
objAccess.DoCmd.OutputTo acReport, "YourReportName",
acFormatSnapshot, _
"c:\some dir\some output file.snp"
DoEvents
objAccess.Quit
Set objAccess = Nothing
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg


message
I generate a report from VB and would like to export
(as
snapshot) the report as well after the report has been
generated. I haven't a clue how to do it from VB and
I've
been searching through help files with no luck so far.
Any
help would be appreciated.

Thanks.


.

.
Click to expand...


.
Click to expand...
Click to expand...
 
I'll give your recommendation a try next week and let you
know how it works out. Thanks again for your help.

Keith
-----Original Message-----
Keith:

Actually the temp table idea would be slower. That's because you are
creating a lot more network traffic to pull the records accross to the local
app via jet and sent them back into a temp table. By doing a simple filter
(although the implementation's a little conveluted) you only pull the
records across once.
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

Would it be easier to do a "SELECT INTO" the records into
a new table, run the DoCmd.OutputTo, then delete the
table? There would only be a max of about 150 records for
the report. Would doing this have an affect on performance?

Thanks for all your help.

Keith
-----Original Message-----
Keith:

OutputTo has no built in way to filter a report as
Click to expand...
you've
found. The only
real way to do it, is to, within Access, create a
Click to expand...
module
that has a module
level sting variable to hold the SQL based filter you want to apply.

Then create one function in the same module that you
Click to expand...
can
call from VB to set
the SQL filter into the string var. Create a second function that returns
the filter, that the report can call in its On open event. In the report's
on Open event add code like this:

Dim strFilterReturn$
strFilterReturn = funcGetFilter()
If len(strFilterReturn) > 0 Then
Me.Filter = strFilterReturn
Me.FilterOn = True
End if

Last in your VB code, use the Application.Run method (objAccess.Run) to run
the function you've created in Access and pass it the
Click to expand...
SQL
filter you want to
apply.

HTH
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

Oops, spoke to soon. Here's my code with the "OutputTo"
line added. When it exports the report it contains the
entire table. I need to export the report I get with
the "UserID" field as the filter.

Set objAccess = CreateObject("Access.Application")
With objAccess
.OpenCurrentDatabase filepath:=dbName
.Visible = False
.DoCmd.OpenReport rptName, , , "[UserID] = " &_
PrintUserID
.DoCmd.OutputTo acReport, rptName,_
acFormatSnapshot, "C:\Data\" &_ dataUser.Recordset! Name_
& ".snp"
.Quit
End With
Set objAccess = Nothing

-----Original Message-----
Excellent! Thank you.

-----Original Message-----
Keith:

Dim objAccess as Object
Const acReport = 3
Const acFormatSnapshot = "Snapshot Format (*.snp)"

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase("C:\some dir\some
file.mdb")
objAccess.DoCmd.OutputTo acReport, "YourReportName",
acFormatSnapshot, _
"c:\some dir\some output file.snp"
DoEvents
objAccess.Quit
Set objAccess = Nothing
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg


message
I generate a report from VB and would like to export
(as
snapshot) the report as well after the report
Click to expand...
has
been
generated. I haven't a clue how to do it from VB and
I've
been searching through help files with no luck
Click to expand...
so
far.
Any
help would be appreciated.

Thanks.


.

.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top