multiple append queries using VBA

[Barn Yard]

good morning,

im still kind of new to VBA but I have learned some interesting things
so far. Right now I am normalizing my survey database so I'm having
to run an append query for each question(300 in some surveys).
WARNING: I've very new at this so this may not look right. i hope it
gives the idea of what I'm trying to accomplish. Before I was just
chaning the 1's to 2's then to 3's in the Design view of my query.
hope this can save me some time. thanks in advance!

Sub RunAppQuery()

Dim db As DAO.Database
Dim qryTest As DAO.QueryDef
Dim IntCnt As Integer

Set db = CurrentDb()
Set qryTest = dbsCurrent.QueryDefs("Query1")

For IntCnt = 1 to 300

qryTest.SQL = "INSERT INTO tblResponses ( RspnsID, Rspns, QstnID )
SELECT FT.ID, FT.FT" & IntCnt & ", "FT" & IntCnt & "" AS Expr1
FROM FT;"

Next IntCnt

End Sub

[Pieter Linden]

ba******@yahoo.com (Barn Yard) wrote in message news:<e0**************************@posting.google. com>...

good morning,

im still kind of new to VBA but I have learned some interesting things
so far. Right now I am normalizing my survey database so I'm having
to run an append query for each question(300 in some surveys).
WARNING: I've very new at this so this may not look right. i hope it
gives the idea of what I'm trying to accomplish. Before I was just
chaning the 1's to 2's then to 3's in the Design view of my query.
hope this can save me some time. thanks in advance!

Sub RunAppQuery()

Dim db As DAO.Database
Dim qryTest As DAO.QueryDef
Dim IntCnt As Integer

Set db = CurrentDb()
Set qryTest = dbsCurrent.QueryDefs("Query1")

For IntCnt = 1 to 300

qryTest.SQL = "INSERT INTO tblResponses ( RspnsID, Rspns, QstnID )
SELECT FT.ID, FT.FT" & IntCnt & ", "FT" & IntCnt & "" AS Expr1
FROM FT;"

Next IntCnt

End Sub

Do everyone that could help you a favor... post ABBREVIATED versions
of the structures of your original table and final table. If you have
the question number as a field, you could loop through the fields
collection of the table/query and use those for your append...

Something like
tdf.Fields(intCounter)
or
qdf.fields(intCounter)

just remember that the Fields collection in a querydef or a tabledef
is zero-based, so it will run from 0 to (tdf.fields.count-1)

Also, you could just use a recordset for this. Probably faster than
executing queries all over the place....

[Barn Yard]

Pieter,

Thank you for replying and sorry about the lack of table structures.
I'll remember to put those in from now on. :) The FT1, FT2, and so on
are the question responses. I'm using append queries for each
question in order to arrive to tblResponses, I was looking for a
faster was to get there. I'm unsure how to use the recordset
property. Can you please show me a short example of how i might use
it? Thanks so much! -B

Here are the table structures:

----
tblFT (non-relational design)

ID Number
FT1 Text
FT2 Text
FT3 Text
FT4 Text
FT5 Text
FT6 Text
and so on....
-----
tblResponses' (normalized)

ID Number
QstnID Text
Respns Text
-----
pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. com>...

ba******@yahoo.com (Barn Yard) wrote in message news:<e0**************************@posting.google. com>...

good morning,

im still kind of new to VBA but I have learned some interesting things
so far. Right now I am normalizing my survey database so I'm having
to run an append query for each question(300 in some surveys).
WARNING: I've very new at this so this may not look right. i hope it
gives the idea of what I'm trying to accomplish. Before I was just
chaning the 1's to 2's then to 3's in the Design view of my query.
hope this can save me some time. thanks in advance!

Sub RunAppQuery()

Dim db As DAO.Database
Dim qryTest As DAO.QueryDef
Dim IntCnt As Integer

Set db = CurrentDb()
Set qryTest = dbsCurrent.QueryDefs("Query1")

For IntCnt = 1 to 300

qryTest.SQL = "INSERT INTO tblResponses ( RspnsID, Rspns, QstnID )
SELECT FT.ID, FT.FT" & IntCnt & ", "FT" & IntCnt & "" AS Expr1
FROM FT;"

