Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2013
    Posts
    4

    Unanswered: SQL Server to Access Query

    Hi everyone,

    Im a little bit of newbie when it comes to sql so please bear with me while i try and explain what im trying to do. I have some SQL code that runs fine when queried with a MSQL server, it returns the vales i want in a speedy manner.

    I have some front end forms which i have done in MS Access, and linked back to the appropriate tables, i was hoping it would be a simple case of copying in the SQL into a new query in access with some name changes for the databases links ect.

    Unfortunately access complains about a Ambiguous JOIN, i have read a little bit about it and basically access doesnt know what joins to do in what order and the only way round it is to split the query into queries. This is where im totally lost as i have a few joins to do. I was hoping someone could point me in the right direction.

    Here is the SQL that runs perfectly well when not in Access.

    Code:
    SELECT  jobans.JOB_NUMBER AS "Job Number",
    		JOBANS.STR_ANS17 AS "ISBN No", 
    		ESTJOB.DETAILS1 AS "Title", 
    		ESTJOB.START_DATE AS "Order Date", 
    		SORDTR.ORDER_NO AS "Our Order No",
    		SFINORD.ORDER_NO AS "Client Order No", 
    		DEVFILE.DEL_QTY AS "Del Quantity", 
    		DEVHEAD.DEL_DATE AS "Del Date" 
    FROM   
    	(
    		
    		 (	
    				(
    					(DEVHEAD LEFT OUTER JOIN DEVFILE ON DEVHEAD.DEL_NO=DEVFILE.DEL_NO)
     				LEFT OUTER JOIN  SFINORD ON DEVHEAD.SFINORD_REF=SFINORD.REF) 
    		 LEFT OUTER JOIN  SORDTR ON DEVFILE.SORDTR_RECNUM=SORDTR.RECNUM) 
    	LEFT OUTER JOIN  ESTJOB ON SORDTR.JOB_NO=ESTJOB.JOB_NUMBER)
    INNER JOIN JOBANS ON SORDTR.JOB_NO = JOBANS.JOB_NUMBER
    
    
    
    
    
    
    
    WHERE JOBANS.JOB_NUMBER  IN 
    	
    	(SELECT estjob.JOB_NUMBER
    	FROM ESTJOB
    	INNER	JOIN JOBANS ON estjob.JOB_NUMBER = JOBANS.JOB_NUMBER
    	WHERE jobans.STR_ANS17 = '9780946439843')
    
    ORDER BY jobans.JOB_NUMBER DESC

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Why dont you use a pass-through query and let the server perform the query operation? Its really easy and probably more efficient:
    Code:
    Sub PassThroughQuery()
    
        Const c_QueryName As String = "QueryName"
        Const c_Connect As String = "ODBC;DRIVER={SQL Server};SERVER=ServerName;DATABASE=DatabaseName;Trusted_Connection=Yes;"
        Const c_SQL As String = "SELECT  jobans.JOB_NUMBER AS [Job Number], " & _
                                "JOBANS.STR_ANS17 AS [ISBN No], " & _
                                "ESTJOB.DETAILS1 AS [Title], " & _
                                "ESTJOB.START_DATE AS [Order Date], " & _
                                "SORDTR.ORDER_NO AS [Our Order No], " & _
                                "SFINORD.ORDER_NO AS [Client Order No], " & _
                                "DEVFILE.DEL_QTY AS [el Quantity], " & _
                                "DEVHEAD.DEL_DATE AS [Del Date] " & _
                                "FROM ( ( ( (DEVHEAD LEFT OUTER JOIN DEVFILE ON DEVHEAD.DEL_NO=DEVFILE.DEL_NO) " & _
                                "LEFT OUTER JOIN  SFINORD ON DEVHEAD.SFINORD_REF=SFINORD.REF) " & _
                                "LEFT OUTER JOIN  SORDTR ON DEVFILE.SORDTR_RECNUM=SORDTR.RECNUM) " & _
                                "LEFT OUTER JOIN  ESTJOB ON SORDTR.JOB_NO=ESTJOB.JOB_NUMBER) " & _
                                "INNER JOIN JOBANS ON SORDTR.JOB_NO = JOBANS.JOB_NUMBER " & _
                                "WHERE JOBANS.JOB_NUMBER  IN " & _
                                "(SELECT estjob.JOB_NUMBER " & _
                                "FROM ESTJOB " & _
                                "INNER JOIN JOBANS ON estjob.JOB_NUMBER = JOBANS.JOB_NUMBER " & _
                                "WHERE jobans.STR_ANS17 = '9780946439843') " & _
                                "ORDER BY jobans.JOB_NUMBER DESC;"
        Dim qdf As DAO.QueryDef
        
        If DCount("*", "MSysObjects", "Name = '" & c_QueryName & "'") > 0 Then CurrentDb.QueryDefs.Delete c_QueryName
        Set qdf = CurrentDb.CreateQueryDef(c_QueryName)
        With qdf
            .Connect = c_Connect
            .SQL = c_SQL
            .Close
        End With
        Set qdf = Nothing
        
    End Sub
    With:
    - QueryName is the name of the query in Access.
    - ServerName is the name of the SQL Server.
    - DatabaseName is the name of the database (on the SQL Server).

    Using this technique, you can also create a stored procedure (on the server) with the SQL expression then call this stored procedure to retrieve the rows in a query in Access:
    Code:
    Sub OpenSPQuery()
    
        Const c_QueryName As String = "QueryName"
        Const c_Connect As String = "ODBC;DRIVER={SQL Server};SERVER=ServerName;DATABASE=DatabaseName;Trusted_Connection=Yes;"
        Const c_SQL As String = "StoredProcedureName"
        
        Dim qdf As DAO.QueryDef
        
        If DCount("*", "MSysObjects", "Name = '" & c_QueryName & "'") > 0 Then CurrentDb.QueryDefs.Delete c_QueryName
        Set qdf = CurrentDb.CreateQueryDef(c_QueryName)
        With qdf
            .Connect = c_Connect
            .SQL = c_SQL
            .Close
        End With
        Set qdf = Nothing
        
    End Sub
    With:
    - QueryName is the name of the query in Access.
    - ServerName is the name of the SQL Server.
    - DatabaseName is the name of the database (on the SQL Server).
    - StoredProcedureName is the name of the stored procedure.
    Have a nice day!

  3. #3
    Join Date
    Apr 2013
    Posts
    4
    Thanks for replying Sinndho,

    Seems like a good idea, i shall try and implement it. Will let you know how i get on

    Cheers

  4. #4
    Join Date
    Apr 2013
    Posts
    4
    I did manage to get access to display results i wanted but it took far to long to retrieve the results. Using the passthrough method worked a treat, i modify the query via VBA on form update so i can pass data from the form.

    thankyou sinndho for sending me down the right track.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!

    Note: You could probably fine-tune your application and get faster results by calling a stored procedure, to which you can pass one or several parameters. That way, the SQL Server could re-use the execution plan of the stored procedure instead of compiling the SQL expression you pass through the query every time you call it.
    Have a nice day!

Posting Permissions

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