3

How to refresh the table list using vba in Access 2007. I make table in Sql code using vba:

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

Error message: Microsoft Office Access can not find the object table2 if pause RefreshDatabaseWindow and refresh manualy press F5 and run back to work correctly. How to do it automatically? In Access 2003 work correctly if table list is active.

I try pause but not work:

Private Declare Sub sApiSleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
...
RefreshDatabaseWindow
Call sApiSleep(4000)
...
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
crywar
  • 73
  • 1
  • 1
  • 6
  • 1
    Have you set warnings off? Have you got On Error Resume Next somewhere? You should not need to refresh the database window for that code to run, you should be asked if you want to `paste into table2`, then the transfer should run. – Fionnuala Oct 03 '12 at 13:43
  • Code `DoCmd.RunSQL` does not ask for anything. I not use `DoCmd.SetWarnings False`. – crywar Oct 03 '12 at 14:01
  • You should get a warning. At some stage, warnings have been set to false. The change affects every Access database on your PC. Add this line just before DoCmd.RunSQL : `DoCmd.SetWarnings True` – Fionnuala Oct 03 '12 at 14:04
  • Does not work, all command `DoCmd.` run on `Access` in `SQL server` (ADODB.Connection). Table "table2" is created on the server (dbo.table2). `.RefreshLink` also does not work. – crywar Oct 04 '12 at 06:28
  • In that case, you have left out a great deal of necessary information. For example, are you using an ADP? What exactly is the code? – Fionnuala Oct 04 '12 at 09:38
  • Project access `.adp` connect to `SQL server`, vba: `CurrentProject.Connection.ConnectionString` – crywar Nov 13 '12 at 14:51

3 Answers3

1

This is what I use to attach SQL/server tables. I delete them then re-link them with this code. Obviously, you have to repeat the last section for each table. I don't know if this will work with an ADP. My environment does not use an 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
pghcpa
  • 833
  • 11
  • 25
  • 1
    not work with .adp: `Set td = CurrentDb.CreateTableDef("")` Error: `"Object variable or With block variable not set"` – crywar Nov 26 '12 at 13:55
0

Works in Access 2007, 2010 (project .ADP):

RefreshDatabaseWindow
DoCmd.SelectObject acTable, "table2", True
DoCmd.TransferDatabase ...
crywar
  • 73
  • 1
  • 1
  • 6
0

This work fine in Access2010

Call this function between table creation and TransferDataBase

Function refrescarconexionBBDD()

Dim cadenaConexion As String

cadenaConexion = Application.CurrentProject.BaseConnectionString
Application.CurrentProject.CloseConnection

Application.CurrentProject.OpenConnection cadenaConexion

End Function

MikiGrino
  • 1