access (dao) request doesnt work 2

[stefanhab]

hi, i have a problem that drives me mad since i can't see a mistake and think the code once worked but doesn't anymore (but i am starting to get the impression, that it was a distraction of sense)

Sub COPY()
Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM Working_Table_KAP1") ' opens the recordset1
Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM Duplication_Test") ' opens the recordset2 WHICH IS EMPTY AT THE BEGINNING

If Not rs1.EOF And Not rs2.EOF Then ' dont know why, but some it-experts told me to use that
rs1.MoveFirst
rs2.MoveFirst
Do Until rs1.EOF ' he should run through the whole recordset1
var1 = rs1("KATALOG_ID") ' and put the content of the column KATALOG_ID into var1
var2 = rs1("PROFIL_ID") '...
var3 = rs1("CONCAT")
var4 = rs1("SWERT")

If var2 = 3225 Then ' this is just a test, so he should copy just those cells where PROFIL_ID is 3225 in recordset1
rs2.AddNew ' to add a new row (record) in recordset2
rs2.Fields("PROFIL_ID") = var2 ' copy the content of var1 from recset1 to recset2 into the corresponding column
rs2.Fields("KATALOG_ID") = var1
rs2.Fields("CONCAT") = var3
rs2.Fields("SWERT") = var4
rs2.Update ' to do the job
rs2.MoveNext ' to jump to the next line
End If
rs1.MoveNext
Loop
End If
Set rs1 = Nothing
Set rs2 = Nothing
End Sub



nothing happpens, but the code seems so logical to me and i cant explain what is going wrong (and the most confusing thing is, that i am sure it already worked - but it isnt anymore)



thanks for any kind of recommendation,
stefan



annotation: i am also open to raw sql (in vba) solutions even though i coded it in this dao library, since i do not know (or it is not possible - due to the selection) how to solve such an tast with sql

 

[Remou]

You say
[tt]' opens the recordset2 WHICH IS EMPTY AT THE BEGINNING[/tt]

Then you say
[tt]If Not rs1.EOF And Not rs2.EOF Then[/tt]

In other words, this code will never run. Stepping through the code would have shown that.

From a quick glance, it looks like this can be done with an append query. Why not use the query design screen to rough something out? You can post the SQL back here if you have a problem.

 

[stefanhab]

hi Remou,

thanks for the input.

I deleted the And Not rs2.EOF
and then also rs2.MoveFirst (because access forced me to do so)

but then failure 3021 occured (no actual recordset)
--> thats why i implemented these EOF things

 

[stefanhab]

oh no wait, there happend something with the database - i think i must have worked partially,

i try a little bit more

 

[stefanhab]

hey, thanks a lot for your guideline

Sub COPY()
Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM Working_Table_KAP1")
Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM Duplication_Test")

If Not rs1.EOF Then ' And Not rs2.EOF
rs1.MoveFirst
rs2.MoveFirst
Do Until rs1.EOF
var1 = rs1("KATALOG_ID")
var2 = rs1("PROFIL_ID")
var3 = rs1("CONCAT")
var4 = rs1("SWERT")

If var2 = 1026 Then
rs2.AddNew
rs2.Fields("PROFIL_ID") = var2
rs2.Fields("KATALOG_ID") = var1
rs2.Fields("CONCAT") = var3
rs2.Fields("SWERT") = var4
rs2.Update
rs2.MoveNext
End If
rs1.MoveNext
Loop
End If
Set rs1 = Nothing
Set rs2 = Nothing
End Sub



after deleting the And Not rs2.EOF but leaving the and then also rs2.MoveFirst inside - everything worked well

thanks a lot
stefan

 

[stefanhab]

but why does it only work with numeric variables?

If var1 = 34 Then ... works since var1 is a numeric variable,

but

If var3 = "1_1_1_1" doenst work - beides var3 is a text string

 

[stefanhab]

oh, no it doesnt work anymore

 

[Remou]

It really would be much easier with SQL. For example:

strSQL="INSERT INTO Duplication_Test ( " _
& "KATALOG_ID, PROFIL_ID, CONCAT, SWERT ) " _
& "SELECT KATALOG_ID, PROFIL_ID, CONCAT, SWERT " _
& "FROM Working_Table_KAP1 " _
& "WHERE PROFIL_ID = 1026"
CurrentDB.Execute strSQL, dbFailOnError

