1

Topic: The list of tables in basis is not updated

The list of tables in basis after here this code is not updated

Sub ExceltoAccess ()
Worktable = CreateObject ("WScript. Shell").SpecialFolders ("Desktop")
BaseName = Worktable AND "\" AND ActiveWorkbook. Name AND ".mdb"
Select Case CLng (Split (Application. Version, ".") (0))
Case Is <12
dbConnectStr = "Provider=Microsoft. Jet. OLEDB.4.0; Data Source =" AND BaseName AND ";"
Case Is> = 12
dbConnectStr = "Provider=Microsoft. ACE.OLEDB.12.0; Data Source =" AND BaseName AND ";"
End Select
On Error Resume Next
Set oAccess = GetObject ("Access. Application")
If Err. Number = 429 Then
Set Catalog = CreateObject ("ADOX.Catalog")
Catalog. Create dbConnectStr
Set Catalog = Nothing
Set cnt = New ADODB.Connection
cnt. Open dbConnectStr
Set rs = CreateObject ("ADODB.Recordset")
sSQL = "SELECT * INTO [" AND ActiveSheet. Name AND "] FROM [Excel 12.0; HDR=YES; IMEX=1; DATABASE =" AND ActiveWorkbook. FullName AND "]. [" AND ActiveSheet. Name AND "$]"
Set rs = cnt. Execute (sSQL)
cnt. Close
Set sAccess = CreateObject ("Access. Application")
sAccess. Visible = True
sAccess. UserControl = True
sAccess. OpenCurrentDataBase (BaseName)
Else
basepath = oAccess. CurrentDb. Name
If Err. Number = 91 Then
Set Catalog = CreateObject ("ADOX.Catalog")
Catalog. Create dbConnectStr
Set Catalog = Nothing
Set sAccess = GetObject ("Access. Application")
sAccess. OpenCurrentDataBase (BaseName)
End If
oAccess.CurrentProject.Connection.Execute "SELECT * INTO [" AND ActiveSheet. Name AND "] FROM [Excel 12.0; HDR=YES; IMEX=1; DATABASE =" AND ActiveWorkbook. FullName AND "]. [" AND ActiveSheet. Name AND "$]"
End If
Err. Clear
End Sub

But if to rediscover basis all tables are displayed. How to correct this trouble?

2

Re: The list of tables in basis is not updated

BlackeAngel;
In what it expresses? It is not visible new tables in the panel of the navigator? Push F5 in it. In the code it will be necessary for database variables to consider fresh value CurrentDb with new tables, Dbengine (0 (0), for example, does not see new tables while violently you will not update through TableDefs. Refresh.
Yes, and very much I recommend all variables to declare explicitly, in all units to deliver Option Explicit, and in adjustments of editor VBA tick Require Variable Declarations, differently can run into unpredictable behavior if casually be mistaken in a title of a variable or Aks sets incorrect data type of your implicit variable.

3

Re: The list of tables in basis is not updated

;
Thanks for the help about request, but here with

Application. RefreshDatabaseWindow

The ambush,  excel does not know such commands. I launch from excel...

4

Re: The list of tables in basis is not updated

, yes, chopped off. Well so window Access is contracted. Otherwise as?

5

Re: The list of tables in basis is not updated

BlackeAngel wrote:

excel does not know such commands

sAccess. RefreshDatabaseWindow

And with error handling full seams, will long get problems. It is necessary to ungear only in extreme cases and on minimum possible number of commands

6

Re: The list of tables in basis is not updated

BlackeAngel wrote:

Well so window Access is contracted

And here Aktsess if the code in Eksele is fulfilled? Errors will be produced by the Ex-fur-tree

7

Re: The list of tables in basis is not updated

,

sAccess. RefreshDatabaseWindow
Application. RefreshDatabaseWindow

Do not give desirable result. While only F5 yields the necessary result.

8

Re: The list of tables in basis is not updated

, everything, the jamb is found, a letter passed, and vba was silent. Rewrote once again and all is normal. The glitch was shorter.

9

Re: The list of tables in basis is not updated

, more optimally? On mine so the shortest way.

10

Re: The list of tables in basis is not updated

, owing to my "knowledge" - yes

11

Re: The list of tables in basis is not updated

BlackeAngel wrote:

a letter passed, and vba was silent

Therefore also it is necessary to use Option Explicit and error handling. And so search of everyone such  will occupy hours

12

Re: The list of tables in basis is not updated

, can offer the optimal decision? Share wink

13

Re: The list of tables in basis is not updated

MrShin, It agree, But it I do in the end when precisely I do not know. Yes switch-off of errors I try not to use generally, except categorically severe need.