locked
Microsoft Access Database to SQL server and access via End User Website RRS feed

  • Question

  • Hi,

    After months of practising ETL and SQL query I successfully completed extracting API data  into Access Database. 

    Just so,
    Here is what I have: 
    I wrote a SQL based programmed DB that I update periodically from my 'data mining research' results
    DB structure: made up of relational tables, queries, forms, macros, modules. I converted it into an executable local MS Access file. I also created a 'Access web app' that has a SQL server connection but it is doesnt meet my requirements described below. 

     High Level Requirements:
    1. I want to build a website to host my relational database for target customers
    2. It must let users search data by entering criteria in form field and run a query. e.g. form containing blank fields, drop downs, list, etc and click SEARCH button
    3. It must let users download the query results as Excel Report and PDF without restriction on number of records.
    4. Faster response time
    5. Can handle many users at a time
     Please suggest as what would be the most efficient way to achieve this. I have an open mind to various possible effective solutions. I already exported the DB to SQL server via AZURE but FORMS and MACROS wont migrate typically. I created website but the form fields wont get referenced/feed into the SQL command. Any guidance would move the mountain thats growing on my head
     

    Wednesday, December 14, 2016 6:01 PM

All replies

  • It sounds like you have exported the table and the data successfully to SQL Azure. So far so good. Now you need to create a web site to duplicate the forms and macros you had before.

    It sounds like the type of searches you are trying to do would best be handled by dynamic sql.  This should help you with this:

    http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1

    However you will need to build your web forms in a different tool.

    This should help you with this:

    https://www.asp.net/web-forms/overview/getting-started/getting-started-with-aspnet-45-web-forms/introduction-and-overview

    Wednesday, December 14, 2016 6:42 PM
  • Thanks Hilary, I feel a bit relieved that someone acknowledged my endeavor. Please tell me what  is the gap in my knowledge area.  eg. I have a ACCESS SQL command where many Tables are joined and criteria is set. Just look at the bold font and suggest how can the forms be referenced in it when they become webforms. I tried but SQL command wont identify the exisiting webform but that's my lack of knowledge at this point.

    SELECT DISTINCT Comp.PRODUCT_CODE, Comp.COMPANY_NAME, PRODUCT.BRAND_NAME, Ingred.INGREDIENT, PRODUCT.PRODUCT_IDENTIFICATION_NUMBER, Status.Status
    FROM (([Comp] INNER JOIN PRODUCT ON Comp.PRODUCT_CODE = PRODUCT.PRODUCT_CODE) INNER JOIN Ingred ON Comp.PRODUCT_CODE = Ingred.PRODUCT_CODE) INNER JOIN Status ON Comp.PRODUCT_CODE = Status.Field1
    WHERE
    (((Comp.COMPANY_NAME) Like forms![PRODUCT Search]![PRODUCT COMPANY] Or forms![PRODUCT Search]![PRODUCT COMPANY] Is Null) And

    ((PRODUCT.BRAND_NAME) Like forms![PRODUCT Search]![BRAND] Or forms![PRODUCT Search]![BRAND] Is Null) And

    ((Ingred.INGREDIENT) Like forms![PRODUCT Search]![INGREDIENT] Or forms![PRODUCT Search]![INGREDIENT] Is Null) And

    ((PRODUCT.PRODUCT_IDENTIFICATION_NUMBER) Like forms![DPD Search]![PIN] Or forms![DPD Search]![PIN] Is Null));

    I also had a hard time configuring 'Excel download button' as macro since the above SQL command became a STORED PROCEDURE and it wont get referenced to the button and forms are not identified either.

    Wednesday, December 14, 2016 7:45 PM
  • Each one of those forms![PRODUCT Search]![PRODUCT COMPANY] corresponds to a text box field. You will need to do through the access code and figure out what those apply to.
    Wednesday, December 14, 2016 7:52 PM
  • Yes, however after migrating the DB to SQL Server, the "forms!...." criteria holds no value and it doesnt accept change when I edit it to 'webform field name or url path' as per my try through VisualStudio editing.  There seems to be some gap which  I cant seem to figure. 
    Wednesday, December 14, 2016 9:07 PM
  • you will need to parameterize them like this:

    SELECT DISTINCT Comp.PRODUCT_CODE, Comp.COMPANY_NAME, PRODUCT.BRAND_NAME, Ingred.INGREDIENT, PRODUCT.PRODUCT_IDENTIFICATION_NUMBER, Status.Status
    FROM (([Comp] INNER JOIN PRODUCT ON Comp.PRODUCT_CODE = PRODUCT.PRODUCT_CODE) INNER JOIN Ingred ON Comp.PRODUCT_CODE = Ingred.PRODUCT_CODE) INNER JOIN Status ON Comp.PRODUCT_CODE = Status.Field1
    WHERE
    (((Comp.COMPANY_NAME) Like @companyName Or @companyName Is Null) And

    ((PRODUCT.BRAND_NAME) Like @productSearchBrand   Or @productSearchBrand  Is Null) And

    ((Ingred.INGREDIENT) Like @INGREDIENT] Or @INGREDIENT  Is Null) And

    ((PRODUCT.PRODUCT_IDENTIFICATION_NUMBER) Like @PIN  Or @PIN Is Null));

    now you would need to create your web page to do this:

    http://csharp-station.com/Tutorial/AdoDotNet/Lesson06

    • Proposed as answer by Teige Gao Wednesday, December 21, 2016 9:06 AM
    Wednesday, December 14, 2016 9:15 PM
  • Thanks Hilary for the reply and the link. Very knowledgeable. I will keep my development posted here.
    Wednesday, December 14, 2016 11:57 PM
  • Hi asif_patel,

    Could you please tell us if you have done this experiment? Can you share your result?

    If you have any other questions, please let me know.

    Best Regards,
    Teige

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 21, 2016 7:32 AM