如何在access 2007中使用vba刷新表列表

Question

如何在Access 2007中使用vba刷新表列表。我使用vba:在Sql代码中制作表格

DoCmd.RunSQL "SELECT * INTO table2 FROM table1", True    
RefreshDatabaseWindow
DoCmd.TransferDatabase acExport, "Microsoft Access", "C:import.mdb", , "table2", tab2, 0

错误消息：Microsoft Office Access找不到对象表2如果暂停RefreshDatabaseWindow并手动刷新，请按F5并重新运行以正常工作。如何自动完成？在Access 2003中，如果表列表处于活动状态，则工作正常。

我尝试暂停但不工作：

Private Declare Sub sApiSleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
...
RefreshDatabaseWindow
Call sApiSleep(4000)
...

Answer 1

这是我用来附加SQL/服务器表的方法。我删除了它们，然后用这个代码重新链接它们。显然，您必须为每个表重复最后一节。我不知道这是否适用于ADP。我的环境不使用ADP。

Dim vConnect As String
Dim td As New TableDef
Dim vtablename As String
vConnect = "ODBC;Driver={SQL Server Native Client 10.0};SERVER=<server>;DATABASE=" <database>; Uid=<user>; Pwd=<pwd>;TrustServerCertificate=yes;encrypt = yes;"
'Delete existing tables
For Each td In CurrentDb.TableDefs
vattributes = td.Attributes And dbAttachedODBC
If Left(td.Name, 4) <> "Msys" And vattributes > 0 Then CurrentDb.TableDefs.Delete td.Name
Next
Debug.Print "Old Tables Deleted"
Set td = CurrentDb.CreateTableDef("<your table name>")
td.Connect = vConnect
td.SourceTableName = "<your table name>"
vtablename = td.Name
CurrentDb.TableDefs.Append td
Debug.Print "Attached table: " & vtablename

Answer 2

在Access 2007, 2010(项目.ADP)中工作：

RefreshDatabaseWindow
DoCmd.SelectObject acTable, "table2", True
DoCmd.TransferDatabase ...

Answer 3

这在Access2010 中运行良好

在表创建和TransferDataBase之间调用此函数

函数refrescarnextBBDD()

Dim cadenaConexion As String
cadenaConexion = Application.CurrentProject.BaseConnectionString
Application.CurrentProject.CloseConnection
Application.CurrentProject.OpenConnection cadenaConexion

终端功能