build a query via vba

[yaniv d]

i wonder if there is an option to enter SQL sentence in order to change
its structure and data via vba code

 

[Nikos Yannacopoulos]

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

 

[yaniv d]

ok,thanks for your replay.
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)
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

 

[Douglas J Steele]

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

 

[yaniv d]

hi thanks for your help,
i'm woring on ADO,can you give me a code for it

 

[Nikos Yannacopoulos]

Yaniv,

You don't even need a (saved) query at all in this case... you can just
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

 

[Douglas J Steele]

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.

 

[yaniv d]

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