Open a new database from inside access (1 Viewer)

Valentine

Member
Local time
Today, 08:26
Joined
Oct 1, 2021
Messages
261
sorry about that internet in the army sucks. What i was trying to say is that doesn't that code require me to have copied the front end before because you are bringing it in as a variable from outside. I am trying to copy and then open that copy in the same button click code.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:26
Joined
Oct 29, 2018
Messages
21,357
sorry about that internet in the army sucks. What i was trying to say is that doesn't that code require me to have copied the front end before because you are bringing it in as a variable from outside. I am trying to copy and then open that copy in the same button click code.
Yes, that's what you said earlier you were trying to do. So, here's what I am saying you could have in your code.

(pseudocode vba)
1. click_event starts
2. vba code to copy the FE file (I thought you already have this part - I mean, in your original post and in the title of your thread, I got the impression you managed to make a copy of the FE but just need to figure out how to open it to convert the linked tables into local tables)
3. call the function I gave you at this point, passing the filepath for the file you just created above
(that's it)

Do you need help with any of those steps?
 

Valentine

Member
Local time
Today, 08:26
Joined
Oct 1, 2021
Messages
261
yeah after i typed that i realized i could just pull in the name of the DB that i created as the strDB in your code. I am awaiting permission to test on an actual linked table to see if what i put in will work.
 

Valentine

Member
Local time
Today, 08:26
Joined
Oct 1, 2021
Messages
261
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.

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

theDBguy

I’m here to help
Staff member
Local time
Today, 05:26
Joined
Oct 29, 2018
Messages
21,357
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.

1635438860523.png
 

Valentine

Member
Local time
Today, 08:26
Joined
Oct 1, 2021
Messages
261
@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

I’m here to help
Staff member
Local time
Today, 05:26
Joined
Oct 29, 2018
Messages
21,357
@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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:26
Joined
Oct 29, 2018
Messages
21,357
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

Member
Local time
Today, 08:26
Joined
Oct 1, 2021
Messages
261
im getting an error on that line that states "Object doesn't support this property or method"
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:26
Joined
Oct 29, 2018
Messages
21,357
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

Member
Local time
Today, 08:26
Joined
Oct 1, 2021
Messages
261
Your right there was a typo so I fixed that and a new error "The command or action 'SelectObject' isn't available now"
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:26
Joined
Oct 29, 2018
Messages
21,357
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:

Code:
?tdf.Name
Make sure the result is a table name that actually exist in the backup db.
 

Valentine

Member
Local time
Today, 08:26
Joined
Oct 1, 2021
Messages
261
When you get the error, click on the Debug button and then in the Immediate Window, enter the following:

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

Member
Local time
Today, 08:26
Joined
Oct 1, 2021
Messages
261
Here is what i have right now..
Code:
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

I’m here to help
Staff member
Local time
Today, 05:26
Joined
Oct 29, 2018
Messages
21,357
Here is what i have right now..
Code:
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

Member
Local time
Today, 08:26
Joined
Oct 1, 2021
Messages
261
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
 

Users who are viewing this thread

Top Bottom