IINSERT syntax question

[FinalPrime]

Hello,

MSND provides this synax at their Office Developer Center

INSERT INTO target [(field1[, field2[, …]])] [IN externaldatabase] SELECT [source.]field1[, field2[, …] FROM tableexpression

They say

"externaldatabase" is the path "to" an external database (the target)
and tableexpression is from the local db currentDB()

I need the syntax whereby the 'IN' is CurrentDB and the "tableexpression" is a remote access db.

That is to say, Inserting FROM a Remote.db into the CurrentDB().

Thanks,
final-prime

 

[Remou]

How about:

INSERT INTO ThisTable (F1, F2)
SELECT F1,F2
FROM ThatTable
IN 'C:\docs\ThatDB.mdb'

http://lessthandot.com

 

[FinalPrime]

Do you mean
as an example:

dim dbr as Database
Set dbR = DBEngine(0).OpenDatabase(pathfile)

INSERT INTO ThisTable
SELECT ThatTable IN dbR

 

[Remou]

No, because that would not work. What is the problem with the example?

http://lessthandot.com

 

[FinalPrime]

When you say IN (C:\docs\ThatDB.mdb) I don't

understand how Access would 'know' the connection has been Set to dbR.

What you have here is a only a path & a file name.

 

[Remou]

I said
IN 'C:\docs\ThatDB.mdb'

And that is the syntax. Paste the example into the query design window and alter it to suit your set-up and you will see. Why shouldn't Access be able to open a file from its exact address?

http://lessthandot.com

 

[FinalPrime]

I get error message "Syntax Error in FROM Clause"

Function ri()
On Error GoTo x

Dim pathfile As String
Dim rxy As New Recordset
rxy.Open "tblpathFiles", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
pathfile = rxy!pathfiles
rxy.Close
Dim sql As String
Dim db As Database
Set db = CurrentDb()
sql = "INSERT INTO tblSubjectComps1 (StreetName ) SELECT StreetName FROM tblSubjectComps IN (" & pathfile & ")"
Debug.Print sql
db.Execute sql
db.Close
Exit Function
x:
MsgBox err.Description
End Function
''''''''''''''''''
INSERT INTO tblSubjectComps1 (StreetName ) SELECT StreetName FROM tblSubjectComps IN ("K:\Spatialest_Library\libArea11.mdb")

 

[Remou]

That is probably because you are missing the quotes:

sql = "INSERT INTO tblSubjectComps1 (StreetName ) SELECT StreetName FROM tblSubjectComps  IN ('"  & pathfile & "')"

You should end up with
IN 'C:\docs\ThatDB.mdb'

Note the single quotes.

http://lessthandot.com

 

[FinalPrime]

INSERT INTO tblSubjectComps1 (StreetName ) SELECT StreetName FROM tblSubjectComps IN 'K:\Spatialest_Library\libArea11.mdb'

tested this sql. It gave no run-time error but also does not do the insert. As you see, the single quotes were used.

 

[Remou]

There could be several reasons why the query would fail, for example, there may be problems with unique keys. If you ran the query in code and did not use dbFailOnError, the query will fail silently. Probably the easiest way to find the problem is to paste the SQL into the query design window in SQL view and run from there. I might mention that a similar query works for me.

http://lessthandot.com

 

[FinalPrime]

Upon debuging as you directed:

In the query you'll notice I am inserting only Streetname... Since both the input-output have primary indexes.....the Insert needs
BOTH the Street and primary key..................INSERT INTO tblsubjectcomps (UID,StreetName....

Thanks!

final prime