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]

On 4 Jan 2006 05:53:29 -0800, "DeepHalo" <de******@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",,,,,sql 'I hope that was enough commas

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

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

-Tom.

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

[DeepHalo]

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("MyQuery")
cmd.Connection = myDataProvider.ERConnection
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add(myDataProvider.ERParameter("TAB LENAME"))
cmd.Parameters(0).Value = tableName

da = myDataProvider.ERDataAdapter
da.SelectCommand = 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]

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" <de******@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

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("MyQuery")
cmd.Connection = myDataProvider.ERConnection
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add(myDataProvider.ERParameter("TAB LENAME"))
cmd.Parameters(0).Value = tableName

da = myDataProvider.ERDataAdapter
da.SelectCommand = 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

[DeepHalo]

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

Deep Halo

[Stevel]

Terry Kreft wrote:

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.

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]

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" <[e-mail]> wrote in message
news:11***************@seven.kulnet.kuleuven.ac.be ...

Terry Kreft wrote:

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.

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

[DeepHalo]

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]

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]

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("MyQuery")
cmd.Connection = myDataProvider.ERConnection
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add(myDataProvider.ERParameter("TAB LENAME"))
cmd.Parameters(0).Value = tableName

da = myDataProvider.ERDataAdapter
da.SelectCommand = 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.