从另一个数据库访问SQL查询 [英] Access SQL Query from another DB

问题描述

我想对一个Access表应用一个SQL查询，它从另一个Access文件中的一个表中检索数据.我在这个问题上四处张望，似乎无法找到可行的解决方案.

I want to apply an SQL query to one Access table, from which it is retrieving data from a table in another Access file. I've looked around on this subject and can't seem to get solutions to work.

基于此来源 http://support.microsoft.com/kb/113701 ，我提出了以下建议，但仍然没有运气.

Based on this source http://support.microsoft.com/kb/113701, I came up with the following, but still have no luck.

sSQL = "UPDATE TableInCurrentDB 
SET [Field1InCurrentDB]= DAvg('Field1InExternalDB','[;database=C:\VB\ExternalDB.accdb].[TableInExternalDB]','Field2InExternalDB= & Year(Now()) & ') 
WHERE [Field2InCurrentDB]='1';"
DoCmd.RunSQL sSQL

我知道该错误位于对外部DB的引用中，因为如果表位于同一数据库中，则代码可以正常工作.但是，由于我得到的错误是未知"，因此很难准确地说出问题所在.

I know that the error lies somewhere in the reference to the external DB, because the code works fine if the tables are in the same database. However, it's tough to tell exactly what's wrong because the error I get is 'Unknown'.

如何修改此语句以从另一个Access数据库的表更新Access表?

How can I modify this statement to update an Access table from another Access database's table?

推荐答案

您宁愿不使用指向外部数据库中表的链接，但是当您要使用DAvg时，这种选择很复杂.但是，由于要使用VBA代码进行此操作，因此可以放弃DAvg并分两步执行所需的操作:

You prefer not to use a link to the table in the external database, but that choice is a complication when you want to use DAvg. However, since you're doing this with VBA code, you can ditch DAvg and do what you need in 2 steps:

1. 首先从外部表中检索平均值.
2. 在您的UPDATE中使用该步骤#1的平均值.

1. First retrieve the average from the external table.
2. Use that step #1 average in your UPDATE.

对于步骤1，在Access查询设计器中将其作为新查询进行测试...

For step #1, test this as a new query in the Access query designer ...

SELECT Avg(Field1InExternalDB)
FROM TableInExternalDB IN 'C:\VB\ExternalDB.accdb'
WHERE Field2InExternalDB=Year(Date());

假设查询返回正确的值，请修改您的VBA代码以检索相同的值.

Assuming that query returns the correct value, adapt your VBA code to retrieve the same value.

Dim db As DAO.database
Dim strSelect As String
Dim varAvg As Variant
strSelect = "SELECT Avg(Field1InExternalDB)" & vbCrLf & _
    "FROM TableInExternalDB IN 'C:\VB\ExternalDB.accdb'" & vbCrLf & _
    "WHERE Field2InExternalDB=Year(Date());"
'Debug.Print strSelect
Set db = CurrentDb
varAvg = db.OpenRecordset(strSelect)(0)
Debug.Print Nz(varAvg, 0) ' see note

请注意，当没有行包含与当前年份匹配的Field2InExternalDB值时，查询将返回Null.这就是为什么varAvg被声明为Variant的原因.以后Nz(varAvg, 0)将给您零而不是Null.

Note that query will return Null when no rows include Field2InExternalDB values which match the current year. That is why varAvg is declared as Variant. Later Nz(varAvg, 0) will give you zero instead of Null.

然后，您可以对UPDATE使用参数查询，并提供Nz(varAvg, 0)作为参数值.

Then you can use a parameter query for your UPDATE and supply Nz(varAvg, 0) as the parameter value.

Dim qdf As DAO.QueryDef
Dim strUpdate As String
strUpdate = "UPDATE TableInCurrentDB" & vbCrLf & _
    "SET [Field1InCurrentDB]=[pAvg]" & vbCrLf & _
    "WHERE [Field2InCurrentDB]='1';"
'Debug.Print strUpdate
Set qdf = db.CreateQueryDef(vbNullString, strUpdate)
qdf.Parameters("pAvg") = Nz(varAvg, 0)
qdf.Execute dbFailOnError
Set qdf = Nothing
Set db = Nothing

这篇关于从另一个数据库访问SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！