Altering the SQL code

[Pfeni]

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:

DECLARE @startdate = '20040101 00:00:00'

Can I find that line and replace it with for example:

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

[Thing]

Sure you can. I'm guessing that is used to pull back records after a certain date (startdate) correct?

[Pfeni]

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?

[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.

[Pfeni]

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

[MarcusJT]

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').

[Pfeni]

(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...

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... =)

[MarcusJT]

What I mean is that your new SP would look something like this:

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...

[Pfeni]

What I mean is that your new SP would look something like this:

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.

@startdate = [Input stardate:]

That's the whole problem...

[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, 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!

[Pfeni]

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

@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

[MarcusJT]

As I say, I haven't done this myself, so I'm not the man to help you, but...

@startdate = [Input startdate:]

Where do you specify this exactly?

[Pfeni]

As I say, I haven't done this myself, so I'm not the man to help you, but...

@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.

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):

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

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

[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...

[Pfeni]

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

[MarcusJT]

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!

[Pfeni]

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