In general, you cannot insert a text field into a numeric field. Check that the field types match. Your code is still a little awkward, we can work on it if you still want to go that way.

 

[stefanhab]

thanks for your response,

nono - the fieds are both text

so CONCAT is in recset1 and recset2 a text field

 

[Remou]

You will need to provide more information than "doesn't work". For example, what error are you getting?

 

[stefanhab]

nono,

the doesnt work was referring to my old code - i actually try yours and it worked at least with the numeric variables (at my code i had to delete the rs2.MoveNext and the rs2.MoveFirst as well, so that it worked again - but also not for the text-columns)

 

[stefanhab]

WHERE CONCAT = 1_1_1_1 "
"WHERE CONCAT = "1_1_1_1" "
"WHERE CONCAT = '1_1_1_1' " which i expected should be the correct one
"WHERE CONCAT = '"1_1_1_1"' "
"WHERE CONCAT = ("1_1_1_1") "
"WHERE CONCAT = `1_1_1_1` "

but none of them worked

 

[Remou]

This is a little tidier:

Sub COPY()
    Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM Working_Table_KAP1")
    Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM Duplication_Test")
    
     Do Until rs1.EOF
                   
        If rs1("PROFIL_ID") = 1026 Then
            rs2.AddNew
            rs2.Fields("PROFIL_ID") = rs1("PROFIL_ID")
            rs2.Fields("KATALOG_ID") = rs1("KATALOG_ID")
            rs2.Fields("CONCAT") = rs1("CONCAT")
            rs2.Fields("SWERT") = rs1("SWERT")
            rs2.Update
        End If
        rs1.MoveNext
    Loop
    Set rs1 = Nothing
    Set rs2 = Nothing
End Sub

My code is the SQL, are you saying you have decided to go with that?

 

[stefanhab]

My code is the SQL, are you saying you have decided to go with that?"

yes - i tried it with the numeric variable (as a test) and it worked well but access refuses my text-trials

 

[stefanhab]

i tried to outwit access but it didnt work

Sub COPY_SQL()

Dim strSQL As String
Dim x As String

var_xxx = "1_1_1_1"


strSQL = "INSERT INTO Duplication_Test ( " & _
"KATALOG_ID, PROFIL_ID, CONCAT, SWERT ) " & _
"SELECT KATALOG_ID, PROFIL_ID, CONCAT, SWERT " & _
"FROM Working_Table_KAP1 " & _
"WHERE CONCAT = var_xxx "

CurrentDb.Execute strSQL, dbFailOnError

End Sub

 

[Remou]

Yes, it is a little confusing:

strSQL = "INSERT INTO Duplication_Test ( " & _
"KATALOG_ID, PROFIL_ID, CONCAT, SWERT ) " & _
"SELECT KATALOG_ID, PROFIL_ID, CONCAT, SWERT " & _
"FROM Working_Table_KAP1 " & _
"WHERE CONCAT ='" &  var_xxx & "'"

 

[stefanhab]

OH YEAH, it works - THANK YOU SO MUCH

Sub COPY_SQL()

Dim strSQL As String
Dim var_xxx As String

var_xxx = "1_1_1_1"


strSQL = "INSERT INTO Duplication_Test ( " & _
"KATALOG_ID, PROFIL_ID, CONCAT, SWERT ) " & _
"SELECT KATALOG_ID, PROFIL_ID, CONCAT, SWERT " & _
"FROM Working_Table_KAP1 " & _
"WHERE CONCAT ='" & var_xxx & "'"

CurrentDb.Execute strSQL, dbFailOnError

End Sub

and referring to your question or statement "why not raw SQL?"

i must confess, that when i am coding by myself, i prefer this dao thing, since i understand this do while structure much better (i am not a programmer) and sql always surprises me, what it can do - even though i didnt expect, that it can do.


so thanks a lot (and i think i will take more care on sql, since this is already the second time, that this discussion board transformed and simplified into sql)


best wishes
Stefan Habermehl

 

[JerryKlmns]

You could also incorporate the CONCAT field (from thread701-1423071) in that last code, without first creating it at Working_Table!