Solved Table is Not Visible in Target DB after DoCmd.TransferDatabase

[Pac-Man]

Hello,

I'm using following code to transfer a table along with its data into other db (backend db of another FE) which already has a table with same name. I intend to replace that table with the transferred one. Also a field in the table is an OLE Object data type.

    Dim db As dao.Database
    Set db = DBEngine.OpenDatabase(Name:=sDBFullPath, options:=False, ReadOnly:=False, Connect:=";PWD=" & sPWD)

    If sObjectTargetName = "" Then sObjectTargetName = sObjectSourceName

    DoCmd.TransferDatabase acExport, "Microsoft Access", sDBFullPath, acTable, sObjectSourceName, sObjectTargetName, False

    db.Close
    Set db = Nothing

After the code is run, table is transferred successfully and is also being linked shown correctly in the respective FE in which it is linked. But when I view the target db in which the table was transferred, the table is not visible. Even if I turned ON show hidden tables and show system table, it is not being shown. I viewed the MSysObject table and it is present in it with flag 1.

My question is, how to fix this issue. And if I let it remain unfixed, can it cause some problem in future like db corruption etc? What am I doing wrong in the code which is causing this issue.

Best Regards,
Abdullah

 

[Uncle Gizmo]

Try:-

RefreshDatabaseWindow

RefreshDatabaseWindow Method [Access 2003 VBA Language Reference]

docs.microsoft.com

 

[Pac-Man]

Try:-

RefreshDatabaseWindow

RefreshDatabaseWindow Method [Access 2003 VBA Language Reference]

docs.microsoft.com

Thanks for reply @Uncle Gizmo, RefreshDatabaseWindow didn't help. Even C&R and also closing and reopening the db didn't help.

 

[isladogs]

Hello,

I'm using following code to transfer a table along with its data into other db (backend db of another FE) which already has a table with same name. I intend to replace that table with the transferred one. Also a field in the table is an OLE Object data type.

    Dim db As dao.Database
    Set db = DBEngine.OpenDatabase(Name:=sDBFullPath, options:=False, ReadOnly:=False, Connect:=";PWD=" & sPWD)

    If sObjectTargetName = "" Then sObjectTargetName = sObjectSourceName

    DoCmd.TransferDatabase acExport, "Microsoft Access", sDBFullPath, acTable, sObjectSourceName, sObjectTargetName, False

    db.Close
    Set db = Nothing

After the code is run, table is transferred successfully and is also being linked shown correctly in the respective FE in which it is linked. But when I view the target db in which the table was transferred, the table is not visible. Even if I turned ON show hidden tables and show system table, it is not being shown. I viewed the MSysObject table and it is present in it with flag 1.

My question is, how to fix this issue. And if I let it remain unfixed, can it cause some problem in future like db corruption etc? What am I doing wrong in the code which is causing this issue.

Best Regards,
Abdullah

Flags =1 corresponds to a table which is deep hidden so it will not appear in the navigation pane even if you show hidden and system tables.
You need to change the Flags value to zero before transfer (or afterwards)

 

[Pac-Man]

Flags =1 corresponds to a table which is deep hidden so it will not appear in the navigation pane even if you show hidden and system tables.
You need to change the Flags value to zero before transfer (or afterwards)

Thanks @isladogs for reply. How can I change flag value to 0. I tried but it is not being editable.

 

[isladogs]

First of all, I wonder why you made the tables deep hidden in the first place.
Are you aware that you can still use deep hidden tables in a query provided you know the table name.

e.g.

SELECT * FROM tblDeepHidden

Anyway, you can't edit the MSysObjects table directly as it is read only.
However you can change the table properties using code

Public Sub UnhideTable(ByVal strTable As String)

On Error GoTo Err_Handler

    Application.CurrentDb.TableDefs(strTable).Properties("Attributes").Value = 0
    
Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " in UnhideTable procedure : " & Err.Description
    Resume Exit_Handler

End Sub

Typical usage UnhideTable "tblSettings"

Similarly, to deep hide a table, you can use:

Public Sub HideTable(ByVal strTable As String)

On Error GoTo Err_Handler

    Application.CurrentDb.TableDefs(strTable).Properties("Attributes").Value = dbHiddenObject
    
Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " in HideTable procedure : " & Err.Description
    Resume Exit_Handler

End Sub

As already suggested, add the line RefreshDatabaseWindow to show the changes in the nav pane immediately

You might want to test using the attached example app before doing this on your own database

 

[Pac-Man]

First of all, I wonder why you made the tables deep hidden in the first place.
Are you aware that you can still use deep hidden tables in a query provided you know the table name.

e.g.

SELECT * FROM tblDeepHidden

Anyway, you can't edit the MSysObjects table directly as it is read only.
However you can change the table properties using code

Public Sub UnhideTable(ByVal strTable As String)

On Error GoTo Err_Handler

    Application.CurrentDb.TableDefs(strTable).Properties("Attributes").Value = 0
   
Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " in UnhideTable procedure : " & Err.Description
    Resume Exit_Handler

End Sub

Typical usage UnhideTable "tblSettings"

Similarly, to deep hide a table, you can use:

Public Sub HideTable(ByVal strTable As String)

On Error GoTo Err_Handler

    Application.CurrentDb.TableDefs(strTable).Properties("Attributes").Value = dbHiddenObject
   
Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " in HideTable procedure : " & Err.Description
    Resume Exit_Handler

End Sub

As already suggested, add the line RefreshDatabaseWindow to show the changes in the nav pane immediately

You might want to test using the attached example app before doing this on your own database

Thanks a lot, problem is solved. Table is visible now after running the provided code. So nice of you.