使用两个不同的数据库插入Select

本文介绍了使用两个不同的数据库插入Select的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

大家好。

我试图将数据从一个数据库存档到另一个数据库。

我花了几个小时把这段代码放在一起 - 它不起作用，我无法弄清楚原因。

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.

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()

我不确定问题是否存在于SQL语句中

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

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

da = New OleDb.OleDbDataAdapter(strSQL, cnTo)

这里的任何帮助都将非常感谢

Darrell

Any assistance here would be hugely appreciated

Darrell

推荐答案

da = New OleDb.OleDbDataAdapter(strSQL, cnTo)

只能使用一个连接一个时间。

如果这是sql，我在表名前面使用了数据库名称来实现这个。就像

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

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

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

更多关于：

[]

[]

如果它对您有用，请给我一个标志......

另一个想法是将源数据库中的表与不同的名称链接起来，例如： SrcTableName 。然后尝试使用以下方法插入数据：

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:

INSERT INTO TableName (FiedlsCollection)
SELECT FieldsCollection
FROM SrcTablename

这是一个想法： []，但尚未完全实现;（

[];）

[] - vba

更多：

[ ]

[]

[]

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[^]

这篇关于使用两个不同的数据库插入Select的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

Tables