Solved Table is Not Visible in Target DB after DoCmd.TransferDatabase (1 Viewer)

Pac-Man

Active member
Local time
Today, 16:25
Joined
Apr 14, 2020
Messages
408
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.
Code:
    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
 

isladogs

MVP / VIP
Local time
Today, 11:25
Joined
Jan 14, 2017
Messages
18,186
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.
Code:
    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

Active member
Local time
Today, 16:25
Joined
Apr 14, 2020
Messages
408
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

MVP / VIP
Local time
Today, 11:25
Joined
Jan 14, 2017
Messages
18,186
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.
Code:
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

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:

Code:
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
 

Attachments

  • DeepHideTablesExample.accdb
    520 KB · Views: 428

Pac-Man

Active member
Local time
Today, 16:25
Joined
Apr 14, 2020
Messages
408
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.
Code:
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

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:

Code:
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.
 

Users who are viewing this thread

Top Bottom