Append Query VBA help

[IKMD66]

Hi,

I would appreciate your assistance with some VBA. I have to receive data in
a semi-structured vertical format and have worked through how to get this
into a useable format in the database.
The issue I have is the the number of columns vary depending on the
originating doument - whilst I could replicate the number of queries and
hardcode.
However I think there should be a better way....depending on the field count
I want to (would like to) pass a variable to the SQL for the query in VBA and
just simply loop a counter until the total fieldf count is reached.

The logic for the query is below:
db.Execute ("INSERT INTO tblInterim ( A, [Original ID] )
SELECT tblIdocImport.F19, tblIdocImport.ID
FROM tblIdocImport
WHERE (((tblIdocImport.F19) Is Not Null))"), dbFailOnError

"F19" is the variable that I want to change each time in the loop.

Any pointers on how to do this is appreciated.

Many Thanks,
Kirk

 

[Dirk Goldgar]

Hi,

I would appreciate your assistance with some VBA. I have to receive data
in
a semi-structured vertical format and have worked through how to get this
into a useable format in the database.
The issue I have is the the number of columns vary depending on the
originating doument - whilst I could replicate the number of queries and
hardcode.
However I think there should be a better way....depending on the field
count
I want to (would like to) pass a variable to the SQL for the query in VBA
and
just simply loop a counter until the total fieldf count is reached.

The logic for the query is below:
db.Execute ("INSERT INTO tblInterim ( A, [Original ID] )
SELECT tblIdocImport.F19, tblIdocImport.ID
FROM tblIdocImport
WHERE (((tblIdocImport.F19) Is Not Null))"), dbFailOnError

"F19" is the variable that I want to change each time in the loop.

Any pointers on how to do this is appreciated.

Many Thanks,
Kirk

If I understand you, you could do something like this:

'------ start of example code ------

Dim intStartCol As Integer ' First column to be imported
Dim intEndCol As Integer ' Last column to be imported
Dim intCol As Integer ' Current column being processed

intStartCol = ... ' Set this based on inspection
intEndCol = ... ' Set this based on inspection

For intCol = intStartCol to intEndCol

db.Execute _
"INSERT INTO tblInterim ( A, [Original ID] ) " & _
"SELECT F" & intCol & ", ID FROM tblIdocImport " & _
"WHERE F" & intCol & " Is Not Null", _
dbFailOnError

Next intCol

'------ end of example code ------

 

[IKMD66]

Dirk,

Thank you very much - this worked a treat.

Regards,
Kirk

Hi,

I would appreciate your assistance with some VBA. I have to receive data
in
a semi-structured vertical format and have worked through how to get this
into a useable format in the database.
The issue I have is the the number of columns vary depending on the
originating doument - whilst I could replicate the number of queries and
hardcode.
However I think there should be a better way....depending on the field
count
I want to (would like to) pass a variable to the SQL for the query in VBA
and
just simply loop a counter until the total fieldf count is reached.

The logic for the query is below:
db.Execute ("INSERT INTO tblInterim ( A, [Original ID] )
SELECT tblIdocImport.F19, tblIdocImport.ID
FROM tblIdocImport
WHERE (((tblIdocImport.F19) Is Not Null))"), dbFailOnError

"F19" is the variable that I want to change each time in the loop.

Any pointers on how to do this is appreciated.

Many Thanks,
Kirk

If I understand you, you could do something like this:

'------ start of example code ------

Dim intStartCol As Integer ' First column to be imported
Dim intEndCol As Integer ' Last column to be imported
Dim intCol As Integer ' Current column being processed

intStartCol = ... ' Set this based on inspection
intEndCol = ... ' Set this based on inspection

For intCol = intStartCol to intEndCol

db.Execute _
"INSERT INTO tblInterim ( A, [Original ID] ) " & _
"SELECT F" & intCol & ", ID FROM tblIdocImport " & _
"WHERE F" & intCol & " Is Not Null", _
dbFailOnError

Next intCol

'------ end of example code ------

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)