Creating dynamic sql

[DeepHalo]

I want to do simply this in MS Access.


PARAMETERS pTableName;

SELECT * FROM pTableName;


This doesn't work ( if it did I wouldn't ask you :) ) but is it clear
what I want to do? Just with passing table names or columns as
parameters I want to change my query but not using any programming
language as Java, VB, C# ... Just like Stored Procedure in SQL Server I
want to create this query and call it from my proc.

Your urgent helps needed. Thanks.

DeepHalo

[Tom van Stiphout]

Re: Creating dynamic sql

On 4 Jan 2006 05:53:29 -0800, "DeepHalo" <deephalo@gmail .com> wrote:

As you found out, Access does not support this. Alternatives include:
* Use a dynamic sql statement. Something like:
dim sql as string
sql = "select * from SomeTable"
DoCmd.OpenForm "frmTest",,,,,s ql 'I hope that was enough commas

and in frmTest:
Private Sub Form_Open(Cance l as Boolean)
Me.RecordSource = OpenArgs
End Sub

* Set the SQL property of an existing Querydef
dim sql as string
sql = "select * from SomeTable"
Currentdb.Query defs("SomeQuery ").SQL = sql

-Tom.

[color=blue]
>I want to do simply this in MS Access.
>
>
>PARAMETERS pTableName;
>
>SELECT * FROM pTableName;
>
>
>This doesn't work ( if it did I wouldn't ask you :) ) but is it clear
>what I want to do? Just with passing table names or columns as
>parameters I want to change my query but not using any programming
>language as Java, VB, C# ... Just like Stored Procedure in SQL Server I
>want to create this query and call it from my proc.
>
>Your urgent helps needed. Thanks.
>
>DeepHalo[/color]

[DeepHalo]

Re: Creating dynamic sql

Here is my SQL Server SP:

