How to read from db1 and write to db2 with one sql query?

[amorosik]

Have you tried the variant in #17? It works for me - but I could only test with 2 SQL servers.
CurrentDB and its DBEngine are only responsible for execution and data transfer if you use 2 external ODBC sources in the SQL statement.

Yes, I tried with two different Sqlite db
And it works only if at least one of the tables of the two databases is linked to the Access procedure code
If no table is linked, it doesn't work, it returns error 3000
The instruction usede is:

Currentdb.execute "INSERT INTO [ODBC;Driver={SQLite3 ODBC Driver};Dsn=Erp2_Sqlite;Database=d:\erp2_sqlite.db].ARTICOLI (id_articoli, codice_interno, descrizione_breve) select id_articoli, codice_interno, descrizione_breve FROM [ODBC;Driver={SQLite3 ODBC Driver};Dsn=Erp2_Sqlite_bis;Database=d:\erp2_sqlite_bis.db].ARTICOLI"

 

[Josef P.]

Now tested with SQLite - this works for me:

Dim SqlText As String

Const CopyToOdbcSource = "ODBC;DSN=SQLite-T2;Database=H:\SQLite\Test\test2.db"
Const CopyFromOdbcSource = "ODBC;DSN=SQLite-T1;Database=H:\SQLite\Test\test.db"


SqlText = "INSERT INTO [" & CopyToOdbcSource & "].TabB  (id, T)" & vbNewLine & _
         " select id, T FROM [" & CopyFromOdbcSource & "].TabA"

CurrentDb.Execute SqlText, dbFailOnError

 

[amorosik]

Without pre-link table from test.db and test2.db?
I ask because I tried as you say (with other dsn name e db file), but after doing a compacting program Access and trying again without any linked table, it returned error 3000
Access 2013 32bit, driver odbc 0.9998

 

[Josef P.]

I ran the code in an empty accdb that never had a linked table.

Used:
* Access 365 (Ver 2305) 32 bit
* SQLite3 ODBC driver 0.9998 32 bit

 

[amorosik]

I don't know what to tell you, it doesn't work for me
And as you can see on the attached video, I don't think I forgot anything
The fact is that if I connect any table of each of the two db then it works correctly