• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 407
  • Last Modified:

Procedure in Access will not place data on excel sheet

vba 2003
access 2003, excel 2003

I'm running a procedure from access and passing variables to the procedure in access. as shown below..
Public Sub cmdCreatePrebuilt_Click()
Dim strSeg As Variant
Dim strFam As Variant
Dim strCat As Variant
         
      strSeg = ComboBox1.Text
      strFam = ComboBox2.Text
       strCat = ComboBox3.Text
       
       
       
Dim remoteApp As Access.Application
Set remoteApp = New Access.Application
remoteApp.OpenCurrentDatabase "C:\Program Files\Crs_iCart\Enterprise_Reporting_Backend.mdb", False 'you may need to change the false into true
remoteApp.Visible = True
remoteApp.Run "CallExportSeg", strSeg, strFam, strCat
 remoteApp.CloseCurrentDatabase
 Set remoteApp = Nothing
       
       
        ' restore the old cursor or else you may never get it back
        MsgBox "Done", vbInformation, "Export Segment:Family:Category"
   
End Sub

The procedure will run but no data is posted to the excel sheet


A sheet is being created but no data is placed in the sheet ?


Thanks
fordraiders
procedure in access:
Public Sub ExportSegFamCat(strSeg As String, strFam As String, strCat As String)

Dim strsql As String, qd As DAO.QueryDef
Set qd = CurrentDb.QueryDefs("qrySegFamCat")
On Error GoTo ExportSegFamCat_Err

strsql = "SELECT dbo_WwgExtraChgo.material_no, dbo_WwgExtraChgo.long_description, dbo_WwgExtraChgo.short_description, dbo_WwgExtraChgo.current_catalog_page_no, "
strsql = strsql & "dbo_WwgExtraChgo.sales_status, dbo_WwgExtraChgo.condensed_mfr_model_no, dbo_BrandInformation.brand_name, "
strsql = strsql & "dbo_BrandInformation.private_label, dbo_WwgExtraChgo.green_material_flag, dbo_WwgExtraChgo.van_eligible, "
strsql = strsql & "dbo_WwgExtraChgo.segment_name, dbo_WwgExtraChgo.family_name, dbo_WwgExtraChgo.category_name"
strsql = strsql & " FROM dbo_WwgExtraChgo LEFT JOIN dbo_BrandInformation ON dbo_WwgExtraChgo.brand_no = dbo_BrandInformation.brand_no"
strsql = strsql & " WHERE dbo_WwgExtraChgo.segment_name = '" & strSeg & "' AND dbo_WwgExtraChgo.family_name = '" & strFam & "' AND dbo_WwgExtraChgo.category_name = '" & strCat & "' "
qd.SQL = strsql

DoCmd.TransferSpreadsheet acExport, 8, "qrySegFamCat", "C:\Program Files\Crs Enterprise\iFinal\Prebuilt_Export\PreBuilt_Export.xls", True, ""
qd.Close
Set qd = Nothing
ExportSegFamCat_Exit:
    Exit Sub
ExportSegFamCat_Err:
    If Err.Number = "3010" Then
MsgBox "The Excel Sheet is Already Open. Close The Open Workbook", vbCritical, "Error Exporting Prebuilt"
Exit Sub
End If
Resume ExportSegFamCat_Exit

End Sub

Open in new window

0
Fordraiders
Asked:
Fordraiders
1 Solution
 
SiddharthRoutCommented:
1) Declare the variables as String instead of variants in cmdCreatePrebuilt_Click
2) Debug print the variables in ExportSegFamCat and see if the values are being passed.

We will take it form there.

Sid
0
 
TinTombStoneCommented:
Rather than have one database open another then call a procedure to run a query

Why not just build the query in the first database to get the data from the second?

strsql = "SELECT dbo_WwgExtraChgo.* FROM Clients
 IN 'C:\Program Files\Crs_iCart\Enterprise_Reporting_Backend.mdb';"

Seems a lot easier than messing about calling secondary procedures
0
 
FordraidersAuthor Commented:
moteApp.Run "CallExportSeg", strSeg, strFam, strCat <-----I get an illegal dunction call here  <-------------
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
SiddharthRoutCommented:
Ah!

Unfortunately I don't have access to ACCESS at the moment but I think I know what the problem is. Instead of using

remoteApp.Run

use

remoteApp.DoCmd.RunMacro

Sid
0
 
Rory ArchibaldCommented:
Or use Run but call the actual subroutine itself directly. Mind you, if you are doing this from Excel, there's no need I can see to involve Access itself - just use ADO to run the query against the db.
0
 
FordraidersAuthor Commented:
THANKS
0

Featured Post

Industry Leaders: 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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now