1

Topic: To compare two tables in ACCESS 2013 by means of VBA

Kind day!
I work with tables of a DB which got by right of succession.
It is necessary to compare table 1 (for example on value of a column 7) to table 2 (a column 7).
If the position misses in tab. 2 in table 1, for example in a column 8 to put down digit 777.
Earlier programmed in VBA EXCEL.
In VBA EXCEL it would look so:

Sub SEARCH ()
X1 = 2
X11 = 5984
X2 = 2
X22 = 6292
For XX1 = X1 To X11
Z1 = Sheets (1).Cells (XX1, 7)
FL = 0
For XX2 = X2 To X22
Z2 = Sheets (2).Cells (XX2, 7)
If Z1 = Z2 Then FL = 1
Next XX2
If FL <> 1 Then
Sheets (1).Cells (XX1, 7) =777
End If
Next XX1
End Sub

How it can look in ACCESS (there should be something similar - both there and there VBA)?
If that -  and boots I ask not to rush...)))

2

Re: To compare two tables in ACCESS 2013 by means of VBA

alex-access-on;
Created 2  and compare.
[spoiler] (DAO)

Private sub Sverka ()
const tbl1$ = "table1"
const tbl2$ = "table2"
const Prostav $ = "777": const stolb&=7
Dim rst1 as recordset, rst2 as recordset
Set rst1=CurrentDb. OpenRecordset ((tbl1, dbOpenDynaset) ' we will put down here in a column of 8 digits 777
if rst1.absoluteposition =-1 then exit sub
Set rst2=CurrentDb. OpenRecordset ((tbl2, dbOpenSnapshot)
if rst2.absoluteposition =-1 then exit sub
with rst1
.movefirst
do until.EOF
' we search for coincidence
rst2.findfirst "[Stolbec7] =" and.fields (6) ' if this number if it is the text  brackets
If rst2.nomatch then ' coincidence is not present
.edit
.fields (stolb) =Prostav
.update
end if
.movenext
Loop
end with
end sub

[/spoiler]

3

Re: To compare two tables in ACCESS 2013 by means of VBA

alex-access-on wrote:

It is necessary to compare table 1 (for example on value of a column 7) to table 2 (a column 7).
If the position misses in tab. 2 in table 1, for example in a column 8 to put down digit 777.

UPDATE [1]
LEFT JOIN [2] ON [1]. [7] = [2]. [7]
SET [1]. [8] = 777
WHERE [2]. [7] IS NULL
alecko wrote:

created 2  and compare

Aha... And still it is possible to delete tonsils through a bum...

4

Re: To compare two tables in ACCESS 2013 by means of VBA

Thanks for answers (helped all)