AccessTr.neT

Public Sub sqls(lastTableName As String, newTableName As String, criteriaFieldName As String)

    Dim i As Integer

    Dim sql, strSQL As String

    Dim lastColumnName, newColumnName As String

    Dim name1, name2 As String

    

    strSQL = ""



    For i = 0 To CurrentDb.TableDefs(lastTableName).Fields.Count - 1

    

        lastColumnName = CurrentDb.TableDefs(lastTableName).Fields(i).Name

        newColumnName = CurrentDb.TableDefs(newTableName).Fields(i).Name

  

        Sql = "SELECT" & _

        " [" & lastTableName & "].[" & criteriaFieldName & "]" & "," & _

        " '" & lastColumnName & "' AS AlanAdi" & "," & _

        " [" & lastTableName & "].[" & lastColumnName & "]" & "," & _

        " [" & newTableName & "].[" & newColumnName & "]" & _

        " FROM [" & lastTableName & "]" & _

        " INNER JOIN [" & newTableName & "] ON [" & lastTableName & "].[" & criteriaFieldName & "] = [" & newTableName & "].[" & criteriaFieldName & "]" & _

        " WHERE [" & lastTableName & "].[" & lastColumnName & "]<>[" & newTableName & "].[" & newColumnName & "]"



        strSQL = strSQL & sql

        

        If (i < CurrentDb.TableDefs(lastTableName).Fields.Count - 1) Then

            strSQL = strSQL & " UNION ALL "

        Else

            strSQL = strSQL & ";"

        End If

        

    Next i



    Me.Liste0.RowSource = strSQL

    Me.Liste0.Requery

    

    

End Sub

SELECT [EGITmMX].[SICNO], 'ESCY34' AS AlanAdi, [EGITmMX].[ESCY34], [EGITmM].[ESCY34] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[ESCY34]<>[EGITmM].[ESCY34] UNION ALL SELECT [EGITmMX].[SICNO], 'MASYER' AS AlanAdi, [EGITmMX].[MASYER], [EGITmM].[MASYER] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[MASYER]<>[EGITmM].[MASYER] UNION ALL SELECT [EGITmMX].[SICNO], 'GRVADI' AS AlanAdi, [EGITmMX].[GRVADI], [EGITmM].[GRVADI] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[GRVADI]<>[EGITmM].[GRVADI] UNION ALL SELECT [EGITmMX].[SICNO], 'GORADI' AS AlanAdi, [EGITmMX].[GORADI], [EGITmM].[GORADI] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[GORADI]<>[EGITmM].[GORADI] UNION ALL SELECT [EGITmMX].[SICNO], 'SICNO' AS AlanAdi, [EGITmMX].[SICNO], [EGITmM].[SICNO] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[SICNO]<>[EGITmM].[SICNO] UNION ALL SELECT [EGITmMX].[SICNO], 'ESCY06' AS AlanAdi, [EGITmMX].[ESCY06], [EGITmM].[ESCY06] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[ESCY06]<>[EGITmM].[ESCY06] UNION ALL SELECT [EGITmMX].[SICNO], 'MASCIN' AS AlanAdi, [EGITmMX].[MASCIN], [EGITmM].[MASCIN] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[MASCIN]<>[EGITmM].[MASCIN] UNION ALL SELECT [EGITmMX].[SICNO], 'PERAD' AS AlanAdi, [EGITmMX].[PERAD], [EGITmM].[PERAD] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[PERAD]<>[EGITmM].[PERAD] UNION ALL SELECT [EGITmMX].[SICNO], 'PERSOY' AS AlanAdi, [EGITmMX].[PERSOY], [EGITmM].[PERSOY] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[PERSOY]<>[EGITmM].[PERSOY] UNION ALL SELECT [EGITmMX].[SICNO], 'CTEL' AS AlanAdi, [EGITmMX].[CTEL], [EGITmM].[CTEL] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[CTEL]<>[EGITmM].[CTEL] UNION ALL SELECT [EGITmMX].[SICNO], 'KE' AS AlanAdi, [EGITmMX].[KE], [EGITmM].[KE] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[KE]<>[EGITmM].[KE] UNION ALL SELECT [EGITmMX].[SICNO], 'VATNO' AS AlanAdi, [EGITmMX].[VATNO], [EGITmM].[VATNO] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[VATNO]<>[EGITmM].[VATNO] UNION ALL SELECT [EGITmMX].[SICNO], 'DOGGUN' AS AlanAdi, [EGITmMX].[DOGGUN], [EGITmM].[DOGGUN] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[DOGGUN]<>[EGITmM].[DOGGUN] UNION ALL SELECT [EGITmMX].[SICNO], 'DOGAY' AS AlanAdi, [EGITmMX].[DOGAY], [EGITmM].[DOGAY] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[DOGAY]<>[EGITmM].[DOGAY] UNION ALL SELECT [EGITmMX].[SICNO], 'DOGYIL' AS AlanAdi, [EGITmMX].[DOGYIL], [EGITmM].[DOGYIL] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[DOGYIL]<>[EGITmM].[DOGYIL] UNION ALL SELECT [EGITmMX].[SICNO], 'GIRGUN' AS AlanAdi, [EGITmMX].[GIRGUN], [EGITmM].[GIRGUN] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[GIRGUN]<>[EGITmM].[GIRGUN] UNION ALL SELECT [EGITmMX].[SICNO], 'GIRAY' AS AlanAdi, [EGITmMX].[GIRAY], [EGITmM].[GIRAY] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[GIRAY]<>[EGITmM].[GIRAY] UNION ALL SELECT [EGITmMX].[SICNO], 'GIRYIL' AS AlanAdi, [EGITmMX].[GIRYIL], [EGITmM].[GIRYIL] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[GIRYIL]<>[EGITmM].[GIRYIL] UNION ALL SELECT [EGITmMX].[SICNO], 'TAHSIL' AS AlanAdi, [EGITmMX].[TAHSIL], [EGITmM].[TAHSIL] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[TAHSIL]<>[EGITmM].[TAHSIL] UNION ALL SELECT [EGITmMX].[SICNO], 'MEZUN' AS AlanAdi, [EGITmMX].[MEZUN], [EGITmM].[MEZUN] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[MEZUN]<>[EGITmM].[MEZUN] UNION ALL SELECT [EGITmMX].[SICNO], 'KURS3' AS AlanAdi, [EGITmMX].[KURS3], [EGITmM].[KURS3] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[KURS3]<>[EGITmM].[KURS3] UNION ALL SELECT [EGITmMX].[SICNO], 'EMEKLI' AS AlanAdi, [EGITmMX].[EMEKLI], [EGITmM].[EMEKLI] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[EMEKLI]<>[EGITmM].[EMEKLI];

