Open a new database from inside access

[Valentine]

Just tried to run the button. Tables still linked. The way thsi is I cannot do a simple copy and paste so here is some of what i wrote.

Dim sDBUnlinkName
Dim db as DAO.Database
Dim app as Object
Dim tdf as DAO.TableDef

sDBUnlinkName = sDBUnlinkFolder & sDBFile & " " & sDateFormat & "_unlinked" "." sDBFileExt

set app = CreateObject("Access.Application")
set db = app.DBEngine.OpenDatabase(sDBUnlinkName)

For Each tdf in db.TableDefs
with tdf
If InStr(.Connect, "ACEWSS") > 0 Then
app.DoCmd.Select acTable, .Name, True
app.DoCmd.RunCommand acCmdConvertToLocalTable
End If
End with

 

[theDBguy]

Yes but @theDBguy do you know what ype of object a "6" is?

@Pat Hartman, Honestly, I have no idea. How do I find out? Here's a screenshot of my MSysObjects table. For comparison, I created an Access linked table as well. The one in red box is to SharePoint, and the one in blue is Access.

 

[jdraw]

Not answering the question as per the title, but an interesting reference for the MSysObjects Types and Flag values from Daniel Pineault (Devhut).

 

[Valentine]

@theDBguy looking at your screenshot i noticed that "ACEWSS" is your system, would that make that line not work in mine and if so how do i find out what my connect title is?

 

[theDBguy]

@theDBguy looking at your screenshot i noticed that "ACEWSS" is your system, would that make that line not work in mine and if so how do i find out what my connect title is?

Open your MSysObjects table and check there.

 

[Valentine]

app.DoCmd.Select acTable, .Name, True

Do I have to put each table's name in here or should this go through them automatically?

 

[theDBguy]

Do I have to put each table's name in here or should this go through them automatically?

It's all automatic. All you have to provide is the name of the backup ACCDB file.

 

[Valentine]

im getting an error on that line that states "Object doesn't support this property or method"

 

[theDBguy]

im getting an error on that line that states "Object doesn't support this property or method"

Not sure if you had a typo. I gave you "SelectObject," and you showed me only "Select."

 

[Valentine]

Your right there was a typo so I fixed that and a new error "The command or action 'SelectObject' isn't available now"

 

[theDBguy]

Your right there was a typo so I fixed that and a new error "The command or action 'SelectObject' isn't available now"

When you get the error, click on the Debug button and then in the Immediate Window, enter the following:

?tdf.Name

Make sure the result is a table name that actually exist in the backup db.

 

[Valentine]

When you get the error, click on the Debug button and then in the Immediate Window, enter the following:

?tdf.Name

Make sure the result is a table name that actually exist in the backup db.

I am not getting a new window after hitting debug. I just get sent to my VBA code for the button with that line highlighted yellow. On the bottom in the Locals window if I expand "tdf" and go to the Name branch the first table that needs to be unlocked is shown.

Also in that Locals window in the tdf tree there is a branch called "ConflictTable" and "ReplicaFilter" that are saying cannot perform this operation.

 

[Valentine]

Here is what i have right now..

Private Sub Backup_Click()

    Dim sSourceFolder
    Dim sBackupFolder
    Dim sDBFile
    Dim sDBFileExt
    Dim sDateFormat
    Dim sDBUnlinkFolder
    Dim sDBUnlinkName
    Dim db As DAO.Database
    Dim app As Object
    Dim tdf As DAO.TableDef
    
'Setting Variables
    sSourceFolder = "File Path"       
    sBackupFolder = "File Path"
    sDBFile = "File Name"
    sDBFileExt = "accdb"
    sDateFormat = (Format(Date, "yyyymmdd"))
    sDBUnlinkFolder = "File Path"
    sDBUnlinkName = "File Path"
    
