PDA

View Full Version : Using an External Database

Smiley1
06-26-02, 05:18
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
rnealejr
06-30-02, 00:31
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.
Smiley1
06-30-02, 01:42
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