Next IntCnt

End Sub

Do everyone that could help you a favor... post ABBREVIATED versions
of the structures of your original table and final table. If you have
the question number as a field, you could loop through the fields
collection of the table/query and use those for your append...

Something like
tdf.Fields(intCounter)
or
qdf.fields(intCounter)

just remember that the Fields collection in a querydef or a tabledef
is zero-based, so it will run from 0 to (tdf.fields.count-1)

Also, you could just use a recordset for this. Probably faster than
executing queries all over the place....

[Pieter Linden]

ba******@yahoo.com (Barn Yard) wrote in message news:<e0**************************@posting.google. com>...

Pieter,

Thank you for replying and sorry about the lack of table structures.
I'll remember to put those in from now on. :) The FT1, FT2, and so on
are the question responses. I'm using append queries for each
question in order to arrive to tblResponses, I was looking for a
faster was to get there. I'm unsure how to use the recordset
property. Can you please show me a short example of how i might use
it? Thanks so much! -B

Here are the table structures:

----
tblFT (non-relational design)

ID Number
FT1 Text
FT2 Text
FT3 Text
FT4 Text
FT5 Text
FT6 Text
and so on....
-----
tblResponses' (normalized)

ID Number
QstnID Text
Respns Text
-----

So FT# is the question, and the response goes in that field. OK...

I think I need some amyl nitrate... LOL... so basically what you want
to do is loop through your denormalized table and grab the ID and FT#
(where # is some number corresponding to a field in the table), right?
If so, you could create your SQL statement using

tdf.fields(0).Name and tdf.fields(intCounter).Name Something like...

For intCounter = 1 to tdf.fields.count-1
strSQL = "INSERT INTO tblResponses(QstnID,Rspns) VALUES (SELECT
tblResponses.QstnID " & tdf.fields(intCounter).Name & " FROM
tblResponses)"
db.Execute strSQL, dbFailOnError
Next intCounter

Confused yet? Basically, the way I would go about it is to loop
through the fields collection of your denormalized table, and use a
CurrentDB.Execute strSQL kind of thing, where you build the SQL
statement on the fly. Have a look at the fields collection in help.
(A table consists of a zero-based collection of fields, so you can
refer to a field as tdf.fields(intFieldNo))

So then you can see automate most of this.

If this is all just way over your head, try something simple first to
get an idea of what's going on... something like

dim db as dao.database
dim tdf as dao.tabledef
dim fld as dao.field

set db=currentdb
set tdf = db.Tabledefs("tblFT")
for intCounter = 0 to tdf.fields.Count-1
debug.print intCounter, tdf.fields(intCounter).Name
next intCounter
set tdf=nothing
set db=nothing

you'll get something like this:
0 ID
1 FT1
2 FT2
3 FT3
....

then you can use that to build your SQL statement on the fly, and use
currentdb.Execute strSQL to execute the SQL statement for you.

Confused? Great! Mission accomplished. <g>

[Barn Yard]

Pieter,

I think I got it but I'm stuck on building strSQL. The one you
provided showed up red in the VB editor and showed it getting stuck
at:

strSQL = "INSERT INTO tblResponses(QstnID,Rspns) VALUES (SELECT
*here* tblResponses.QstnID " & tdf.fields(intCounter).Name & " FROM
tblResponses)"

I tried to fiddle with it and tried putting it on one line but
couldn't get it to work. The one I posted below I know is wrong
because I get a run-time error 3346 'Number of query fields and
destination fields are not the same'. Thanks for all your help. But
I'm understanding how the looping works as I see the value of
tdf.fields(intCounter).Name changes as I push F8 in the editor. This
is what I am currently have:

Thanks for all your help again!

Option Compare Database

Sub LoopFields()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim intCounter As Integer
Dim strSQL As String

Set db = CurrentDb
Set tdf = db.Tabledefs("tblFT")
For intCounter = 0 To tdf.Fields.Count - 1
Debug.Print intCounter, tdf.Fields(intCounter).Name
strSQL = "INSERT INTO tblResponses ( RspnsID, QstnID, Rspns )
SELECT tblFT." & tdf.Fields(intCounter).Name & " FROM FT;"
db.Execute strSQL, dbFailOnError
Next intCounter

