Hi, everybody:
I wish to use VBA to read/write a Exist Query's sql text in Access. Do anyone can give me some help and point out how to do this.

Thanks for your help.

For example:
1. First, I create a Query in Access, and save it as "qryCustom"
2, Now, I wish to read the Query "qryCustom" SQL Text in VBA code, then I can modify the SQL Text for other purpose.
3, If need, I wish I can update the original Query with new SQL Text.

Pls help! Thanks.

Best Regards,
Gino Liu at CI.

Try this code....Be sure to set a reference to DAO.

    Dim qdefTemp As DAO.QueryDef

    'Change the querydef
    Set qdefTemp = CurrentDb.QueryDefs("qselCustom1")
    qdefTemp.SQL = "SELECT * FROM newTable - Put your SQL here"
    qdefTemp.Close
    Set qdefTemp = Nothing


HTH,
Mike

first create a SP in your SQL database using


USE [yentel]
GO
/****** Object: StoredProcedure [dbo].[__ListQuery] Script Date: 01/07/2012 08:37:11 ******/
/****** Author: Frans Eilering ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create PROCEDURE [dbo].[__ListQuery]
AS
BEGIN
SET NOCOUNT ON;
select obj.Name as SPName,
modu.definition as SPDefinition,
obj.create_date as SPCreationDate
FROM sys.sql_modules modu
INNER JOIN sys.objects obj
ON modu.object_id = obj.object_id
--you may also insert a where clause to select only SP beginning with a letter
--WHERE obj.type = 'P' and left(obj.Name,1)='B'
End




Sub test()

'then execute this vba piece of code

Dim cn As ADODB.Connection
Set cn = CurrentProject.AccessConnection

Set rs1 = New ADODB.Recordset
With rs1
Set .ActiveConnection = cn
.Source = "__ListQuery"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With
rs1.MoveFirst
With rs1
While Not .EOF
'loop through all queries
SPName = !SPName
contents = !SPDefinition
'do what ever you want with the name and the contents
rs1.MoveNext
Wend
End With
End Sub