Create Knowledge Bank containing frequently used sql scripts 1

[MwTV]

This might not be possible but I will post my request;

I am interested in storing frequently used sql scripts in a Microsoft Access table and having a form available for the users in the department to query on certain "key" words (using wild cards, etc.) and then the sql script would display at the bottom of the form along with comments on the query results that would display if the query was processed.



This will assist several novice users who are just not "grasping" some of the complex sql scripts that involve left joins, subqueries, aliases, etc.

Has this been done before?

 

[dhookom]

If your objective is to allow users to create, save, modify, and run queries in a fairly friendly environment, you might want to check the query by form applet at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

You as the developer create a few "master" queries that combine tables and rename fields as necessary to make your structure easier to understand. Users select fields, set criteria, sorting, etc. The are a couple clicks from displaying and sending the results to Print, Word, CSV, Excel, HTML, Graph, merge,...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MwTV]

Thanks.

I will incorporate the Query By Form technique as well.

Really was trying to present a form to the end-user that would display portions of sql code in a image control or something similar.

Specifically, the concept would go as follows;

The end-user types in a word or phrase (with wildcard capability) in a text box that is on a form. Then several sql queries are presented in a list that "matches" what the end-user types. The end-user can highlight one of the queries in the list to display sections of the sql script so he/she will learn how to create sql scripts.

Note, no more than 15 basic sql scripts will be contained in the database.

So far, I am exploring the use of image controls on the form and also have typed in portions of sql scripts for 5 queries in a table.

Is this far fetched or even feasible?

Just trying to originate a creative method to promote learning among the employees.

 

[dhookom]

You could display the SQL generated from the QBF in the results screen. Add a text box with a control source like:

=currentdb.querydefs("myquery").[SQL]

You could add a description field to zstblQBFSource and allow users to enter something and search for something.

I don't know why you would want to use an image control to display text.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MwTV]

Currently implementing.

Will this QBF work with passthrough queries?

 

[dhookom]

Pass-Through queries will generally have different syntax. For instance while Access uses "#" as a date delimiter, SQL Server uses "'". Also, the wild cards are generally different. I have made some changes to the code to allow the QBF create P-T rather than standard queries.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MwTV]

dhookom,

Were the changes made to the code recent?

If so, I will download the QBF again.

Thanks for the insight.

 

[dhookom]

I only changed the code for an implementation at work. It's not available to the outside community.

If you have some specific questions about what needs to be modified and where, just ask.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MwTV]

dhookom,

What modifications do I need to make to the QBF to allow for passthrough queries?

Thanks in advance.

 

[dhookom]

The first task would be to create a basic P-T that you can change the SQL property much like you do with "MyQuery" in the current applet. Then you need to identify how your database server syntax will vary from Access/JET sql syntax. After you have done this, modify the code to implement the new sql syntax.

One of the main modifications would be to this function in the sfrmQBFFields. strDel will vary based on your server.

Private Function OpVisible(pControl As Control)
   On Error GoTo OpVisible_Err
   Dim strErrMsg As String 'For Error Handling

    If Len(pControl & "") > 0 Then
        Dim db As DAO.Database
        Dim strDel As String
        Dim strField As String
        Set db = CurrentDb
        Dim strFld As String
        strField = pControl
        Select Case db.QueryDefs(Forms![frmQBFSource]![cboSource]).Fields(strField).Type
            Case dbText
                strDel = "'"
            Case dbDate
                strDel = "#"
            Case Else
                strDel = " "
        End Select
        Me("txtDelimiter") = strDel
    End If
    

OpVisible_Exit:
   Exit Function

OpVisible_Err:
   Select Case Err
      Case Else
         strErrMsg = strErrMsg & "Error #: " & Format$(Err.Number) & vbCrLf
         strErrMsg = strErrMsg & "Error Description: " & Err.Description
         MsgBox strErrMsg, vbInformation, "OpVisible"
         Resume OpVisible_Exit
   End Select
End Function

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MwTV]

Thanks.

I plan to have two QBFs within my application - one for standard queries and one for P-T queries.

Currently implementing and will post back with any additional questions and/or comments.