Set tdf = Nothing
Set db = Nothing

End Sub


pi********@hotmail.com (Pieter Linden) wrote in message news:<bf*************************@posting.google.c om>...

ba******@yahoo.com (Barn Yard) wrote in message news:<e0**************************@posting.google. com>...

Pieter,

Thank you for replying and sorry about the lack of table structures.
I'll remember to put those in from now on. :) The FT1, FT2, and so on
are the question responses. I'm using append queries for each
question in order to arrive to tblResponses, I was looking for a
faster was to get there. I'm unsure how to use the recordset
property. Can you please show me a short example of how i might use
it? Thanks so much! -B

Here are the table structures:

----
tblFT (non-relational design)

ID Number
FT1 Text
FT2 Text
FT3 Text
FT4 Text
FT5 Text
FT6 Text
and so on....
-----
tblResponses' (normalized)

ID Number
QstnID Text
Respns Text
-----

So FT# is the question, and the response goes in that field. OK...

I think I need some amyl nitrate... LOL... so basically what you want
to do is loop through your denormalized table and grab the ID and FT#
(where # is some number corresponding to a field in the table), right?
If so, you could create your SQL statement using

tdf.fields(0).Name and tdf.fields(intCounter).Name Something like...

For intCounter = 1 to tdf.fields.count-1
strSQL = "INSERT INTO tblResponses(QstnID,Rspns) VALUES (SELECT
tblResponses.QstnID " & tdf.fields(intCounter).Name & " FROM
tblResponses)"
db.Execute strSQL, dbFailOnError
Next intCounter

Confused yet? Basically, the way I would go about it is to loop
through the fields collection of your denormalized table, and use a
CurrentDB.Execute strSQL kind of thing, where you build the SQL
statement on the fly. Have a look at the fields collection in help.
(A table consists of a zero-based collection of fields, so you can
refer to a field as tdf.fields(intFieldNo))

So then you can see automate most of this.

If this is all just way over your head, try something simple first to
get an idea of what's going on... something like

dim db as dao.database
dim tdf as dao.tabledef
dim fld as dao.field

set db=currentdb
set tdf = db.Tabledefs("tblFT")
for intCounter = 0 to tdf.fields.Count-1
debug.print intCounter, tdf.fields(intCounter).Name
next intCounter
set tdf=nothing
set db=nothing

you'll get something like this:
0 ID
1 FT1
2 FT2
3 FT3
...

then you can use that to build your SQL statement on the fly, and use
currentdb.Execute strSQL to execute the SQL statement for you.

Confused? Great! Mission accomplished. <g>

[Pieter Linden]

Barn,

This *might* be what you're after. I made the code write all the SQLs
to the debug window so you could look at them before executing them.
Just comment out the "db.Execute strSQL, dbFailOnError" line and run
the code.
Sub AppendRecordsToNormalizedTable()
Dim db As DAO.Database
Dim tdf As DAO.TableDef

Dim intCounter As Integer
Dim strSQL As String

Set db = CurrentDb
Set tdf = db.TableDefs("tblFT")

For intCounter = 1 To tdf.Fields.Count - 1
strSQL = "INSERT INTO tblResponses(RspnsID,Rspns,QstnID)
SELECT ID, " & tdf.Fields(intCounter).Name & ", " & intCounter & " AS
QuestID FROM tblFT;"
Debug.Print strSQL
'db.Execute strSQL, dbFailOnError '---Remove leading ' to
run...
Debug.Print
Next intCounter

Set tdf = Nothing
Set db = Nothing
End Sub
Okay, the tdf.Fields(intCounter).Name thing is just looping through
the fields in the table (I'm skipping 0,the ID field on purpose). So
I'm going across the table and building the SQL statements on the fly
(in the strSQL variable) and then executing them. Hope this is closer
than before... and DON'T run this on real data... well, not yet
anyway...

Hope this works for ya,
Pieter