Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    Unanswered: Your thoughts on this script?

    I think I am almost completed just missing one or two pieces I believe. If you could please just take a quick look to give your opinion I would be grateful.

    Some background where I am at now with objects in Access

    I have a linked table called DBO_VW_PPDREPORT off of this I built a query that is called qryPPDREPORT. This query is essentially the same as the view from SSMS with the data range choked down with a hard coded value ('2014-11')

    The code is tagged below. The end game is to run the script to loop through the request and save off the filtered results into an Excel file. Pretty basic for a experts however I am not.

    I did have a parameter set into the query (qryPPDREPORT) however I felt that may be problematic so I hard coded the value in the query.

    Code is listed below which I found off of another Access site. Error message is run-time error 3061 two few parameters excepted two. ***I'm probably completely missing something.

    HTML Code:
    Private Sub Command9_Click()  'Help me :)
    
    Dim qdf As DAO.QueryDef
    Dim dbs As DAO.Database
    Dim rstMgr As DAO.Recordset
    Dim strSQL As String, strTemp As String, strMgr As String
    
    ' Replace PutEXCELFileNameHereWithoutdotxls with actual EXCEL
    ' filename without the .xls extension
    ' (for example, MyEXCELFileName, BUT NOT MyEXCELFileName.xls)
    Const strFileName As String = "Please"
    Const strQName As String = "zExportQuery"
    
    Set dbs = CurrentDb
    
    ' Create temporary query that will be used for exporting data;
    ' we give it a dummy SQL statement initially (this name will
    ' be changed by the code to conform to each manager's identification)
    
    strTemp = dbs.TableDefs(0).Name
    strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
    Set qdf = dbs.CreateQueryDef(strQName, strSQL)
    qdf.Close
    strTemp = strQName
    
    ' *** code to set strSQL needs to be changed to conform to your
    ' *** database design -- ManagerID and EmployeesTable need to
    ' *** be changed to your table and field names
    ' Get list of ManagerID values -- note: replace my generic table and field names
    ' with the real names of the EmployeesTable table and the ManagerID field
    
    strSQL = "SELECT DISTINCT Supplier FROM qryPPDREPORT;"   ' Getting error message here stating two few parameters
    Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
    
    ' Now loop through list of ManagerID values and create a query for each ManagerID
    ' so that the data can be exported -- the code assumes that the actual names
    ' of the managers are in a lookup table -- again, replace generic names with
    ' real names of tables and fields
    If rstMgr.EOF = False And rstMgr.BOF = False Then
          rstMgr.MoveFirst
          Do While rstMgr.EOF = False
    ' *** code to set strMgr needs to be changed to conform to your
    ' *** database design -- ManagerNameField, ManagersTable, and
    ' *** ManagerID need to be changed to your table and field names
    ' *** be changed to your table and field names
                strMgr = DLookup("SUPPLIER", "qryPPDREPORT", _
                      "SUPPLIER_ID = " & rstMgr!SUPPLIER_ID.Value)
    ' *** code to set strSQL needs to be changed to conform to your
    ' *** database design -- ManagerID, EmployeesTable need to
    ' *** be changed to your table and field names
                strSQL = "SELECT * FROM qryPPDREPORT WHERE " & _
                      "SUPPLIER_ID = " & rstMgr!SUPPLIER_ID.Value & ";"
                Set qdf = dbs.QueryDefs(strTemp)
                qdf.Name = "q_" & strMgr
                strTemp = qdf.Name
                qdf.sql = strSQL
                qdf.Close
                Set qdf = Nothing
    ' Replace C:\FolderName\ with actual path
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
                      strTemp, "C:\MFG\" & strFileName & ".xls"
                rstMgr.MoveNext
          Loop
    End If
    
    rstMgr.Close
    Set rstMgr = Nothing
    
    dbs.QueryDefs.Delete strTemp
    dbs.Close
    Set dbs = Nothing
    End Sub

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    One line bothers me in your procedure:
    Code:
    strTemp = dbs.TableDefs(0).Name
    This means that you retrieve the name of the first created table in the database. How can you be sure that it will always be the table you expect? I would explicitly provide the name of the table instead.
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    to be honest Im not going to plow through that amount of code to try and guess what is goign wrong
    the fact the code compiels sugests its not really a code issue, its a data issue, and to resolve that we actually need to see what the detail fo the code that is failign is. that means the actual values being passsed to the SQL engine. that usually means use the debugger, put a watch/breakpont font ehcode and identify what is actually happening on (or in the build up to where the error is reported)

    if that doens't cut it for you then perhaps you should go back to where you culled this code from and ask questions there.

    I gave up wading throught he code when I saw
    Code:
    strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
    which will alwasy result in a booelan false condition, so no rows will be returned from any query with that where clause. if you want rows returned using that dodge use where 1=1, of I suppose where 1 <> 0!

    just a comment I would expect there to be a eay of avoiding the dlookup (if you are running a query, which contains the source column(s) for the dlookup then it shoudl be possible to fold the dlookup into the query itself using a JOIN. don't use dlookup INSIDE a query, but use a joion
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Thank you for your input. I am not a VBA program others don't see it that way :")

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •