How to read from db1 and write to db2 with one sql query? (1 Viewer)

[amorosik]

I have an Access procedure with some tables, including 'articoli', connected via odbc to a Firebird db
The same procedure has some tables, including 'articoli2', connected via odbc to a Microsoft Sql Server db
The structure of the two tables is identical
I want to copy all the information contained on some lines of 'articoli' in the table 'articoli2'
Do you know how this can be done, from vba code, with a single Sql command?

 

[GPGeorge]

We could assume from the setup to this question that both tables are linked in the same Access accdb.
If that's the case, a straightforward Append or Update query would do the job, albeit potentially a slow performer.

You could execute that query from VBA, using the CurrentDB.Execute method.

However, the fact that this is a question here suggests there are more details to be revealed that might complicate things.

 

[CJ_London]

I’ll bite - why use vba?

 

[The_Doc_Man]

I don't know if you can get this to work with Firebird BUT if everything is ODBC-connected, the syntax you want involves the "IN" clause. Access specifically supports this syntax.

IN clause (Microsoft Access SQL)

Office VBA reference topic

learn.microsoft.com

You can specify the location of a data source OR a data destination using IN as shown in their examples.

 

[June7]

Example from my db using two Access files:

Public Sub ConstructionExtract()
'exports data to ConstructionExtract Access file
'copies file to zip folder
'opens Outlook and attaches file to msg and sends

Dim strZip As String
Dim strExtract As String

strZip = gstrBasePath & "Editing\ConstructionExtract.zip"
strExtract = gstrBasePath & "Editing\ConstructionExtract.accdb"

'delete records from ConstructionExtract tables
CurrentDb.Execute "DELETE FROM Bituminous IN '" & strExtract & "'"
CurrentDb.Execute "DELETE FROM BituminousMD IN '" & strExtract & "'"
CurrentDb.Execute "DELETE FROM Concrete IN '" & strExtract & "'"
CurrentDb.Execute "DELETE FROM Emulsion IN '" & strExtract & "'"
CurrentDb.Execute "DELETE FROM PGAsphalt IN '" & strExtract & "'"
CurrentDb.Execute "DELETE FROM SoilsAgg IN '" & strExtract & "'"
CurrentDb.Execute "DELETE FROM SampleInfo IN '" & strExtract & "'"
'insert records into ConstructionExtract tables
CurrentDb.Execute "INSERT INTO Bituminous IN '" & strExtract & "' SELECT * FROM ConstructionBIT;"
CurrentDb.Execute "INSERT INTO BituminousMD IN '" & strExtract & "' SELECT * FROM ConstructionBMD;"
CurrentDb.Execute "INSERT INTO Concrete IN '" & strExtract & "' SELECT * FROM ConstructionCONC;"
CurrentDb.Execute "INSERT INTO Emulsion IN '" & strExtract & "' SELECT * FROM ConstructionEMUL;"
CurrentDb.Execute "INSERT INTO PGAsphalt IN '" & strExtract & "' SELECT * FROM ConstructionPG;"
CurrentDb.Execute "INSERT INTO SoilsAgg IN '" & strExtract & "' SELECT * FROM ConstructionSA;"
CurrentDb.Execute "INSERT INTO SampleInfo IN '" & strExtract & "' SELECT * FROM ConstructionSampleInfo;"

'create empty zip folder
'found this on web, no idea what the Print line does but if it isn't there, this won't work
Open strZip For Output As #1
Print #1, "PK" & Chr$(5) & Chr$(6) & String(18, 0)
Close #1
'copy file into zip folder
Dim objApp As Object
Set objApp = CreateObject("Shell.Application")
'variable for source file doesn't seem to work in this line
'also double parens not in original example code but won't work without
objApp.NameSpace((strZip)).CopyHere gstrBasePath & "Editing\ConstructionExtract.accdb"

