Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post Upgrade to SQL Server (Access 2k2)

    I just installed Office XP and SQL Server Desktop Edition and upsized a database. It appears that the only major errors are involving a couple of queries that referenced text or combo boxes on forms. For example here is the query in Access SQL prior to upsizing.

    SELECT qryOpenOrders.Order_ID, qryOpenOrders.Order_Date, qryOpenOrders.Cust_Name, qryOpenOrders.Cust_DistCenter, qryOpenOrders.Cust_Store, qryOpenOrders.Cust_PO, qryOpenOrders.Date2HS, qryOpenOrders.ShipDate, qryOpenOrders.DateRec
    FROM qryOpenOrders
    WHERE (((Forms!FrmMain!txtSchOrderID) Is Null Or (Forms!FrmMain!txtSchOrderID)=[Order_ID]) And
    ((Forms!frmMain!txtSchOrderFrom) Is Null Or (Forms!frmMain!txtSchOrderFrom)<=[Order_Date]) And
    ((Forms!frmMain!txtSchOrderTo) Is Null Or (Forms!frmMain!txtSchOrderTo)>=[Order_Date]) And
    ((Forms!frmMain!cboSchCustname) Is Null Or (Forms!frmMain!cboSchCustname)=[Cust_Name]) And
    ((Forms!frmMain!cboSchDistCent) Is Null Or (Forms!frmMain!cboSchDistCent)=[Cust_DistCenter]) And
    ((Forms!frmMain!cboSchStoreName) Is Null Or (Forms!frmMain!cboSchStoreName)=[Cust_Store]) And
    ((Forms!frmMain!txtSchPurchOrd) Is Null Or (Forms!frmMain!txtSchPurchOrd)=[Cust_PO]) And
    ((Forms!frmMain!txtSchDate2HSFrom) Is Null Or (Forms!frmMain!txtSchDate2HSFrom)<=[Date2HS]) And
    ((Forms!frmMain!txtSchDate2HSTo) Is Null Or (Forms!frmMain!txtSchDate2HSTo)>=[Date2HS]) And
    ((Forms!frmMain!txtSchShipDateFrom) Is Null Or (Forms!frmMain!txtSchShipDateFrom)<=[ShipDate]) And
    ((Forms!frmMain!txtSchShipDateTo) Is Null Or (Forms!frmMain!txtSchShipDateTo)>=[ShipDate]) And
    ((Forms!frmMain!txtSchDateRecFrom) Is Null Or (Forms!frmMain!txtSchDateRecFrom)<=[DateRec]) And
    ((Forms!frmMain!txtSchDateRecTo) Is Null Or (Forms!frmMain!txtSchDateRecTo)>=[DateRec]) And
    (Forms!frmMain!txtDateRecNull Is Null Or [DateRec] Is Null))
    ORDER BY qryOpenOrders.Order_ID;

    This SQL statement was altered to:

    ALTER FUNCTION dbo.qryOrderSch
    (@Forms_FrmMain_txtSchOrderID varchar (255),
    @Forms_frmMain_txtSchOrderFrom varchar (255),
    @Forms_frmMain_txtSchOrderTo varchar (255),
    @Forms_frmMain_cboSchCustname varchar (255),
    @Forms_frmMain_cboSchDistCent varchar (255),
    @Forms_frmMain_cboSchStoreName varchar (255),
    @Forms_frmMain_txtSchPurchOrd varchar (255),
    @Forms_frmMain_txtSchDate2HSFro varchar (255),
    @Forms_frmMain_txtSchDate2HSTo varchar (255),
    @Forms_frmMain_txtSchShipDateFr varchar (255),
    @Forms_frmMain_txtSchShipDateTo varchar (255),
    @Forms_frmMain_txtSchDateRecFro varchar (255),
    @Forms_frmMain_txtSchDateRecTo varchar (255),
    @Forms_frmMain_txtDateRecNull varchar (255))
    RETURNS TABLE
    AS
    RETURN ( SELECT TOP 100 PERCENT Order_ID, Order_Date, Cust_Name, Cust_DistCenter, Cust_Store, Cust_PO, Date2HS, ShipDate, DateRec
    FROM dbo.qryOpenOrders
    WHERE (@Forms_FrmMain_txtSchOrderID IS NULL OR
    @Forms_FrmMain_txtSchOrderID = Order_ID) AND (@Forms_frmMain_txtSchOrderFrom IS NULL OR
    @Forms_frmMain_txtSchOrderFrom <= Order_Date) AND (@Forms_frmMain_txtSchOrderTo IS NULL OR
    @Forms_frmMain_txtSchOrderTo >= Order_Date) AND (@Forms_frmMain_cboSchCustname IS NULL OR
    @Forms_frmMain_cboSchCustname = Cust_Name) AND (@Forms_frmMain_cboSchDistCent IS NULL OR
    @Forms_frmMain_cboSchDistCent = Cust_DistCenter) AND (@Forms_frmMain_cboSchStoreName IS NULL OR
    @Forms_frmMain_cboSchStoreName = Cust_Store) AND (@Forms_frmMain_txtSchPurchOrd IS NULL OR
    @Forms_frmMain_txtSchPurchOrd = Cust_PO) AND (@Forms_frmMain_txtSchDate2HSFro IS NULL OR
    @Forms_frmMain_txtSchDate2HSFro <= Date2HS) AND (@Forms_frmMain_txtSchDate2HSTo IS NULL OR
    @Forms_frmMain_txtSchDate2HSTo >= Date2HS) AND (@Forms_frmMain_txtSchShipDateFr IS NULL OR
    @Forms_frmMain_txtSchShipDateFr <= ShipDate) AND (@Forms_frmMain_txtSchShipDateTo IS NULL OR
    @Forms_frmMain_txtSchShipDateTo >= ShipDate) AND (@Forms_frmMain_txtSchDateRecFro IS NULL OR
    @Forms_frmMain_txtSchDateRecFro <= DateRec) AND (@Forms_frmMain_txtSchDateRecTo IS NULL OR
    @Forms_frmMain_txtSchDateRecTo >= DateRec) AND (@Forms_frmMain_txtDateRecNull IS NULL) OR
    (@Forms_FrmMain_txtSchOrderID IS NULL OR
    @Forms_FrmMain_txtSchOrderID = Order_ID) AND (@Forms_frmMain_txtSchOrderFrom IS NULL OR
    @Forms_frmMain_txtSchOrderFrom <= Order_Date) AND (@Forms_frmMain_txtSchOrderTo IS NULL OR
    @Forms_frmMain_txtSchOrderTo >= Order_Date) AND (@Forms_frmMain_cboSchCustname IS NULL OR
    @Forms_frmMain_cboSchCustname = Cust_Name) AND (@Forms_frmMain_cboSchDistCent IS NULL OR
    @Forms_frmMain_cboSchDistCent = Cust_DistCenter) AND (@Forms_frmMain_cboSchStoreName IS NULL OR
    @Forms_frmMain_cboSchStoreName = Cust_Store) AND (@Forms_frmMain_txtSchPurchOrd IS NULL OR
    @Forms_frmMain_txtSchPurchOrd = Cust_PO) AND (@Forms_frmMain_txtSchDate2HSFro IS NULL OR
    @Forms_frmMain_txtSchDate2HSFro <= Date2HS) AND (@Forms_frmMain_txtSchDate2HSTo IS NULL OR
    @Forms_frmMain_txtSchDate2HSTo >= Date2HS) AND (@Forms_frmMain_txtSchShipDateFr IS NULL OR
    @Forms_frmMain_txtSchShipDateFr <= ShipDate) AND (@Forms_frmMain_txtSchShipDateTo IS NULL OR
    @Forms_frmMain_txtSchShipDateTo >= ShipDate) AND (@Forms_frmMain_txtSchDateRecFro IS NULL OR
    @Forms_frmMain_txtSchDateRecFro <= DateRec) AND (@Forms_frmMain_txtSchDateRecTo IS NULL OR
    @Forms_frmMain_txtSchDateRecTo >= DateRec) AND (DateRec IS NULL)
    ORDER BY Order_ID )


    Apparently my references to text boxes and combo boxes on my forms have been changed to procedures? How do i define these procedures so that this query will work in the same way it did in Access? Or is this even possible or is there another technique I should use?

    Thanks in advance for any assistance.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Post Upgrade to SQL Server (Access 2k2)

    The simple answer is, you can't. SQL Server knows absolutely nothing about Access objects or functions, so you can't use them as criteria in views and stored procedures in SQL Server.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Post Upgrade to SQL Server (Access 2k2)

    But would it still be possible to create VBA code that would utilize the information in text boxes and combo boxes to filter a view. In theory, I want to know if I can pull the results of the view into access and then filter this view based on controls on my form to get the specific information to show up in a list box?

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Post Upgrade to SQL Server (Access 2k2)


  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Post Upgrade to SQL Server (Access 2k2)

    I presume you are still using an MDB front-end, and not an ADP (that implies that you have an ODBC connection to the SQL Server tables). If that is the case, yes you can apply filters to a view. However, we don't usually upsize queries, as the upsizing wizard doesn't do a terribly good job in that area. As long as you are using ODBC connections, you can continue to use the Access queries and all should be fine. We may move certain functions to a view or to a stored procedure to gain performance benefits, but we do it pretty selectively.
    Wendell

  6. #6
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Post Upgrade to SQL Server (Access 2k2)

    Thanks for the info, Wendell and Hans. I was using an ADP, but will investigate and see what I can come up with.

  7. #7
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Post Upgrade to SQL Server (Access 2k2)

    I think my main problem in utilizing the front end .mdb and connecting through an ODBC is that I have had the hardest time figuring out how to do this. I have split a database front end and back end. Installed MSDE 2000 and upsized the back end. However, when I try to utilize the original .mdb file (with the tables removed) and use File|Get External Data|Link Tables I select ODBC databases as file type but I don't know where to go from here.

    I think I am way off, but don't know how to correct. I spent all evening working to figure this out.

Posting Permissions

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