Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Display Modes
  #1 (permalink)  
Old September 8th, 2003, 11:20 PM
Registered User
Join Date: Jun 2003
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to use VBA to Read/Write a Access Query

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.
Best Regards,
Gino Liu at CI.
Reply With Quote
  #2 (permalink)  
Old September 8th, 2003, 11:31 PM
Authorized User
Join Date: Jun 2003
Location: Glendale, AZ, USA.
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts

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"
    Set qdefTemp = Nothing

Reply With Quote
  #3 (permalink)  
Old January 7th, 2012, 03:03 AM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Jan 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default this works

first create a SP in your SQL database using

USE [yentel]
/****** Object: StoredProcedure [dbo].[__ListQuery] Script Date: 01/07/2012 08:37:11 ******/
/****** Author: Frans Eilering ******/
create PROCEDURE [dbo].[__ListQuery]
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'

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
End With
With rs1
While Not .EOF
'loop through all queries
SPName = !SPName
contents = !SPDefinition
'do what ever you want with the name and the contents
End With
End Sub

Last edited by frans eilering; January 7th, 2012 at 03:13 AM.
Reply With Quote

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Passing Query from Access to MySAL using VBA stephej Access 3 June 20th, 2006 06:56 AM
How to read/write to Access database from Visual C mmakrzem Access 1 July 30th, 2005 01:57 AM
Access VBA Delete Query Issue... snoopy92211 Access VBA 4 June 24th, 2004 03:57 PM
How to read/write Access Database jmmendes Access VBA 0 November 18th, 2003 12:26 PM
Logging File Access Read & Write ntbluez VBScript 1 November 12th, 2003 11:43 PM

All times are GMT -4. The time now is 02:26 PM.

Powered by vBulletin®
Copyright ©2000 - 2015, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.