Merging Tables

[cstimart]

Suppose I have two tables. tblOriginal and tblNew. Both have the same fields, except that tblOriginal has additional fields.

Is it possible to....
1. "merge" tblNew into tblOriginal? Any/All new records to be appended to the tblOriginal
2. and for records that are in both, only tblOriginal Fields that are different in tblNew (if any)

Hopefully that makes sense.

 

[Joe4]

Take a look at Action Queries in Access Help.

You can use Append Queries to add new records to an existing table and
you can use Update Queries to update specified fields in existing records.

 

[cstimart]

Take a look at Action Queries in Access Help.

The section "Join tables and queries"?

 

[Joe4]

The section "Join tables and queries"?

No, you want to look at Action Queries.
Select queries just return data. Action queries update data.

Any intro Access book should have information on how to do this. You probably Google some tutorials on this also, if you are unfamiliar with these type of queries.

 

[cstimart]

Ok, after some research, I have created another DB to test. Two Tables [tblImported_Data] and [tblSaved_Data]. [tblImported_Data] as 10 fields and [tblSaved_Data] has the same 10 (in the same order), in addition to a few more.

I created an Unmatched Query to weed out the Records in [tblImported_Data] that are not in [tblSaved_Data], based on the Field [Loan #]....no problem!

However, when I change to Append Query Type and select [tblSaved_Data] as the Table to append to and Run the query, I get the error that says "Duplicate output destination '[Loan #]'"

I've tried renaming the Fields (with no luck), I've verified that the unmatched query does it's job and there are no records that match.

I also tried not selecting the 10 fields and using *. When I run the append I get "The INSERT INTO statement contains the following unknown field name: '[Loan #]'."

Any ideas?

 

[cstimart]

I figured it out by reviewing the SQL. I needed to remove a couple references to [Loan #] that the code had duplicated.

 

[cstimart]

Here's a portion of my code, where I am using ADO to (try) to delete records from a table...

sSQL(1) = "DELETE Exists (SELECT 
[*] FROM [Stuff2Delete] WHERE [Stuff2Delete].[Loan #] = [tblImported_Data].[Loan #]), 
[*] FROM [tblImported_Data]"
sSQL(2) = "WHERE (((Exists (SELECT 
[*] FROM [Stuff2Delete] WHERE [Stuff2Delete].[Loan #] = [tblImported_Data].[Loan #]))<>False));"

    Set cnn = New ADODB.Connection
    With cnn
        .Provider = IIf(Right(sDB_Path, 3) = "mdb", "Microsoft.Jet.OLEDB.4.0", "Microsoft.ACE.OLEDB.12.0")
        .Open sDB_Path
    End With
    cnn.Execute sSQL(1) & sSQL(2)
    cnn.Close
    Set cnn = Nothing

The section in red crashes and indicates "No value given for one or more parameters. However, the SQL Code as is will work directly in Access.

 

[Joe4]

I usually use DAO instead of ADO, but try putting a space between sSQL(1) and sSQL(2), i.e.

sSQL(1) & " " & sSQL(2)

 

[cstimart]

I usually use DAO instead of ADO, but try putting a space between sSQL(1) and sSQL(2), i.e.

sSQL(1) & " " & sSQL(2)

Thanks Joe, but when I copied/pasted the code back into Access and re-ran, the SQL changed.

DELETE Exists (SELECT * FROM [Stuff2Delete] 
WHERE [Stuff2Delete].[Loan #] = [tblImported_Data].[Loan #]) AS [Expr1], * 
FROM [tblImported_Data] 
WHERE (((Exists (SELECT * FROM [Stuff2Delete] 
WHERE [Stuff2Delete].[Loan #] = [tblImported_Data].[Loan #]))<>False));

 

[Joe4]

Thanks Joe, but when I copied/pasted the code back into Access Where did you change it?

I meant this line:

    cnn.Execute sSQL(1) & " " & sSQL(2)

Alternatively, you could just add a space to the end of your sSQL line so the end of the first line doesn't blend into the start of the second one:

sSQL(1) = "DELETE Exists (SELECT 
[*] FROM [Stuff2Delete] WHERE [Stuff2Delete].[Loan #] = [tblImported_Data].[Loan #]), 
[*] FROM [tblImported_Data] "

One thing that may be complicating matters here. Have you defined sSQL(1) and sSQL(2) as array variables? That is usually what numbers in parentheses mean in VBA. I am not sure why you would do this. If this is intentional, it is not a technique I am familiar with. I would recommend naming them sSQL1 and sSQL2.