Create a Query programmatically

[Andydr]

I want to create a Query using an SQL staement that can be run with VB.
I am use to Oracle SQL where can you can write a SQL statement such as Create or Replace View as ... SELECT Statement.
I Cannot find the equivalent within Access.
Is this possible please?
Thanks

 

[Oppenhiemer]

The specifics of the solutions will depend on which version of MS Access you are using. But you can create a temporary Querydef using VBA. Here is a brief example (for Office 97)
'============================================'===========================================

Dim Sql
Dim db As Database
Dim rst As Recordset
Dim qd As QueryDef

Set db = CurrentDb()
Sql = "SELECT Something from SomeTable"
Set qd = db.CreateQueryDef("", Sql)
Set rst = qd.OpenRecordset
if not rst.eof then
' Do something here
rst.MoveFirst
While not rst.eof do
' Iterate through results ?
Wend
End If

qd.Close
rst.Close
db.Close
Set qd = Nothing
Set rst = Nothing
Set db = Nothing
'============================================
Hope this helps..

Opp.

 

[SQLBI]

Andydr,

You have a couple of choices here:

You could create your query in access, then in your VBA use

DoCmd.OpenQuery "qryYourQuery"

Or you could do it all with VBA using ADO (ActiveX Data Objects).

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim rstArray As Variant

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset

strSQL =

"Your SQL statement goes here."

rst.Open strSQL, cnn, AdOpenKeyset

You can then either test to make sure the recordset contains any records using;

rst.RecordCount

or populate your array with the recordset;

rstArray = rst.GetRows.

Hope this helps you out in some way.



Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[Andydr]

Thanks for that. Am just trying CreateQueryDef to see if that will do what I want.
Again many thanks