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
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
Reply
 
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
Default

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
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]
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

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


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 03:03 AM.


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