Access VBA to compare HUGE tables

[GloverK]

Im a new boy on the block and am looking for a bit of help....
Ive got this bit of code below which I have cobbled together from bits and pieces but I need to make it quicker....that table is about 700,000 rows by 14 columns and I need to reconcile the lot!!!

Private Sub Command0_Click()
Dim sql As String, fieldname As String
Dim column As Integer

column = 1
Do Until column = 15
fieldname = "Field" & column
sql = "SELECT DISTINCTROW table1.Id INTO " & fieldname & " FROM table1 LEFT JOIN table2 ON table1." & fieldname & " = table2." & fieldname & " WHERE (((table2." & fieldname & &quot Is Null));"
DoCmd.RunSQL (sql)
column = column + 1
Loop

End Sub

Pls help.

 

[mflancour]

What do you want to do with the null fields once it returns them to you?

 

[GloverK]

Thanks for replying mflancour.

I take it null fields are blank (told you I was new at this)? If so I dont want them at all if the data in the other table (same row/field position) is also blank but I dont know how to miss them out.

I should have mentioned in orig code that I am comparing 2 tables of the 700k size. Cant get it to deal with columns full of the same data (i.e. column full of 1's). Biggest problem is running the sql, takes forever.

Is there an easier way to compare tables of this size....?

 

[MichaelRed]

Any 'stored' query will be somewhat quicker than the code thing, so at least a BIT of speed could be gained by making the seperate queries for each field. You could, of course, call the queries in a loop in code, but sinec this 'looks to be' a one shot deal, I would think it un-necessary to do the code.

Further, It looks like you are appending new rows into a new table for each of these occurances, while your explination appears to say you merely want to update the null fields in the dest with the Not null fields in the source. IF this is the direction, perhaps a set of (layered) queries will be faster, but this would depend on the relative "nullness" of the fields in the tables. To do this, I would select the records in the source table where the specific field is NOT null and the specific field in the dest tble ARE null, and use the first as te source to update the second, of course joining the src and dest on different fields which are unique to each table and thus "Match" the records. ADditional details require more info than you have provided.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over