Updating one Recordset using another Recordset

[jmdl0518]

Hello all,



I new to this forum, so please forgive me if I leave out information that might be needed. I'll do better as I post more.


I have two tables that have the exact same fields. In table1 I have records that need to be UPDATED into table2. I tried an Update query and out of 600 records only half of those got updated. In my update query I joined tbl1 and tbl2 by Location (LOC) I checked for Nulls, Blanks, spaces, you name it. I can't figure why they all didn't get updated. I created a SQL query and go the exact same results. Ideally, I would like to create something in VBA to do this. I do not have any forms linked to these tables. In all I have about 600 records and 15 different fields that need to be updated. My example is only for one field.

sql example below. Which is the Access Sql in the Query
sql = "UPDATE tbl2 INNER JOIN tbl1 ON tbl2.LOC = tbl2.LOC" & _
"SET tbl2.Name = tbl1!Name" & _
"WHERE (((tbl2.Name) Is Null));"

Example.

Table1
Field1.Names = John

Table2
Field1.Names = "Need to UPDATE the name 'John' here"


If someone could possible give me a good starting point for a VBA function that would be great. I tried the Recordset .EDIT but I couldn't get it to work using two tables.

[Rawb]

The first thing I noticed was that on the SET line of your query, you use a dot on one side and a bang/exclamation on the other. Unless you're actually referencing an object outside the Query itself (for example, a value on a Form), you should always try and use a dot.

I also noticed that there are no spaces at the end/beginning of each line. This means that, once Access has put everything together, you end up with the following: UPDATE tbl2 INNER JOIN tbl1 ON tbl2.LOC = tbl2.LOCSET tbl2.Name = tbl1!NameWHERE (((tbl2.Name) Is Null));" (notice how tbl2.LOC and SET are combined into one word and how tbl1!Name and WHERE are in the same situation.

Try the following and see if it makes any difference.

sql = "UPDATE tbl2 INNER JOIN tbl1 ON tbl2.LOC = tbl2.LOC " & _
      "SET tbl2.Name = tbl1.Name " & _
      "WHERE (((tbl2.Name) Is Null));"

[ssanfu]

Also, "Name" is a reserved word in Access and shouldn't be used for object names - could be causing problems.

[jmdl0518]

Rawb,

Thank you for the response. Okay, I think I see what you mean. Even though I added the " & _ I Still need to space the Quotation(") out. I did not know that. Would adding a comma(,) be advisable as well or does that represent something else?


ssanfu,

Thank you for also responding. I will make the change in the tables and see if it helps.

I will update you all tomorrow on the hopeful resolution. I am off today and won't be back to the DB till then.

[Rawb]

In SQL, commas are used to separate specific columns in your query and in any ORDER BY clauses. They shouldn't be used there as they'll just generate an error. I'd stick with using spaces.