'open Outlook, attach zip folder, send e-mail
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
    .BodyFormat = olFormatRichText
    .To = "june7ak@live.com"
    ''.cc = ""
    ''.bcc = ""
    .Subject = "Central Materials Laboratory Data"
    .HTMLBody = "Construction data extract: " & Now
    .Attachments.add (strZip)
    .DeleteAfterSubmit = True 'to not retain in sent folder
    .Display
    ''.Send
End With

'delete zip folder
Kill strZip

CurrentDb.Execute "UPDATE Updates SET ConstructionExtract=#" & Date & "#"

End Sub

 

[ebs17]

INSERT INTO TableX ([FieldList]) SELECT [FieldList] FROM [connection data].TableX

INSERT INTO [connection data].TableX ([FieldList]) SELECT [FieldList] FROM TableX

Jet can handle external databases. So you use a DAO reference or an ADODB connection to one database and store the reference to the other database in the connection data.

Here is an example of a make-table query from an Access table to a SQL Server:

SELECT *
INTO [ODBC;Driver={ODBC Driver 17 for SQL Server};Server=xxx;Database=xxx;UID=xxx;PWD=xxxxxxxxxxxxx;].newtable
FROM Tabelle

Firebird connection strings - ConnectionStrings.com

Connection strings for Firebird. Connect using Firebird .NET Provider, IBPhoenix ODBC and ODBC .NET Provider.

www.connectionstrings.com

​

 

[amorosik]

I'm trying, with no luck, with:

INSERT INTO [ODBC;Driver={SQLite3 ODBC Driver};Database=d:\erp2_sqlite.db].ARTICOLI (id_articoli, codice_interno, descrizione_breve) select id_articoli, codice_interno, descrizione_breve FROM ARTICOLI

Error 3000

Odbc driver Sqlite is present (ver 0.9998)
On main db, ARTICOLI table is present, with field id_articoli, codice_intermo, descrizione_breve and some data rows
File d:\erp2_sqlite.db is present, and containa a tabler ARTICOLI with field id_articoli, codice_intermo, descrizione_breve

 

[ebs17]

My answer was related to the content of the topic title, just for clarification.

I have an Access procedure with some tables, including 'articoli', connected via odbc to a Firebird db
The same procedure has some tables, including 'articoli2', connected via odbc to a Microsoft Sql Server db

As @GPGeorge correctly said: Linked tables can be processed in Access like internal tables, i.e. with a very simple query. You've probably tried it: with what result?

Otherwise: With what relevance do you test on SQLite? I'm now counting three (3!) databases with my tired fingers, but the fingers of both hands may not be enough for your fluent task presentation.

Also: Don't conclude from your own confusion that the assembled forum is said to be incompetent.

 

[amorosik]

Yes, if I use a linked table everything works fine
If the external db was a Sqlite, and the table is ARTICOLI, when I connect it, it becomes ARTICOLI1 (because ARTICOLI already exists) and if I use the query from vba code:

INSERT INTO ARTICOLI1 (id_articoli, codice_interno, descrizione_breve) select id_articoli, codice_interno, descrizione_breve from ARTICOLI

the writing of the records on the destination table is successful
Since that example works correctly, I wanted to understand if it is possible to obtain the same result even without using the linked table and therefore indicating the connection string in order to identify the destination db
But [ODBC;Driver={SQLite3 ODBC Driver};Database=d:\erp2_sqlite.db].ARTICOLI is not correct

The exact command that i try is:
Currentdb.execute "INSERT INTO [ODBC;Driver={SQLite3 ODBC Driver};Database=d:\erp2_sqlite.db].ARTICOLI (id_articoli, codice_interno, descrizione_breve) select id_articoli, codice_interno, descrizione_breve FROM ARTICOLI"

There are two db's, Firebird as the main db and Sqlite as the external db
I wrote the initial post indicating Sql Server because I was thinking of using that, then in reality I'm doing the tests with Sqlite simply because on this I already have a db available with the same structure as the main db , while on Sql Server I should have also created the db structure
Since they are both accessible via odbc, the logic used to access them should be the same
I probably don't use the correct syntax in the part where the destination db is indicated

 

