Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2005
    Posts
    12

    Unanswered: How to Collete a database?

    How to Collete a database? What is the code?


    Example:

    I had have two database, and the two database have two table which are similiar same but the data are some different. It mean, the two table should have the same data, but there are miss another one. How to make the data same to each other in the two table?


    Thanks.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If I understood you well, the solution might be very simple or impossible.

    Simple solution: you KNOW which of two tabes contains correct data. Delete all "wrong" data from the table and insert "correct" data into it.

    (Almost) impossible solution: correct data can be found in both tables. Do you know which of them are correct and which are wrong? If so, you could create third table and insert only correct data from both tables into it; delete all data from original tables; insert data from third table into original ones.

    Impossible solution: if there's no obvious WHERE clause which distincts correct records (or even columns?) from wrong ones, you're in deep trouble.

  3. #3
    Join Date
    Dec 2005
    Posts
    12
    no... i think you misundestand my meaning

    example:
    table1 have 1,2,4,9...
    table2 have 1,3,5,7,8,.....

    and i want be like this:
    table1 have 1,2,3,4,5,7,8,9...
    table2 have 1,2,3,4,5,7,8,9....

    so, how???????????

    thanks.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I see ... syntax might be like this (it may differ regarding your DB engine):

    CREATE TABLE temporary_table AS SELECT * FROM first_table;

    INSERT INTO first_table SELECT * FROM second_table;

    INSERT INTO second_table SELECT * FROM temporary_table;

    DROP temporary_table;

  5. #5
    Join Date
    Dec 2005
    Posts
    12
    i not understand....
    i am beginner...
    Please explain more... or give me the code more complete

    thanks.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What is your DB engine? When talking about "two databases" - are they really databases or are they schemas in the same database?

    If those are different databases, you'll have to create some kind of a database link between them to be able to transfer data.
    If we're talking about schemas in the same database, simple privilege grant would be enough.

    Code I wrote IS the complete code; I don't know what to add. Perhaps just to say it in English?
    - first create a temporary table to store data from the first table
    - take all records from the second table and insert them into the first table. Now the first table contains all data you need.
    - take all records from temporary table (actually, the original first table) and insert them into the second table. Now the second table contains all the records too.
    - drop temporary table.

  7. #7
    Join Date
    Dec 2005
    Posts
    12
    I store one of the database in pendrive(in mdb format) and the other one in ms sql server.


    However, I still do not know how to create a temporary table. But I also have a doubt, if "I take all records from the second table and insert them into the first table. Now the first table contains all data you need.", then the first table will have the repeating record..... I do not want the repeating record.


    Thanks.

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Oh, of course you'll have repeating records! I missed that "1" Sorry!

    OK then; here's another attempt: ("temporary table" is, in this case, created as selection of all record from some table). UNION will create distinct records from both tables:

    CREATE temporary_table AS
    SELECT * FROM first_table
    UNION
    SELECT * FROM second_table;

    DELETE FROM first_table;
    DELETE FROM second_table;

    INSERT INTO first_table SELECT * FROM temporary_table;
    INSERT INTO second_table SELECT * FROM temporary_table;

    DROP temporary_table;


    EDIT: This might be OK, I guess ... however, I've never heard of "pendrive(in mdb format)" you're talking about. Keep that in mind reading my code - it might not be usable on "pendrive" (whatever it is).
    Last edited by Littlefoot; 12-08-05 at 09:46.

  9. #9
    Join Date
    Dec 2005
    Posts
    12
    Thank you very much.

    After your teaching, I just realize have "union" thning. Pendrive (hardware) is a something to store file, same as external hardisk. However, I will try to do what you teach me.

    Thank again.

  10. #10
    Join Date
    Dec 2005
    Posts
    12
    However, I have try but got many mistake, cause i am beginner, and not quick understanding sql.
    below is my code:


    Code:
    Dim con As New ADODB.Connection
    Dim objRS As New ADODB.Recordset
    Dim conC As New ADODB.Connection
    Dim objRSC As New ADODB.Recordset
    
    
    Const lsDSN = "Provider=sqloledb;" & _
            "Network Library=DBMSSOCN;" & _
            "Data Source=xxx.xxx.68.xxx,1433;" & _
            "Initial Catalog=abc;" & _
            "User ID=sa;" & _
            "Password=axxxxxx"
    
    Const lsDSNC = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=F:\MyLife.dat;" & _
               "Jet OLEDB:Database Password=xxxxxx"
    
    
    
    con.Open lsDSN
    SQL = "SELECT * FROM History order by Dtime asc"
    objRS.Open SQL, con, , 3
    
    conC.Open lsDSNC
    SQLC = "SELECT * FROM History order by Dtime asc"
    objRSC.Open SQLC, conC, , 3
    
    Create temporary_table
    SQL
    Union
    SQLC
    
    DELETE FROM SQL;
    DELETE FROM SQLC;
    
    INSERT INTO SQL SELECT * FROM temporary_table;
    INSERT INTO SQLC SELECT * FROM temporary_table;
    
    DROP temporary_table;
    
    objRS.Close
    Set objRS = Nothing
    con.Close
    Set con = Nothing
    
    objRSC.Close
    Set objRSC = Nothing
    conC.Close
    Set conC = Nothing

    i using vb6 to create a *.exe. How to fix it?

    Thanks.

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Unfortunately, I'm a tabula rasa regarding Visual Basic ... I'll be glad to help you further with SQL part of the solution, but let's hope someone else will know how to help you with this part of the problem.

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    have a look at the COALESCE function.

    SELECT COALESCE(t1.yourField, t2.yourField) as finalField
    FROM t1 INNER JOIN t2 On t1.key = t2.key
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •