Run an Insert query into another database from code

[kbdci]

I want to run an Insert query to insert records from one database into a different database using VBA code in Access 2007. I have set each database using the names WorkingDB as the source database and ActiveDB as the target database. The SQL is something like this:

strSQL4 = "INSERT INTO content ( contentid, contentname ) IN " & ActiveDB & "SELECT content.contentid, content.contentname FROM content, tblLkpSaveUpdatedDate IN” WorkingDB & “WHERE [tblLkpSaveUpdatedDate]![TimeUpdated] < [content]![createDate]"

Unfortunately, this doesn’t work. When I try to compile the code I get an error on the “ActiveDB” that says it is a type mismatch.

Is it possible to run this kind of query from code? If so, how can I do it?

Thanks.

 

[Remou]

You want something on the lines of:

strSQL4 = "INSERT INTO content ( contentid, contentname ) " _
& "SELECT content.contentid, content.contentname " _
& "FROM content, tblLkpSaveUpdatedDate IN '" & workingdb _
& "' WHERE [tblLkpSaveUpdatedDate]![TimeUpdated] < [content]![createDate]"

Set db = CurrentDb  ''Activedb
db.Execute strSQL4, dbFailOnError

Though I suspect you want a join other than Cartesian, probably Inner Join.

http://lessthandot.com

 

[PHV]

you want a join other than Cartesian
The posted join is called a theta join.

 

[Remou]

Thanks, PHV

http://lessthandot.com