DaniWeb Forum Index > Software Development > Visual Basic 4 / 5 / 6

Visual Basic 4 / 5 / 6 RSS

create a query in access from vb6

•

Join Date: Oct 2005

Posts: 3

Reputation: mstangeh is an unknown quantity at this point

Solved Threads: 1

mstangeh

Offline

Newbie Poster

create a query in access from vb6

Oct 19th, 2005

hi,
I need to create or replace a permanent query in access (like a view in oracle, postgres...), but from vb6 using ADODB.
i know that i can create and alter tables using a ADODB connection object with something like this

dim obj as new adodb.connection
...
...
obj.execute "create table ......:"

but, how to create the views?????

thanks
mstangeh

•

Join Date: Nov 2005

Posts: 134

Reputation: Yomet is an unknown quantity at this point

Solved Threads: 10

Yomet

Offline

Junior Poster

Re: create a query in access from vb6

Nov 6th, 2005

Hi ,

Try this out, it worked well when I threw it together. But REMEMBER to read the note in the header... You have been warned...

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
Public Sub CreateQuery()
'NOTE: this code NEEDS to have a reference to DAO
'      If you already have a reference to ADO be sure
'      to prefix your variable declarations correctly
'      since there are some objects that are the same
'      and you will never know which will be opened
'      unless you prefix them, i.e.
'      Dim ADORecs As ADODB.Recordset
'      Dim DAOResc As DAO.Recordset
'
    Dim db As DAO.Database
    Dim MyQry As DAO.QueryDef
 
    Set db = DBEngine.OpenDatabase("<path to your database")
    Set MyQry = db.CreateQueryDef("<Query name>", "<SQL statement>")
    db.Close
End Sub
Public Sub CreateQuery()
'NOTE: this code NEEDS to have a reference to DAO
'      If you already have a reference to ADO be sure
'      to prefix your variable declarations correctly
'      since there are some objects that are the same
'      and you will never know which will be opened
'      unless you prefix them, i.e.
'      Dim ADORecs As ADODB.Recordset
'      Dim DAOResc As DAO.Recordset
'
    Dim db As DAO.Database
    Dim MyQry As DAO.QueryDef
    
    Set db = DBEngine.OpenDatabase("<path to your database")
    Set MyQry = db.CreateQueryDef("<Query name>", "<SQL statement>")
    db.Close
End Sub