[AccessD] The direction of data processing

Hi David

You may be right. I'll check that out when possible.
However, I'm not sure they will let me run any SQL command. My pass-through
queries have until now been rather simple select or update queries.

/gustav


-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af David McAfee
Sendt: 28. januar 2014 01:11
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] The direction of data processing

Gustav, they wont let you write sprocs but leave it open for you to write
any SQL command from the front end?
That sounds pretty scary to me.

Executing sprocs as PTQs are pretty easy as all you have to do is change the
QueryDef:

If they will let you, create a test sproc such as:

CREATE PROCEDURE stpSomeNameHere (@AsOfDate AS DATETIME) AS SELECT * FROM
vwSomeView WHERE SomeDate >= @AsOfDate


Modify the querydef as needed for the input parameter in Access:

  Dim db As DAO.Database
  Dim qd As DAO.QueryDef
  Dim sSQL  As String

   sSQL = "EXEC dbo.stpSomeNameHere '" & Me.txtAsOfDate & "'"
   Set db = CurrentDb
   db.QueryDefs("SomeQueryName").SQL = sSQL

   Set db = Nothing





> ----- Original Message -----
> From: "Gustav Brock" <gustav at cactus.dk>
> To: accessd at databaseadvisors.com
> Sent: Monday, January 27, 2014 10:50:25 AM
> Subject: Re: [AccessD] The direction of data processing
>
> Hi Jim
>
> It is an evolved SQL. Plain awful to write and read.
> It's not my server so writing stored procedures is beyond my control.
>
> /gustav
>
> >>> accessd at shaw.ca 27-01-14 18:48 >>>
> I feel your pain.
>
> T-SQL is sort of like Access SQL or is it only a little like?... Have 
> you considered using SPs and just passing parameters?
>
> Very complex pass-through queries use to crash Access but maybe that 
> bug has been fixed.
>
> Jim
>
> ----- Original Message -----
> From: "Gustav Brock" <gustav at cactus.dk>
> To: "Access Developers discussion and problem solving" < 
> accessd at databaseadvisors.com>
> Sent: Monday, January 27, 2014 12:13:20 AM
> Subject: Re: [AccessD] The direction of data processing
>
> Hi Jim
>
> For the last days I have been struggling with some updating 
> pass-through queries, not Oracle but T-SQL.
> No fun. As soon as you have more than a few joins, the code turns 
> nearly unreadable. I'm not very good at it, so I had to build the 
> query and the joins bit by bit to not lose my feet. I never learn to 
> love this. Give me C# please.
>
> What a moment to bring a potent Oracle server on its nees. That must 
> have been a cup of coffee you never forget.
>
> /gustav