Dynamic Query in Ms-Access

[Arun]

Hi, I'm trying to create a dynamic query in MS-Access. I have a table
with 3 fields and my dynamic query will be constructed with those 3
fields. Since I haven't done this before, I searched for help in this
forum and tried to use some, but still I'm getting errors. Can anyone
help me with this?

Find below the code which I'm using.

Sub Defaults()

Dim sql As String
sql = "select count(*) from " & Default_value.Table_name & " where" &
Default_value.Field_Name & "=" & Default_value.Default
CurrentDb.Querydefs("SomeQuery").sql = sql

End Sub

Thanks
Arun

[Rich P]

Hi Arun,

Here is something you can try that is a little bit simple - for
starters. Then you can use the values contained in textboxes to get
table/field names dynamically.

----------------------------------------------

Private Sub Command3_Click()
Dim qd As DAO.QueryDef, db As DAO.Database

Set db = CurrentDb
For Each qd In db.QueryDefs
If qd.Name = "test1" Then
db.QueryDefs.Delete qd.Name
Exit For
End If
Next
Set qd = db.CreateQueryDef("test1")
qd.SQL = "select fname, lname, num from tbl1"
DoCmd.OpenQuery "test1"
End Sub

--------------------------------------------
this example is using a tbl called "tbl1" which contains the fields
fname (text), lName (text) num (number). To implement dynamically - you
can do this
------------------------------------------------

Private Sub Command3_Click()
Dim qd As DAO.QueryDef, db As DAO.Database

Set db = CurrentDb
For Each qd In db.QueryDefs
If qd.Name = "test1" Then
db.QueryDefs.Delete qd.Name
Exit For
End If
Next
Set qd = db.CreateQueryDef("test1")
qd.SQL = "select " & txtFname & ", " & txtLname & ", " & txtNum & " from
" & txtTblName
DoCmd.OpenQuery "test1"
End Sub

----------------------------------------------------

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Arun]

Thanks Rich, It helped a lot.
Rich P wrote:

Hi Arun,

Here is something you can try that is a little bit simple - for
starters. Then you can use the values contained in textboxes to get
table/field names dynamically.

----------------------------------------------

Private Sub Command3_Click()
Dim qd As DAO.QueryDef, db As DAO.Database

Set db = CurrentDb
For Each qd In db.QueryDefs
If qd.Name = "test1" Then
db.QueryDefs.Delete qd.Name
Exit For
End If
Next
Set qd = db.CreateQueryDef("test1")
qd.SQL = "select fname, lname, num from tbl1"
DoCmd.OpenQuery "test1"
End Sub

--------------------------------------------
this example is using a tbl called "tbl1" which contains the fields
fname (text), lName (text) num (number). To implement dynamically - you
can do this
------------------------------------------------

Private Sub Command3_Click()
Dim qd As DAO.QueryDef, db As DAO.Database

Set db = CurrentDb
For Each qd In db.QueryDefs
If qd.Name = "test1" Then
db.QueryDefs.Delete qd.Name
Exit For
End If
Next
Set qd = db.CreateQueryDef("test1")
qd.SQL = "select " & txtFname & ", " & txtLname & ", " & txtNum & " from
" & txtTblName
DoCmd.OpenQuery "test1"
End Sub

----------------------------------------------------

Rich

*** Sent via Developersdex http://www.developersdex.com ***