SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Altering the SQL code

    Concerning a Pass-through query in Microsoft Access.

    How can you exchange a line of SQL code with another? Say I have the following code:

    Code:
    DECLARE @startdate = '20040101 00:00:00'
    Can I find that line and replace it with for example:

    Code:
    DECLARE @startdate = '20040201 00:00:00'

  2. #2
    Original Gangster silver trophy Thing's Avatar
    Join Date
    Oct 2000
    Location
    Philadelphia, PA
    Posts
    4,708
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Sure you can. I'm guessing that is used to pull back records after a certain date (startdate) correct?

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mongoloid
    Sure you can. I'm guessing that is used to pull back records after a certain date (startdate) correct?
    Yes, let me just clarify, I'm not interested in manuly viewing the SQL code and exchange that row. I want to be able to change the code after a user has inputed a value, then run a procedure that changes the code. If that was what you had in mind, what command or procedure would perform such an action?

  4. #4
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I suggest that you retrieve the Stored Procedure code using ADOX, use a RegEx replace to make the change, and use ADOX to save the changed code.

    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  5. #5
    SitePoint Enthusiast
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MarcusJT
    I suggest that you retrieve the Stored Procedure code using ADOX, use a RegEx replace to make the change, and use ADOX to save the changed code.


    Wow, and in "laymans terms"?? I have never used either ADOX or RegEx replace. Can you show me an exampel, I'm using a Pass-through query in Microsoft Access, and in the query some variables are defined to a specific date and time. But sense the query works directly with the SQL server I can not require user inputs when the query is runned. So I'm thinking to run a "pre-query" procedure that can change the SQL code of the Pass-through query before its runned... can you do that, and was it what you had in mind?

    Forever thankful for your reply!

    Regards Pfenix

  6. #6
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Pfeni
    Wow, and in "laymans terms"?? I have never used either ADOX or RegEx replace. Can you show me an exampel, I'm using a Pass-through query in Microsoft Access, and in the query some variables are defined to a specific date and time. But sense the query works directly with the SQL server I can not require user inputs when the query is runned. So I'm thinking to run a "pre-query" procedure that can change the SQL code of the Pass-through query before its runned... can you do that, and was it what you had in mind?

    Forever thankful for your reply!

    Regards Pfenix
    Seeing this again many days later, I now realise that of course you are referring to a stored procedure (SP) with a local variable. Although it *can* be done, it makes little sense to modify the actual SP itself (for a number of reasons, including the fact that it will be recompiled each time by MSSQL, which is rather extravagant) and therefore I suggest that you modify the SP to take "@startdate" as a parameter instead, and then pass the value directly when calling the SP (e.g. EXEC sp_myquery @startdate='20040201 00:00:00').
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  7. #7
    SitePoint Enthusiast
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MarcusJT
    (e.g. EXEC sp_myquery @startdate='20040201 00:00:00').
    hmm... I don't think you would manage to read the whole code of the SQL query... but let me show you the begining...

    Code:
    IF object_id('tempdb..#Kalle') IS NOT NULL
    BEGIN
       DROP TABLE #Kalle
    END
     
    IF object_id('tempdb..#Kalle2') IS NOT NULL
    BEGIN
       DROP TABLE #Kalle2
    END
    
    BEGIN
    SET NOCOUNT ON
    DECLARE @startdate varchar(20)
    DECLARE @startdate_later varchar(20)
    DECLARE @enddate varchar(20)
    DECLARE @partialpercentage int
    DECLARE @firstdistributor int
    DECLARE @lastdistributor int
     
    -- variabel data
    SELECT @startdate = '20040101 00:00:00'
    SELECT @startdate_later = '20040101 00:00:00'
    SELECT @enddate = '20040331 00:00:00'
    SELECT @partialpercentage = 100
    SELECT @firstdistributor = 4000
    SELECT @lastdistributor = 4999
    What do I have to create for your suggestion to work? A table (on local machine in Access) where I can acquire the user inputed dates, or a dbase on the SQL server where the user input is stored? I would guess on the later... but how would I go about that, would it have to be two different procedures, first a procedure where a user can store a date on the SQL server, then when the query is runned it acquires the dates from that dbase with your suggestion??? Feel like 7 years old and first day at school... =)

  8. #8
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What I mean is that your new SP would look something like this:
    Code:
    CREATE PROCEDURE sp_YourSPNameGoesHere (@startdate varchar(20), @startdate_later varchar(20), @enddate varchar(20)) AS
    
    IF object_id('tempdb..#Kalle') IS NOT NULL
    BEGIN
       DROP TABLE #Kalle
    END
     
    IF object_id('tempdb..#Kalle2') IS NOT NULL
    BEGIN
       DROP TABLE #Kalle2
    END
    
    BEGIN
    SET NOCOUNT ON
    DECLARE @partialpercentage int
    DECLARE @firstdistributor int
    DECLARE @lastdistributor int
     
    -- variabel data
    SELECT @partialpercentage = 100
    SELECT @firstdistributor = 4000
    SELECT @lastdistributor = 4999
    You could do the same for your 3 other variables, depending on how likely it is that you might want to change the values...
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  9. #9
    SitePoint Enthusiast
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MarcusJT
    What I mean is that your new SP would look something like this:
    Code:
    CREATE PROCEDURE sp_YourSPNameGoesHere (@startdate varchar(20), @startdate_later varchar(20), @enddate varchar(20)) AS
    
    IF object_id('tempdb..#Kalle') IS NOT NULL
    BEGIN
       DROP TABLE #Kalle
    END
     
    IF object_id('tempdb..#Kalle2') IS NOT NULL
    BEGIN
       DROP TABLE #Kalle2
    END
    
    BEGIN
    SET NOCOUNT ON
    DECLARE @partialpercentage int
    DECLARE @firstdistributor int
    DECLARE @lastdistributor int
     
    -- variabel data
    SELECT @partialpercentage = 100
    SELECT @firstdistributor = 4000
    SELECT @lastdistributor = 4999
    You could do the same for your 3 other variables, depending on how likely it is that you might want to change the values...

    Ok I'm with you so far but what procedure would call the user for input values? Since the query is a Pass-through query in Access I can not use an Access command e.g.

    Code:
    @startdate = [Input stardate:]
    That's the whole problem...

  10. #10
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have so far presumed that since these forums are dedicated to web development, your problem relates to an access database which you are using as the backend for a website, which in turn (for some reason best known to yourself) links to a SQL Server, in which you can easily modify the code in the server-side scripting (presumably ASP, using ADO). Is this not the case?

    Are you talking about an actual Access database application (i.e. a custom Access database running on the desktop)?

    If so, I can't help you, I'm afraid, but I can virtually 100% guarantee that it's possible - calling a stored procedure is *such* a fundamental thing to be able to do that it *must* be possible!
    Last edited by MarcusJT; May 17, 2004 at 04:50.
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  11. #11
    SitePoint Enthusiast
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MarcusJT
    I have so far presumed that since these forums are dedicated to web development, your problem relates to an access database which you are using as the backend for a website, which in turn (for some reason best known to yourself) links to a SQL Server, in which you can easily modify the code in the server-side scripting (presumably ASP, using ADO). Is this not the case?

    Are you talking about an actual Access database application (i.e. a custom Access database running on the desktop)?

    If so, I can't help you, I'm afraid!
    This is the case:

    I'm creating an Microsoft Access dbase as an interface between my co-workers and the SQL server. Since not everyone at my workplace can program in SQL, I have to make it easy for them to ask a few definied questions themselfes. So far it has been sufficent to link tables on the SQL server to MS Access and require the answers to a question through a selection query created in MS Access. But as the queries gets more and more complicated it isn't longer possible to just link to tables on the SQL server, since the tables (the answers) is to big for a local computer. So this latest question has to create temporary tables on the SQL server (since the server has a much larger HD space) and retrieve its answers from these temporary created tables. Then present it as a table in MS Access. In a selection query in MS Access refering to linked tables it is possible to use MS Access SQL coding interface i.e
    Code:
    @startdate = [Input startdate:]
    Then the user is prompted with a dialog box, with the instruction in the brackets. Thus I can filter the records according to the whishes of a user. But this of course only applies to queries based on linked tables and is not the case for a so called Pass-through query that "talks" directly to the SQL server. Then "real" SQL-coding is required and there is no built in command to prompt a user for input values while the query is running. So there are two options as I see it, either altering the SQL code every time you want to change @startdate and @enddate or finding someway that this can be done automaticly, and it's the later option that I want an answer to, how to do, or to simplify. Since my co-workers don't want to learn how to go in and change the SQL code, which in addition is not very user friendly.

    Furthermore I thought this forum was for generall dbase inquires, irrelevantly if you are using a user interface such as MS Access??? Anyway, are you now able to see a soulution to the problem?

    Thanks, in advance!

    With high regards Pfenix

  12. #12
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As I say, I haven't done this myself, so I'm not the man to help you, but...
    Code:
    @startdate = [Input startdate:]
    Where do you specify this exactly?
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  13. #13
    SitePoint Enthusiast
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MarcusJT
    As I say, I haven't done this myself, so I'm not the man to help you, but...
    Code:
    @startdate = [Input startdate:]
    Where do you specify this exactly?
    When you create a query in MS Access, say a selection query based on a linked table (a table on a SQL server). You can either view it in design view and fill in what fields you want to show in the result, you can write conditions on what values you want to show, select sorting order etc etc or you can view it in SQL view (this means that MS Access creates the SQL code from the selctions you've made i. e. what tables you want to include, what fields you want shown, what conditions you've stated etc) Let me show you a simple selection query on a linked table.

    Code:
    SELECT OIS_cForlag.lForlagsNr AS Förlag, OIS_cArtikel.szArtikelNr AS ArtikelNr, OIS_cArtikel.szArbetsTitel AS Arbetstitel, 
    OIS_oArtOIS.lSaldo AS Kvantitet, OIS_oArtOIS.dLagerVarde AS [Lagervärde/styck], OIS_oArtOIS!lSaldo*OIS_oArtOIS!dLagerVarde AS [Totalt lagervärde]
    
    FROM OIS_cForlag, OIS_oArtOIS INNER JOIN OIS_cArtikel ON OIS_oArtOIS.szArtikelNr = OIS_cArtikel.szArtikelNr
    
    WHERE (((OIS_cForlag.lForlagsNr) Between [Ange minsta förlagsnr:] And [Ange största förlagsnr:]) 
    AND ((OIS_cArtikel.szArtikelNr)=[OIS_oArtOIS]![szArtikelNr]) AND ((OIS_oArtOIS.lSaldo)>0) 
    AND ((OIS_cForlag.lIntressentNr)=[OIS_cArtikel]![lIUtgivare]))
    ORDER BY OIS_cForlag.lForlagsNr;
    This query works perfectly, don't mind the swedish language, I'm sure you get the idea =) Anyway what's interesting here is (I'll translate it for you so you follow more easy):

    Code:
    WHERE (((OIS_cDistributor.lDistributorNr) Between [State the smallest distributor nr:] And [State the largest distributor nr:])
    When the query is runned in MS Access the values "Between ... And..." is required to perform the action, so MS Access asks, whatever it says between the brackets. So two diologboxes appears when you run the query looking like this:

    State the smallest distributor nr:

    and

    State the largest distributor nr:

    This makes it able for the user to input a range of distributors he/she wants to observe. This is possible because MS Access interprets the "MS Access SQL code"... but when I'm talking directly to the SQL server I need to use the correct SQL code, not the "MS Access SQL code". So I'm not able in the same way to prompt the user for input values. Thats the whole problem. So the dates I've programmed in the SQL code (in the Pass-through query speaking directly to the SQL server) are fixed. As shown in previous code

    Code:
    SELECT @startdate = '20040101 00:00:00'
    SELECT @startdate_later = '20040101 00:00:00'
    SELECT @enddate = '20040331 00:00:00'
    I want to be able to change these without having to go in to "SQL programing mode" since the whole purpose with MS Access is to be user friendly and not require of a user to know how to program in SQL.

    I hope that clarified things!

    Thanks!!!

    Regards Pfenix

  14. #14
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm normally very quick to grasp problems, but I must admit that I'm not sure I'm 100% with you yet - perhaps something is lost in translation...

    So you're executing the same query in two different ways? Once in the Access application, by querying the linked tables using a stored query that includes prompts for the values... and once as a stored procedure on the SQL Server itself?

    I still don't see why you can't replace the query stored in Access with a call to the native SQL one, and pass across the parameters as I suggest (i.e. EXEC blah blah blah)? The parameters could even be driven by values entered/selected in an Access form...
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  15. #15
    SitePoint Enthusiast
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MarcusJT
    I'm normally very quick to grasp problems, but I must admit that I'm not sure I'm 100% with you yet - perhaps something is lost in translation...

    So you're executing the same query in two different ways? Once in the Access application, by querying the linked tables using a stored query that includes prompts for the values... and once as a stored procedure on the SQL Server itself?

    I still don't see why you can't replace the query stored in Access with a call to the native SQL one, and pass across the parameters as I suggest (i.e. EXEC blah blah blah)? The parameters could even be driven by values entered/selected in an Access form...
    No I'm not performing the same query in two different ways, I just showed you one of the other questions I've design working with linked tables (on the SQL server), instead of this new one working directly to the SQL server. But I see what you are going with this, can you give me an exampel where information (or data) is acquired from an Access form into an nativ SQL query?

    Thankful

    Regards Pfenix

  16. #16
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Pfeni
    No I'm not performing the same query in two different ways, I just showed you one of the other questions I've design working with linked tables (on the SQL server), instead of this new one working directly to the SQL server. But I see what you are going with this, can you give me an exampel where information (or data) is acquired from an Access form into an nativ SQL query?
    Countless Access applications/projects all over the world use a SQL Server backend and make use of stored procedures, so I know it's well supported, but not having had to do so myself I can't provide an example.

    I suggest that you:
    1) Search for relevant material - here's one that looks promising that I found via Google:
    http://www.winnetmag.com/SQLServer/A...8039/8039.html

    2) Find a book/manual that covers the topic - I would have thought that most serious Access development books would do so.


    Beyond that, I really don't think I can help you any more! Good luck!
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  17. #17
    SitePoint Enthusiast
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MarcusJT
    Beyond that, I really don't think I can help you any more! Good luck!
    Ok, thanks so much for your angle on the problem anyway, I think we got a push in the right direction anyway. Hope I'm able to solve the problem!

    Great Regards Pfenix


Bookmarks

Posting Permissions

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