Creating dynamic query but NOT in code.

  • Thread starter Thread starter DeepHalo
  • Start date Start date
D

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.

-----

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("TABLENAME"))
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?
 
I think there is a big difference here: T-SQL is an SQL Language as well as
a programming language while JET SQL is an SQL language only. For
programming in Access, we need to use VBA.

In Access, source Table names cannot be Parameters. To do this in Access,
we use VBA to construct the SELECT SQL with the Tables name(s) resolve and
then use the SQL String to open the Recordset.
 
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.
Click to expand...

Unfortunately, you cannot. Period.

Access and SQL/Server are two different development environments. They
do not work identically. This is one of the differences.

John W. Vinson[MVP]
 
No, but the following code would work anyway....

Dim strSql As String
Dim rstRec As ADODB.Recordset
Dim strTableName As String

strTableName = "you table name goes here"

strSql = "select * from " & strTableName

Set rstRec = CurrentProject.Connection.Execute(strSql)


The above is really only 2 lines of code, and if you wish, you could wrap
the whole thing in a function to return a reocdset if you please...

Remember, in place of t-sql, we have a MUCH nicer language called VB..and we
can usually do things with less effort and work...
 
I think I will create a dll to do this work but I have to design it
first :)

Thanx for all of your replies.

Deep Halo
 
Back
Top