build a query via vba

#1 (**permalink**) 28-Jul-2006, 08:17 AM

i wonder if there is an option to enter SQL sentence in order to change
its structure and data via vba code
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/10951-build-a-query-via-vba.html

*

#2 (**permalink**) 28-Jul-2006, 08:17 AM

Yaniv,

Most likely yes, but you have to be more specific in order to get a
specific reply! What exactly do you want to do?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10951

Nikos

#3 (**permalink**) 28-Jul-2006, 08:17 AM

ok,thanks for your replay.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10951
my issue is as follow.
i want to build maketable query that will be dinamic,that means that
through vba,i will be able to change everytime(depending on my groups)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10951
the structure of the make table,so it will be dinamicly make table from
diffrent groups
for example:
one form will automatic create a table of animals
second form will automatic create a table of flowers
and ctr..

but of course all of them should be working from one query make-table

Nikos Yannacopoulos wrote:
> Yaniv,
>
> Most likely yes, but you have to be more specific in order to get a
> specific reply! What exactly do you want to do?
>
> Nikos

#4 (**permalink**) 28-Jul-2006, 08:17 AM

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

strSQL = "SELECT ...."
Set dbCurr = CurrentDb()
Set qdfCurr = dbCurr.QueryDefs("MyQuery")
qdfCurr.SQL = strSQL

--
Doug Steele, Microsoft Access MVP
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10951
http://I.Am/DougSteele
(no e-mails, please!)

"yaniv d" wrote in message
news:1149663498.822467.54570@g10g2000cwb.googlegro ups.com...
> i wonder if there is an option to enter SQL sentence in order to change
> its structure and data via vba code
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10951
>

#5 (**permalink**) 28-Jul-2006, 08:17 AM

hi thanks for your help,
i'm woring on ADO,can you give me a code for it
Douglas J Steele wrote:
> Dim dbCurr As DAO.Database
> Dim qdfCurr As DAO.QueryDef
> Dim strSQL As String
>
> strSQL = "SELECT ...."
> Set dbCurr = CurrentDb()
> Set qdfCurr = dbCurr.QueryDefs("MyQuery")
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10951
> qdfCurr.SQL = strSQL
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10951
>
>
> "yaniv d" wrote in message
> news:1149663498.822467.54570@g10g2000cwb.googlegro ups.com...
> > i wonder if there is an option to enter SQL sentence in order to change
> > its structure and data via vba code
> >

#6 (**permalink**) 28-Jul-2006, 08:17 AM

Yaniv,

You don't even need a (saved) query at all in this case... you can just
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10951
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10951
construct your SQL statement in code and execute it without saving it at
all.
As a simple example, suppose there is a table called tblSpecies, and you
want to filter on field SpeciesType (animal, flower etc) and create a
new table called tblSpeciesOfType; filter selection is made by means of
a combo box (cboSpeciesType) on a form, and the make-table query is run
by clicking a command button on the same form. The code behind the
command button would look something like:

Dim strSQL As String
If IsNull(Me.cboSpeciesType) Then
MsgBox "No species type selected.", vbExclamation, _
"Error Creating Table"
Exit Sub
End If
strSQL = "SELECT * INTO tblSpeciesOfType FROM tblSpecies " & _
"WHERE SpeciesType = '" & Me.cboSpeciesType & "'"
CurrentDb.Execute strSQL, dbFailOnError

This will overwrite the table if it already exists.

That said, are you positive you need to create a new table? Why don't
you just filter on the existing one(s) by means of a Select query?
Duplication of data is not advisable for a number of reasons, having to
do with data integrity and maintenance, storage space etc. Elaborate if
you need further help.

HTH,
Nikos

#7 (**permalink**) 28-Jul-2006, 08:17 AM

As NIkos points out, you may not require anything.

While it is possible to use ADOX to accomplish the same, since you're
dealing with QueryDef objects in a Jet database, you're better off using
DAO. There's no problem using both ADO and DAO in the same application: I do
it all the time.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10951

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10951

"yaniv d" wrote in message
news:1149682431.357014.200250@f6g2000cwb.googlegro ups.com...
> hi thanks for your help,
> i'm woring on ADO,can you give me a code for it
> Douglas J Steele wrote:
> > Dim dbCurr As DAO.Database
> > Dim qdfCurr As DAO.QueryDef
> > Dim strSQL As String
> >
> > strSQL = "SELECT ...."
> > Set dbCurr = CurrentDb()
> > Set qdfCurr = dbCurr.QueryDefs("MyQuery")
> > qdfCurr.SQL = strSQL
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no e-mails, please!)
> >
> >
> > "yaniv d" wrote in message
> > news:1149663498.822467.54570@g10g2000cwb.googlegro ups.com...
> > > i wonder if there is an option to enter SQL sentence in order to
change
> > > its structure and data via vba code
> > >
>

#8 (**permalink**) 28-Jul-2006, 08:17 AM

hi douglas,
thanks for your review.
my goal is to make my tool as dinamic as possible,one form and one
query modular for all the group(47 of them) so it will be easy to
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10951
maintain for later on and small in the size for the local hard drive.
i believe the direction that you and nikos gave me will give me some
path.
the issue is that i dont want to add any referenced object to my tool
that when i dont know if i dont know how it will react in the client
desktop.(means i prefer to use the current object rather then to worry
if the activeX is istalled or not,please correct me if im wrong).

thanks again for both of you

Douglas J Steele wrote:
> As NIkos points out, you may not require anything.
>
> While it is possible to use ADOX to accomplish the same, since you're
> dealing with QueryDef objects in a Jet database, you're better off using
> DAO. There's no problem using both ADO and DAO in the same application: I do
> it all the time.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10951
>
>
> "yaniv d" wrote in message
> news:1149682431.357014.200250@f6g2000cwb.googlegro ups.com...
> > hi thanks for your help,
> > i'm woring on ADO,can you give me a code for it
> > Douglas J Steele wrote:
> > > Dim dbCurr As DAO.Database
> > > Dim qdfCurr As DAO.QueryDef
> > > Dim strSQL As String
> > >
> > > strSQL = "SELECT ...."
> > > Set dbCurr = CurrentDb()
> > > Set qdfCurr = dbCurr.QueryDefs("MyQuery")
> > > qdfCurr.SQL = strSQL
> > >
> > > --
> > > Doug Steele, Microsoft Access MVP
> > > http://I.Am/DougSteele
> > > (no e-mails, please!)
> > >
> > >
> > > "yaniv d" wrote in message
> > > news:1149663498.822467.54570@g10g2000cwb.googlegro ups.com...
> > > > i wonder if there is an option to enter SQL sentence in order to
> change
> > > > its structure and data via vba code
> > > >
> >