'Copy Files
    FileCopy sSourceFolder & sDBFile & "." sDBFileExt, sBackupFolder & sDBFile & " " & sDateFormat & "." sDBFileExt
    FileCopy sSourceFolder & sDBFile & "." sDBFileExt, sDBUnlinkFolder & sDBFile & " " & sDateFormat & "_unlinked" & "." & sDBFileExt
    
'Open and Convert tables
    Set app = CreateObject("Access.Application")
    Set db = app.DBEngine.OpenDatabase(sDBUnlinkName)
    
    For Each tdf In db.TableDefs
        With tdf
            If InStr(.Connect, "WSS") > 0 Then
            app.DoCmd.SelectObject acTable, .Name, True
            app.DoCmd.RunCommand acCmdConvertLinkedTableToLocal
            End If
        End With
    Next
    
    MsgBox "All Tables are now Unlinked", vbInformation, "Info"
    
    db.Close
    Set db = Nothing
    Set app = Nothing
    
End Sub

 

[theDBguy]

Here is what i have right now..

Private Sub Backup_Click()

    Dim sSourceFolder
    Dim sBackupFolder
    Dim sDBFile
    Dim sDBFileExt
    Dim sDateFormat
    Dim sDBUnlinkFolder
    Dim sDBUnlinkName
    Dim db As DAO.Database
    Dim app As Object
    Dim tdf As DAO.TableDef
   
'Setting Variables
    sSourceFolder = "File Path"      
    sBackupFolder = "File Path"
    sDBFile = "File Name"
    sDBFileExt = "accdb"
    sDateFormat = (Format(Date, "yyyymmdd"))
    sDBUnlinkFolder = "File Path"
    sDBUnlinkName = "File Path"
   
'Copy Files
    FileCopy sSourceFolder & sDBFile & "." sDBFileExt, sBackupFolder & sDBFile & " " & sDateFormat & "." sDBFileExt
    FileCopy sSourceFolder & sDBFile & "." sDBFileExt, sDBUnlinkFolder & sDBFile & " " & sDateFormat & "_unlinked" & "." & sDBFileExt
   
'Open and Convert tables
    Set app = CreateObject("Access.Application")
    Set db = app.DBEngine.OpenDatabase(sDBUnlinkName)
   
    For Each tdf In db.TableDefs
        With tdf
            If InStr(.Connect, "WSS") > 0 Then
            app.DoCmd.SelectObject acTable, .Name, True
            app.DoCmd.RunCommand acCmdConvertLinkedTableToLocal
            End If
        End With
    Next
   
    MsgBox "All Tables are now Unlinked", vbInformation, "Info"
   
    db.Close
    Set db = Nothing
    Set app = Nothing
   
End Sub

Hi. Can you show us an example of what might be stored in the variable sDBUnlinkName?

 

[Valentine]

It has the file path starting at C: going to the file name ending with .accdb

Sorry i cant give the actual path its a secure system

 

[Valentine]

After some more trial and error and different codes I have found i have a simple problem. When I open the copy I just created that pop up on the top that has the button to click stating "Enable Editing" isnt clicked. So when i try to convert to local it doesnt have permission since it is in a read only copy. Does anyone know how to fix that?

 

[theDBguy]

It has the file path starting at C: going to the file name ending with .accdb

Sorry i cant give the actual path its a secure system

That makes it hard to help you troubleshoot it though. Are you sure it doesn't have any illegal characters, for example?

 

[theDBguy]

After some more trial and error and different codes I have found i have a simple problem. When I open the copy I just created that pop up on the top that has the button to click stating "Enable Editing" isnt clicked. So when i try to convert to local it doesnt have permission since it is in a read only copy. Does anyone know how to fix that?

Easy! Can you set the backup folder as a Trusted Location?

 

[Valentine]

I can if you tell me how to do that

 

[theDBguy]

I can if you tell me how to do that

File > Options > Trust Center > Trusted Center Settings > Trusted Locations > Add new location...