因此,我对如何在VBA代码中处理外部数据库和当前数据库有些困惑。下面是一个子程序,其目的是使用在外部Access数据库中找到的唯一条目来更新当前Access数据库。
外部SourceDBPath
和SelectedTable
传入,我使用字符串变量指定外部数据库和表SourceDBTable
。然后,在SQL中,我尝试提取值与其核心响应字段不匹配的条目,以便仅将两个DB之间的唯一条目插入源数据库。
(例如,其中source = external:
NOT EXIST sourceDB.SelectedTable.Field1 = currentDB.SelectedTable.Field1 And sourceDB.SelectedTable.Field2 = currentDB.SelectedTable.Field2 And sourceDB.SelectedTable.Field3 = currentDB.SelectedTable.Field3,
等)
所以,我的问题是:
1)我是否需要在SQL中指定当前数据库(如currentDB.table.field),或者如果不带前缀调用表或字段(如代码中的表或字段),它将默认为当前数据库下面)?
2)最终,我是否会以正确的方式解决这个问题?
我的代码:
Private Sub UpdateDatabaseTable(SourceDBPath As String, SelectedTable As String)
Dim SourceDBTable As String
On Error GoTo DBError
SourceDBTable = "[;DATABASE=" & SourceDBPath & "]." & SelectedTable
Call DoCmd.RunSQL("INSERT INTO " & SelectedTable & " " & _
"SELECT Field1, Field2, Field3 " & _
"FROM " & SourceDBTable & " " & _
"WHERE NOT EXISTS( SELECT * " & _
"FROM " & SourceDBTable & " " & _
"WHERE (Field1=" & SourceDBTable & ".Field1 And Field2=" & SourceDBTable & ".Field2 And Field3=" & SourceDBTable & ".Field3"));")
GoTo EndSub
DBError:
MsgBox "Database Error!" & vbCrLf & "Error #" & Str(Err.Number) & ": " & Err.Source & vbCrLf & Err.Description, vbExclamation, "Database Error"
EndSub:
End Sub
注意:我通过推断和修改在此处的解决方案中找到的代码来派生我的SQL
您的代码中有2个主要错误,否则,它应该可以工作。
Private Sub UpdateDatabaseTable(SourceDBPath As String, SelectedTable As String)
Dim SourceDBTable As String
On Error GoTo DBError
SourceDBTable = "[;DATABASE=" & SourceDBPath & "].[" & SelectedTable & "]"
DoCmd.RunSQL "INSERT INTO " & SelectedTable & " t " & _
"SELECT Field1, Field2, Field3 " & _
"FROM " & SourceDBTable & " s" & _
"WHERE NOT EXISTS( SELECT * " & _
"FROM " & SourceDBTable & " s1 " & _
"WHERE (t.Field1=s1.Field1 And t.Field2=s1.Field2 And t.Field3=s1.Field3));"
GoTo EndSub
DBError:
MsgBox "Database Error!" & vbCrLf & "Error #" & Str(Err.Number) & ": " & Err.Source & vbCrLf & Err.Description, vbExclamation, "Database Error"
EndSub:
End Sub
我还删除了不推荐使用的Call
关键字。(可选)您可以使用进一步调整此设置CurrentDb.Execute
,但这不是必需的
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句