CREATE PROCEDURE MyQuery @TABLENAME VARCHAR(10) AS EXEC('SELECT * FROM
' + @TABLENAME + '')

and the function I use with it:

Private Function RunQuery(ByVal tableName As String) As DataTable
Dim da As IDbDataAdapter
Dim cmd As IDbCommand
Dim ds As New DataSet
Dim dt As DataTable

Try
cmd = myDataProvider. ERCommand("MyQu ery")
cmd.Connection = myDataProvider. ERConnection
cmd.CommandType = CommandType.Sto redProcedure

cmd.Parameters. Add(myDataProvi der.ERParameter ("TABLENAME" ))
cmd.Parameters( 0).Value = tableName

da = myDataProvider. ERDataAdapter
da.SelectComman d = cmd
da.Fill(ds)
dt = ds.Tables(0)

RunQuery= dt
Catch ex As Exception
Call ErrorControl(ex , "Error:RunQuery ")
Finally
da = Nothing
cmd = Nothing
ds = Nothing
End Try
End Function

It works with SQL Server but not with access :( I don't want to put
another line of code here. I want to do all in my "defined query" in MS
Access. Is there a way?

Thanks,

Deep Halo

[Terry Kreft]

Re: Creating dynamic sql


You're not comparing like with like.

A stored procedure in SQL Server is more like a procedure (Sub or Function)
in VB or VBA. It is in no way comparable to an Access Query (which is more
like a SQl View).

You will neeed to do as Tom suggests and build the SQL dynamically, or
change the SQL property of the Access query.


--
Terry Kreft



"DeepHalo" <deephalo@gmail .com> wrote in message
news:1136385226 .576393.122310@ g43g2000cwa.goo glegroups.com.. .[color=blue]
> Here is my SQL Server SP:
>
> CREATE PROCEDURE MyQuery @TABLENAME VARCHAR(10) AS EXEC('SELECT * FROM
> ' + @TABLENAME + '')
>
> and the function I use with it:
>
> Private Function RunQuery(ByVal tableName As String) As DataTable
> Dim da As IDbDataAdapter
> Dim cmd As IDbCommand
> Dim ds As New DataSet
> Dim dt As DataTable
>
> Try
> cmd = myDataProvider. ERCommand("MyQu ery")
> cmd.Connection = myDataProvider. ERConnection
> cmd.CommandType = CommandType.Sto redProcedure
>
> cmd.Parameters. Add(myDataProvi der.ERParameter ("TABLENAME" ))
> cmd.Parameters( 0).Value = tableName
>
> da = myDataProvider. ERDataAdapter
> da.SelectComman d = cmd
> da.Fill(ds)
> dt = ds.Tables(0)
>
> RunQuery= dt
> Catch ex As Exception
> Call ErrorControl(ex , "Error:RunQuery ")
> Finally
> da = Nothing
> cmd = Nothing
> ds = Nothing
> End Try
> End Function
>
> It works with SQL Server but not with access :( I don't want to put
> another line of code here. I want to do all in my "defined query" in MS
> Access. Is there a way?
>
> Thanks,
>
> Deep Halo
>[/color]

[DeepHalo]

Re: Creating dynamic sql

Thanx for your replies, I've decided to create a dll to do this work.

Deep Halo

[Stevel]

Re: Creating dynamic sql

Terry Kreft wrote:[color=blue]
> You're not comparing like with like.
>
> A stored procedure in SQL Server is more like a procedure (Sub or Function)
> in VB or VBA. It is in no way comparable to an Access Query (which is more
> like a SQl View).
>
> You will neeed to do as Tom suggests and build the SQL dynamically, or
> change the SQL property of the Access query.
>
>[/color]
I think you need to add the @-sign with the parameter name in MS Access

PS. When i've used ADO, this was a shorter way of adding parameters. I
don't know if it works with the IDbCommand:
cmd.Parameters( "@TABLENAME ") = tableName

[Terry Kreft]

Re: Creating dynamic sql

You don't need to use the @ sign with parameters in Access.

I think you're missing what the OP is trying to do.

--

Terry Kreft


"Stevel" <stevenlangenak en-at-@hotmail-dot-.com> wrote in message
news:1136451638 .727180@seven.k ulnet.kuleuven. ac.be...[color=blue]
> Terry Kreft wrote:[color=green]
> > You're not comparing like with like.
> >
> > A stored procedure in SQL Server is more like a procedure (Sub or[/color][/color]
Function)[color=blue][color=green]
> > in VB or VBA. It is in no way comparable to an Access Query (which is[/color][/color]
more[color=blue][color=green]
> > like a SQl View).
> >
> > You will neeed to do as Tom suggests and build the SQL dynamically, or
> > change the SQL property of the Access query.
> >
> >[/color]
> I think you need to add the @-sign with the parameter name in MS Access
>
> PS. When i've used ADO, this was a shorter way of adding parameters. I
> don't know if it works with the IDbCommand:
> cmd.Parameters( "@TABLENAME ") = tableName[/color]

[DeepHalo]

Re: Creating dynamic sql

No, this is a different provider which can support various databases,
so I don't need to pass any prefixes.

As I said I'll use dll to this and started write it already it's going
well :)

So thnx for all.

Deep Halo

[Lyle Fairfield]

Re: Creating dynamic sql

1. You also said:
"but not using any programming
language as Java, VB, C# ...."
Can we assume that you are creating your DLL completely and only from
DAO/JET?

2. Your DLL; what properties and methods will it have that DAO does not
have? How will it differ from DAO?

[DeepHalo]

Re: Creating dynamic sql

I was thinking to do this without writing any language except SQL or
SQL based languages because I don't want to write different code for
different databases.

Private Function RunQuery(ByVal tableName As String) As DataTable
Dim da As IDbDataAdapter
Dim cmd As IDbCommand
Dim ds As New DataSet
Dim dt As DataTable

Try
cmd = myDataProvider. ERCommand("MyQu ery")
cmd.Connection = myDataProvider. ERConnection
cmd.CommandType = CommandType.Sto redProcedure

cmd.Parameters. Add(myDataProvi der.ERParameter ("TABLENAME" ))
cmd.Parameters( 0).Value = tableName

da = myDataProvider. ERDataAdapter
da.SelectComman d = cmd
da.Fill(ds)
dt = ds.Tables(0)

RunQuery= dt
Catch ex As Exception
Call ErrorControl(ex , "Error:RunQuery ")
Finally
da = Nothing
cmd = Nothing
ds = Nothing
End Try
End Function

This is a sample of my function. I cant call queries from different
databases for this function because I have to support MS Access as well
:( myDataProvider is our own data provider and it supports different
databases so dont be confused about code. I just cant do the same
things using defined queries in MS Access that I can do in SQL Server,
Oracle. So I've decided to create a dll using a programming language
that we use for our projects.

I'm sorry that I cant give you detailed answer about the dll but it's
going to use our DataProvider and it will create dynamic tabbed queries
for any database. I'm just trying to reduce the lines of code.