[amorosik]

With [ODBC;Driver={SQLite3 ODBC Driver};Dsn=Erp2_Sqlite;Database=d:\erp2_sqlite.db] seem to be ok
Then the complete command 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 ARTICOLI"

Obviously the dsn Erp2_Sqlite must exist

 

[ebs17]

Currentdb.execute ... has little or nothing to do with two external databases.

Incidentally, you would be able to see the effect of the IntelliSense in a real code.

 

[amorosik]

Currentdb.execute ... has little or nothing to do with two external databases.

Incidentally, you would be able to see the effect of the IntelliSense in a real code.

I don't understand what you mean exactly
Do you mean that with 'Currentdb.execute' the above line shouldn't work?

 

[June7]

Did you try it? CurrentDb refers to database code is executed in. It will expect to find table (local or linked) ARTICOLI in that database to pull data from for export to SQLite table ARTICOLI.

What happened to name ARTICOLI2?

 

[The_Doc_Man]

If one of the two tables in question is defined in whatever is the CurrentDB, then it most certainly SHOULD work. If both of the DBs are located externally, CurrentDB STILL refers to the database defined in Workspaces(0) and still relies on the resources defined for that file. In the "double external" case, CurrentDB probably is just a reference to a front-end - but it is a front-end whose resources will be put to the test.

 

[amorosik]

Did you try it? CurrentDb refers to database code is executed in. It will expect to find table (local or linked) ARTICOLI in that database to pull data from for export to SQLite table ARTICOLI.

What happened to name ARTICOLI2?

Yes, before submitting post #10 i tried it and it works perfectly
Obviuosly i checked with a db manager (Sqlite-gui) that the Sqlite db is indeed filled with the expected rows
In the real case, the contents of about twenty tables on Sqlite are first deleted and then the rows for filling are launched and all oh them work correctly, in total about 200k rows

 

[amorosik]

No, that's not correct
The line at post #10, both with external destination and with external source and destination, work correctly but only if the Access procedure has at least one table in the external db connected (linked)
And this makes the functionality of the code in post #10 useless
Because if we have to connect at least one table of external db, then i migth as well connect them all

 

[Josef P.]

Doesn't the SQL statement work with 2 odbc sources?

CopyToOdbcSource = "[ODBC;..."
CopyFromOdbcSource = "[ODBC;..."

SqlText = "INSERT INTO [" & CopyToOdbcSource & "].ARTICOLI  (id_articoli, codice_interno, descrizione_breve)" & _
         " select id_articoli, codice_interno, descrizione_breve FROM [" & CopyFromOdbcSource & "].ARTICOLI"

Currentdb.execute SqlText, dbfailonerror

 

[amorosik]

Doesn't the SQL statement work with 2 odbc sources?

CopyToOdbcSource = "[ODBC;..."
CopyFromOdbcSource = "[ODBC;..."

SqlText = "INSERT INTO [" & CopyToOdbcSource & "].ARTICOLI  (id_articoli, codice_interno, descrizione_breve)" & _
         " select id_articoli, codice_interno, descrizione_breve FROM [" & CopyFromOdbcSource & "].ARTICOLI"

Currentdb.execute SqlText, dbfailonerror

Yes, but only if there is at least one table linked of db1 and at least one table of db2
But if you read post #14 by The_Doc_Man maybe the problem is in the fact that CurrentDb.execute is linked to Workspace(0)
Perhaps by defining multiple workspaces it is also possible to obtain reading and writing on external db without connecting the tables

 

[ebs17]

So you use a DAO reference or an ADODB connection to one database and ...

Deciphering the meaning of half a sentence (when it isn't even ciphered) isn't that easy ...

By definition, CurrentDb is not an external database, but the one that runs the code.
Running code, linking to one database, linking to another database => that's THREE things. (The fingers of one hand are still enough to calculate this.)

 

[Josef P.]

Yes, but only if there is at least one table linked of db1 and at least one table of db2

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.