vba 从另一个更新一个 Access 数据库中的表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16918544/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Updating table in one Access database from another?
提问by Jeff Brady
I have an Access 2007 database on my local PC, and another one out on the network.
我的本地 PC 上有一个 Access 2007 数据库，网络上有另一个数据库。
local: c:\mydatabase.accdb network: \server\share\publicdatabase.accdb
本地：c:\mydatabase.accdb 网络：\server\share\publicdatabase.accdb
Both databases have 2 identical tables, let's call them Table1and Table2
这两个数据库有2个相同的表，让我们给他们打电话Table1和Table2
My process involves exporting data from a PICK database to a delimited text file, then importing it into Access.
我的过程涉及将数据从 PICK 数据库导出到带分隔符的文本文件，然后将其导入 Access。
Currently, I update the tables in my local db, then in Access I copy/paste the table from my local db to the network db. I'm hoping to do this via VBA.
目前，我更新本地数据库中的表，然后在 Access 中将表从本地数据库复制/粘贴到网络数据库。我希望通过 VBA 来做到这一点。
I found the following code that would be used on the network db to clear a table and then 'pull' the update, but I need to run it from my pc to clear the network db table and then 'push' the update.
我发现以下代码将用于在网络数据库上清除表然后“拉”更新，但我需要从我的电脑运行它以清除网络数据库表，然后“推送”更新。
Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test Files\database.mdb")
AccessConn.Open()
Dim AccessCommand As New System.Data.OleDb.OleDbCommand("DELETE * FROM [Catlog]", AccessConn)
AccessCommand.ExecuteNonQuery()
AccessCommand.CommandText = "INSERT INTO [Table1] SELECT * FROM [MS Access;DATABASE=C:\Test Files\database.mdb;].[Table1]"
AccessCommand.ExecuteNonQuery()
AccessConn.Close()
Also if it's not too much trouble, how could I include a check to first make sure the network db is available to be updated? (not already open by other user)
另外，如果不是太麻烦，我怎么能包括一个检查来首先确保网络数据库可以更新？（尚未被其他用户打开）
Thank you!!
谢谢！！
EDIT: this works so far:
编辑：到目前为止，这有效：
With Access.DoCmd
.RunSQL "Delete FROM Table1 IN '\server\share\publicdatabase.accdb'"
.RunSQL "Insert INTO Table1 IN '\server\share\publicdatabase.accdb' SELECT * FROM Table1"
End With
回答by Gord Thompson
The following C# console application works for me. Notice that it uses ODBC and it opens "publicdatabase.accdb" with Exclusive=1;to ensure that nobody else is using it. I would hope that it wouldn't be too difficult to apply the same techniques to a VBA solution.
以下 C# 控制台应用程序适用于我。请注意，它使用 ODBC 并打开“publicdatabase.accdb”Exclusive=1;以确保没有其他人在使用它。我希望将相同的技术应用于 VBA 解决方案不会太困难。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Odbc;
namespace odbcTest
{
class Program
{
static void Main(string[] args)
{
using (var con = new OdbcConnection(
"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" +
@"Dbq=C:\__tmp\publicdatabase.accdb;" +
"Exclusive=1;" +
"Uid=admin;" +
"Pwd=;"))
{
try
{
con.Open();
}
catch
{
Console.WriteLine("Exclusive 'Open' failed. Quitting.");
System.Threading.Thread.Sleep(2000);
return;
}
using (var cmd = new OdbcCommand())
{
cmd.Connection = con;
cmd.CommandText = "DELETE FROM Table1";
cmd.ExecuteNonQuery();
cmd.CommandText = @"INSERT INTO Table1 SELECT * FROM [MS Access;DATABASE=C:\__tmp\mydatabase.accdb;].[Table1]";
cmd.ExecuteNonQuery();
}
con.Close();
}
Console.WriteLine("Done.");
System.Threading.Thread.Sleep(2000);
}
}
}
Edit
编辑
The corresponding VBA code would be something like this. It is intended to be run from the local database (mydatabase.accdb) and it uses Application.CurrentDb.Nameto avoid hard-coding the local database path (in case "mydatabase.accdb" gets moved to another location):
相应的 VBA 代码将是这样的。它旨在从本地数据库 (mydatabase.accdb) 运行，并Application.CurrentDb.Name用于避免对本地数据库路径进行硬编码（以防“mydatabase.accdb”移动到另一个位置）：
Sub UpdatePublicDatabase()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
On Error GoTo UpdatePublicDatabase_OpenError
con.Open _
"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & _
"Dbq=C:\__tmp\publicdatabase.accdb;" & _
"Exclusive=1;" & _
"Uid=admin;" & _
"Pwd=;"
On Error GoTo 0
con.Execute "DELETE FROM Table1"
con.Execute "INSERT INTO Table1 SELECT * FROM [MS Access;DATABASE=" & Application.CurrentDb.Name & ";].[Table1]"
con.Close
Debug.Print "Done."
Exit Sub
UpdatePublicDatabase_OpenError:
Debug.Print "Exclusive 'Open' failed. Quitting."
Exit Sub
End Sub
回答by Johnny Bones
Your solution appears to be sound. We're still in the dark ages at work (A2003) so I'm not sure about this; is there no locking file in A2007? If so, you can open it and make sure there are no entries (the same process you use to import that PICK file), and if it's clear then you can run your import/export process.
您的解决方案似乎是合理的。我们仍然处于工作的黑暗时代（A2003），所以我不确定这一点；A2007中没有锁定文件吗？如果是这样，您可以打开它并确保没有条目（与您用于导入该 PICK 文件的过程相同），如果没有问题，那么您可以运行导入/导出过程。
回答by Whome
Gord Thompson gave an excellent answer which help me implementing my use case. I had to copy SOURCEDB.tableXX rows to DESTDB.tableXX. Not all table data should be copied each time this function is run.
Gord Thompson 给出了一个很好的答案，帮助我实现了我的用例。我不得不将 SOURCEDB.tableXX 行复制到 DESTDB.tableXX。并非每次运行此函数时都应复制所有表数据。
I created a new ACTIONSDB.accdb msaccess file where inserted a new vba Module1. This probably should work in Excel vba module as well. This is simplified version without my special-case when_not_to_copy_based_on_month_and_day rules.
我创建了一个新的 ACTIONSDB.accdb msaccess 文件，其中插入了一个新的 vba Module1。这可能也适用于 Excel vba 模块。这是没有我的特殊情况 when_not_to_copy_based_on_month_and_day 规则的简化版本。
Option Compare Database
Option Explicit
Sub importDataFromDB()
' Copy table rows from source MSAccess db to another db
On Error GoTo ErrorLabel
Dim idx As Long
Dim tables As Collection
Dim sTable As String
Dim sql As String
Dim con As Object
Set tables = New Collection
Call tables.Add("tbl_data1")
Call tables.Add("tbl_data2")
Call tables.Add("tbl_data5")
' ...more tablenames
Set con = CreateObject("ADODB.Connection")
con.ConnectionString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & _
"Dbq=C:\data\DESTDB.accdb;" & _
"Exclusive=1;" & _
"Uid=;" & _
"Pwd=;"
con.Open
Debug.Print ""
Debug.Print "Tables (" & tables.Count & ")"
For idx = 1 To tables.Count
sTable = tables.Item(idx)
Debug.Print sTable & " (" & idx & "/" & tables.Count & ")"
sql = "INSERT INTO ${table} SELECT * FROM [MS Access;DATABASE=C:\data\SOURCEDB.accdb;].[${table}]"
sql = Replace(sql, "${table}", sTable, 1, -1, vbTextCompare)
con.Execute "DELETE FROM " & sTable
con.Execute sql
Next idx
con.Close
Debug.Print "Completed"
ExitLabel:
Exit Sub
ErrorLabel:
Debug.Print Err.Description
con.Close
Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
End Sub
