Insert into Select with two different databases

Question

0.00/5 (No votes)

See more:

Hi everyone.

I am attempting to archive data from one database into another database.

It has taken me hours to put together this piece of code - it does not work and I cannot figure out why.

VB

Dim DB_FROM As String = GlobalVariables.DBPath & GlobalVariables.DBName
Dim DB_TO As String = GlobalVariables.ArchiveDBPath & GlobalVariables.ArchiveDBName

Dim cnFrom As New OleDb.OleDbConnection
Dim cnTo As New OleDb.OleDbConnection
Dim strSQL As String

cnFrom.ConnectionString = "Provider=" & cp & " Data Source=" & GlobalVariables.DBPath & GlobalVariables.DBName & ";Jet OLEDB:Database Password=xxx;"
cnFrom.Open() 'Open the connection

cnTo.ConnectionString = "Provider=" & cp & " Data Source=" & GlobalVariables.ArchiveDBPath & GlobalVariables.ArchiveDBName & ";Jet OLEDB:Database Password=xxx;"
cnTo.Open() 'Open the connection

strSQL = "INSERT INTO Purchase_Order SELECT * FROM Purchase_Order IN '" & DB_FROM & "' WHERE Po_Number = '" & PoNumber & "'"

da = New OleDb.OleDbDataAdapter(strSQL, cnTo)

cnFrom.Close()
cnTo.Close()

I am not sure if the problem lays in the SQL statement

SQL

INSERT INTO Purchase_Order SELECT * FROM Purchase_Order IN 'C:\Users\Darrell\Databases\DB-TEST\TEST_Commercial_DB.accdb' WHERE Po_Number = 'CZ13-01-001 / 275WK'

or in

VB

da = New OleDb.OleDbDataAdapter(strSQL, cnTo)

Any assistance here would be hugely appreciated

Darrell

Posted 9-Jul-13 0:47am

Darrell de Wet

Updated 21-Jan-20 9:32am

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Amol_B · Answer 1 · 2013-07-09T02:21:00

Hi,

i think you are doing something different. Your query will just insert record of same table into itself (duplication!) Since

da = New OleDb.OleDbDataAdapter(strSQL, cnTo)

can use only one connection at a time.

if this was sql ,i have used database name in front of table name to achive this.like

SQL

INSERT INTO [DBNAME1].[tablename] SELECT * FROM [DBNAME2].[tablename]

Maciej Los · Answer 2 · 2013-07-09T02:50:00

I'm not sure you can use IN clause[^] to fetch records from different database (MS Access file) using VB.NET... but try you need to be completely sure that both tables have the same data structure.

Instead your query, use:

SQL

INSERT INTO TableName (Field1, Field2, Field3, ..., FieldN)
SELECT Field1, Field2, Field3, ..., FieldN
FROM TableName IN 'FullPath'

More about:
IN clause (MS Access SQL)[^]
Accessing external data using the IN clause[^]

Give me a sign if it works for you ...

Another idea is to link table from source database with different name, for example: SrcTableName. Then try to insert data using:

SQL

INSERT INTO TableName (FiedlsCollection)
SELECT FieldsCollection
FROM SrcTablename

Here is an idea: How to programatically refresh linked tables through VB.net?[^], but not fully implemented ;(
Re-linking MS Access tables with VB.NET 2010[^] ;)
http://www.microsoftaccessexpert.com/Microsoft-Access-Code-LinkTable.aspx[^] - vba

More:
Link tables in an Access project by using the Link Table Wizard (ADP)[^]
About importing and linking data and database objects[^]
How to use Microsoft Visual Basic .NET to connect to a Microsoft Access database and to retrieve data[^]

Jeffpete · Answer 3 · 2020-01-21T09:33:00

This works for me ...

Public Sub CopyTableToLogDB(TableName As String, CON1 As ADODB.Connection, CON2 As ADODB.Connection)
Dim RSIn As New ADODB.Recordset
Dim RSOut As New ADODB.Recordset
Dim SQL As String
Dim i1 As Integer

SQL = "Select * From " & TableName
RSIn.Open SQL, CON1
SQL = "Select "
For i1 = 0 To RSIn.Fields.Count - 1
SQL = SQL & RSIn.Fields(i1).name & ","
Next i1
SQL = Left(SQL, Len(SQL) - 1) & " From " & TableName & " Where 1 = 2"
RSOut.Open SQL, CON2, adOpenDynamic, adLockOptimistic
While Not RSIn.EOF
RSOut.AddNew
For i1 = 0 To RSIn.Fields.Count - 1
RSOut.Fields(i1).Value = RSIn.Fields(i1).Value
Next i1
RSIn.MoveNext
Wend
RSOut.UpdateBatch
RSIn.Close
RSOut.Close
End Sub

Insert into Select with two different databases

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0