Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Posts
    17

    Unanswered: Using an External Database

    Hi Again,

    I am trying to insert records in to a MS Access Db with the following

    Qry = "INSERT INTO " & SourceTable(x) & " IN " & DbBackup _
    & " SELECT " & SourceTable(x) & ".* " _
    & "FROM " & SourceTable(x)
    MyDb.Execute (Qry)

    And it works fine.

    My problem is I only want to insert records in the Target Database Table where they exist in the Source Database Table but NOT the Target Database Table. So I modified my query as the following

    Qry = "INSERT INTO " & SourceTable(x) & " IN " & DbBackup _
    & " SELECT " & SourceTable(x) & ".* " _
    & "FROM " & SourceTable(x) & " WHERE isnull([Job No])"
    MyDb.Execute (Qry)

    Now obviously this does not work because the NULL test is being done on The Source Table and Not the Target.

    I have tried the following

    MyDb.Execute "SELECT " & SourceTable(x) & ".* INTO " & SourceTable(x) & " IN " & DbBackup _
    & " FROM " & SourceTable(x) _
    & " WHERE isnull([Job No]) IN (Backup.mdb);"

    but I get a syntax error.

    Can anyone help

    Many Thanks

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    I think the following is what you are trying to do:

    insert into customers in 'c:\program files\microsoft visual studio\vb98\nwind.mdb' select * from customers as c where 0 =( select count(*) from customers in 'c:\program files\microsoft visual studio\vb98\nwind.mdb' where customerid = c.customerid)

    Let me know if this works.

  3. #3
    Join Date
    May 2002
    Posts
    17
    m8 you truly are a guru. Many many thanks. This really had me stumped.

    I modified you code to read
    where ISNULL (ST.[Job No])

    and it worked, every time I ran the code but it kept adding the same records. when i put it back to

    where [Job No] = ST.[Job No]
    it worked correctly, just added any missing records.
    2 questions,

    1 why does this work and not isnull
    2 Where did you learn code like this from

    Many thanks again

Posting Permissions

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