Solved: Insert Into Problem

[gibbo1715]

Hi All

I have 2 databases one contains a table called Books, the second also contains a table called books but also has a few extra fields, both have an auto number field and database 2 table books is only populated with new records from database 1 table books, what i need to do is add any new records from DB1 into my table in DB2

I ve tried the following but it doesnt work, any ideas?

thanks

Gibbo

strString = "INSERT INTO Books Select * FROM " & _
"[MS Access;DATABASE=" & mdifrm1.CommonDialog1.FileName & ";PWD=Test].[Books]"
con.Execute strString

[OBP]

Why not import the whole table and then update the table with an update query. As long as you have "No Duplicates" set you should not have a probem.

[gibbo1715]

I cant import the whole table as i would end up with duplicates

e.g. i import from table 1 records 1 - 10 in to table 2, then at some point records 11-20 get added to table 1 so I now want to either
1. import records 11 -20 or
2. (Preferable) import all records but replace the values currently in records 1 - 10 in table 2 without affecting any data in my added columns in table 2

I ve also tried the following so can someone explain to me why this works

Call CheckConnection
strString = "INSERT INTO Books(Title) Select (Title) FROM " & _
"[MS Access;DATABASE=" & mdifrm1.CommonDialog1.FileName & ";PWD=Test].[Books]"
con.Execute strString

but this doesnt

Call CheckConnection
strString = "INSERT INTO Books(Title, Author) Select (Title, Author) FROM " & _
"[MS Access;DATABASE=" & mdifrm1.CommonDialog1.FileName & ";PWD=Test].[Books]"
con.Execute strString

thanks for looking

Gibbo

[gibbo1715]

Ok if i remove the () after the select it updates but I cant include the auto number field cause if I do it doesnt work, any ideas

thanks

Gibbo

[OBP]

Of course you can't include the Autonumber field, that has to be incremented by Access.
I do not understand why you can't set the tables fields as "No Duplicates" and then run an append query, to add the new records and an Update query to update the existing records.

[gibbo1715]

Sorry to be a pain here but can you give ma a rough example please as im not reallyt getting anwhere

Much Appreciated

Gibbo

[gibbo1715]

Ok got my insert working but need to figure out the last Where part, can anyone help please

Thanks

Gibbo



Call CheckConnection
strString = "INSERT INTO Books(Title, Author) Select Title, Author FROM " & _
"[MS Access;DATABASE="c:\test.mdb";PWD=Test].[Books] " & _
??????? Where Book_id <> [MS Access;DATABASE="c:\test.mdb";PWD=Test].[Books]Book_id"
con.Execute strString

[OBP]

If the insert is working, which part of it isn't working?
Any chance of posting a zipped copy of the databases on here?

[lucky245]

As I'm only a novice maybe I'm missing the point but assuming that there are unique fields in your rows of data why not import table 1 and write a piece of vba to search through table 2 and where the unique value is not found add it to table 1. (or visa versa) Should take all of 5 minutes to write.

Sorry if I'm missing the point:doh:

[OBP]

lucky, my original point was that if the tables are correctly designed with "no Duplicate" fields you do not even have to resort to VBA to do the sorting, a transfer table query, an append and update query run when the 2nd table is opened is all that should be necessary.

[lucky245]

I totally agree but without seeing the design my suggestion was a quick fix, not I agree a solution for the initial problem or a disagreement with your solution which I may add would have been my initial suggestion.
:yes

[gibbo1715]

Thankyou all for the replys, I cant post the database im afraid, My destination table will never have new records put in it( I just add a couple of columns where i input yes or no) and my source table will never have duplicates so OBP's method would be great but I dont have enough SQL knowledge and need an example of what my SQL string might look like if possible to get me going, My second question was just me trying to only get new records but i get stuck at the

Where Book_id <> [MS Access;DATABASE="c:\test.mdb";PWD=Test].[Books]Book_id"

Bit and was wondering how to do that as a way of just importing new records (Sorry if im causing confusion)

Thankyou for your patience

Gibbo

[OBP]

You do not need any SQL knowledge to do it the way that I have suggested.
You can get the Query Wizard to create the Append and Update queries for you and just run the queries. If you can tell us the names of the tables and then names of the fields in the tables we may be able to do it for you.
You can always post a "blank" copy of the databases on here as the data is immaterial, it is the table structure that is important.

[gibbo1715]

Thanks for the reply, the only reason i cant post a blank version is that I cant post anything from work but if ok i ll give you an example as below


books.mdb (Source Database)
books (Table)
Fields Book_ID (Auto_No)
Title (Text)
Author (Text)

Reviewed.mdb (Destination Database)
books (Table)
Fields Book_ID (Auto_No)
Title (Text)
Author (Text)
+
Reviewed (text)
Add to List (Text)


Thanks

Gibbo

[OBP]

I am about to have a look at this for you when it suddenly struck me, have you a particular reason for having the data in seperate databases, rather than in the same database or even the same table?

[gibbo1715]

I do have a good reason and the only way to do what I need is to have two seperate databases, one needs to be portable

[OBP]

OK, I have set up 2 databases with tables as described and written some VBA to import the table from the first in to the second database, it calls the imported table "temp", I have created an Append query that appends the records to the second database table and then delete the "temp" table.
What updating did you want to take place at this point?

[gibbo1715]

I think that is it,

It just needs to update any existing records in Reviewed.mdb that have been changed in books.mdb without affecting my Reviewed (text) and Add to List (Text) and then add any further records from table 1 into table 2

so if it does that your a life saver

thanks

Gibbo

[OBP]

Gibbo, are the records in the Book1 database in the same order as the records in the book2 database?
Is it possible to email me a copy of the database later?

[OBP]

Gibbo, I have established that you must have a unique (No Duplicates) field in your tables to make this work.
How many records are there in your two tables?

[gibbo1715]

there will never be more that 1000 records and the PK field is an auto number - there will be no duplicates

gibbo

[OBP]

I can't find a way to do what you want without a unique field with no duplicates. If the Book Title could meet this criteria then you could use that.
Other than I would add a unique field and then use what I have written.

[gibbo1715]

i Have an auto number that is unique but the book title will also be unique i guess so that will do what you suggest

gibbo

[OBP]

Any luck yet? Would you like me to post what I have so that you can see it working?

[OBP]

Here it is anyway. If you check the two tables in the databases you will see that book1 has 4 records and book2 has 1 record.
There is an Import form in books2 with a Command button that imports the table from book1 in to books2 and then runs an update query to change the existing record, an append query to add the extra records and then it deletes the temp table.
Hopefully you can make this work for you.:)

[gibbo1715]

I will look after work

Thankyou very much for your assistance with this it is very appreciated

Gibbo

[gibbo1715]

That did what i was after

thankyou for putting in so much effort for me

Gibbo

[OBP]

My pleasure.