SELECT degisenler.* INTO tDegisenler FROM degisenler;

SELECT degisenler.* INTO tDegisenler FROM degisenler;

INSERT INTO tDegisenler ( SICNO, AlanAdi, EGITmMX_ESCY34, EGITmM_ESCY34 )

SELECT degisenler.SICNO, degisenler.AlanAdi, degisenler.EGITmMX.ESCY34, degisenler.EGITmM.ESCY34

FROM degisenler;

SELECT degisenler.* INTO tDegisenler FROM (SELECT [EGITmMX].[SICNO], 'ESCY34' AS AlanAdi, [EGITmMX].[ESCY34], [EGITmM].[ESCY34] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[ESCY34]<>[EGITmM].[ESCY34] UNION ALL SELECT [EGITmMX].[SICNO], 'MASYER' AS AlanAdi, [EGITmMX].[MASYER], [EGITmM].[MASYER] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[MASYER]<>[EGITmM].[MASYER] UNION ALL SELECT [EGITmMX].[SICNO], 'GRVADI' AS AlanAdi, [EGITmMX].[GRVADI], [EGITmM].[GRVADI] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[GRVADI]<>[EGITmM].[GRVADI] UNION ALL SELECT [EGITmMX].[SICNO], 'GORADI' AS AlanAdi, [EGITmMX].[GORADI], [EGITmM].[GORADI] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[GORADI]<>[EGITmM].[GORADI] UNION ALL SELECT [EGITmMX].[SICNO], 'SICNO' AS AlanAdi, [EGITmMX].[SICNO], [EGITmM].[SICNO] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[SICNO]<>[EGITmM].[SICNO] UNION ALL SELECT [EGITmMX].[SICNO], 'ESCY06' AS AlanAdi, [EGITmMX].[ESCY06], [EGITmM].[ESCY06] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[ESCY06]<>[EGITmM].[ESCY06] UNION ALL SELECT [EGITmMX].[SICNO], 'MASCIN' AS AlanAdi, [EGITmMX].[MASCIN], [EGITmM].[MASCIN] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[MASCIN]<>[EGITmM].[MASCIN] UNION ALL SELECT [EGITmMX].[SICNO], 'PERAD' AS AlanAdi, [EGITmMX].[PERAD], [EGITmM].[PERAD] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[PERAD]<>[EGITmM].[PERAD] UNION ALL SELECT [EGITmMX].[SICNO], 'PERSOY' AS AlanAdi, [EGITmMX].[PERSOY], [EGITmM].[PERSOY] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[PERSOY]<>[EGITmM].[PERSOY] UNION ALL SELECT [EGITmMX].[SICNO], 'CTEL' AS AlanAdi, [EGITmMX].[CTEL], [EGITmM].[CTEL] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[CTEL]<>[EGITmM].[CTEL] UNION ALL SELECT [EGITmMX].[SICNO], 'KE' AS AlanAdi, [EGITmMX].[KE], [EGITmM].[KE] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[KE]<>[EGITmM].[KE] UNION ALL SELECT [EGITmMX].[SICNO], 'VATNO' AS AlanAdi, [EGITmMX].[VATNO], [EGITmM].[VATNO] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[VATNO]<>[EGITmM].[VATNO] UNION ALL SELECT [EGITmMX].[SICNO], 'DOGGUN' AS AlanAdi, [EGITmMX].[DOGGUN], [EGITmM].[DOGGUN] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[DOGGUN]<>[EGITmM].[DOGGUN] UNION ALL SELECT [EGITmMX].[SICNO], 'DOGAY' AS AlanAdi, [EGITmMX].[DOGAY], [EGITmM].[DOGAY] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[DOGAY]<>[EGITmM].[DOGAY] UNION ALL SELECT [EGITmMX].[SICNO], 'DOGYIL' AS AlanAdi, [EGITmMX].[DOGYIL], [EGITmM].[DOGYIL] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[DOGYIL]<>[EGITmM].[DOGYIL] UNION ALL SELECT [EGITmMX].[SICNO], 'GIRGUN' AS AlanAdi, [EGITmMX].[GIRGUN], [EGITmM].[GIRGUN] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[GIRGUN]<>[EGITmM].[GIRGUN] UNION ALL SELECT [EGITmMX].[SICNO], 'GIRAY' AS AlanAdi, [EGITmMX].[GIRAY], [EGITmM].[GIRAY] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[GIRAY]<>[EGITmM].[GIRAY] UNION ALL SELECT [EGITmMX].[SICNO], 'GIRYIL' AS AlanAdi, [EGITmMX].[GIRYIL], [EGITmM].[GIRYIL] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[GIRYIL]<>[EGITmM].[GIRYIL] UNION ALL SELECT [EGITmMX].[SICNO], 'TAHSIL' AS AlanAdi, [EGITmMX].[TAHSIL], [EGITmM].[TAHSIL] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[TAHSIL]<>[EGITmM].[TAHSIL] UNION ALL SELECT [EGITmMX].[SICNO], 'MEZUN' AS AlanAdi, [EGITmMX].[MEZUN], [EGITmM].[MEZUN] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[MEZUN]<>[EGITmM].[MEZUN] UNION ALL SELECT [EGITmMX].[SICNO], 'KURS3' AS AlanAdi, [EGITmMX].[KURS3], [EGITmM].[KURS3] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[KURS3]<>[EGITmM].[KURS3] UNION ALL SELECT [EGITmMX].[SICNO], 'EMEKLI' AS AlanAdi, [EGITmMX].[EMEKLI], [EGITmM].[EMEKLI] FROM [EGITmMX] INNER JOIN [EGITmM] ON [EGITmMX].[SICNO] = [EGITmM].[SICNO] WHERE [EGITmMX].[EMEKLI]<>[EGITmM].[EMEKLI])  AS degisenler;

strSQL = Replace(strSQL, ";", "")

strSQL = "SELECT degisenler1.* INTO tDegisenler FROM ('" & strSQL & "')  AS degisenler1;"

    

DoCmd.RunSQL strSQL  <--- burada hata veriyor

strSQL = "SELECT degisenler1.* INTO tDegisenler FROM (" & strSQL & ")  AS degisenler1"

INSERT INTO tDegisenler ( SICNO, AlanAdi, EGITmMX_ESCY34, EGITmM_ESCY34 )

SELECT degisenler.SICNO, degisenler.AlanAdi, degisenler.EGITmMX.ESCY34, degisenler.EGITmM.ESCY34

FROM degisenler;

strSQL = "INSERT INTO tDegisenler ( SICNO, AlanAdi, EGITmMX_ESCY34, EGITmM_ESCY34 )" & _

    " SELECT degisenler.SICNO, degisenler.AlanAdi, degisenler.EGITmMX.ESCY34, degisenler.EGITmM.ESCY34" & _

    " FROM (" & strSQL & ")  AS degisenler"