使用两个不同的数据库插入Select [英] Insert into Select with two different databases
问题描述
大家好。
我试图将数据从一个数据库存档到另一个数据库。
我花了几个小时把这段代码放在一起 - 它不起作用，我无法弄清楚原因。
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
推荐答案
i认为你在做一些不同的事情。您的查询只会将同一个表的记录插入其中（重复！）因为
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)
只能使用一个连接一个时间。
如果这是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]
I'我不确定您是否可以使用 IN子句 [ ^ ]从不同的数据库中获取记录（MS Access文件）使用VB.NET ...但尝试你需要完全确定两个表具有相同的数据结构。
Ins请查询您的查询，使用：
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:
INSERT INTO TableName (Field1, Field2, Field3, ..., FieldN)
SELECT Field1, Field2, Field3, ..., FieldN
FROM TableName IN 'FullPath'
更多关于：
IN子句（MS Access SQL） [ ^ ]
使用IN子句访问外部数据 [ ^ ]
如果它对您有用，请给我一个标志......
另一个想法是将源数据库中的表与不同的名称链接起来，例如： 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
这是一个想法：如何通过VB.net以编程方式刷新链接表？ [ ^ ]，但尚未完全实现;（
使用VB.NET 2010重新链接MS Access表 [ ^ ];）
http://www.microsoftaccessexpert.com/Microsoft-Access-Code-LinkTable.aspx [ ^ ] - vba
更多：
使用链接表向导（ADP）链接Access项目中的表 [ ^ ]
关于导入和链接数据和数据库对象 [ ^ ]
如何使用Microsoft Visual Basic .NET连接到Microsoft Access数据库并检索数据 [ ^ ]
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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
