'open external db
Set app = CreateObject("Access.Application")
Set db = app.DBEngine.OpenDatabase(strDB)
'open external db
Set app = CreateObject("Access.Application")
Set db = app.DBEngine.OpenDatabase(strDB)
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.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.
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
@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.Yes but @theDBguy do you know what ype of object a "6" is?
Open your MSysObjects table and check there.@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?
Do I have to put each table's name in here or should this go through them automatically?Code:app.DoCmd.Select acTable, .Name, True
It's all automatic. All you have to provide is the name of the backup ACCDB file.Do I have to put each table's name in here or should this go through them automatically?
Not sure if you had a typo. I gave you "SelectObject," and you showed me only "Select."im getting an error on that line that states "Object doesn't support this property or method"
When you get the error, click on the Debug button and then in the Immediate Window, enter the following:Your right there was a typo so I fixed that and a new error "The command or action 'SelectObject' isn't available now"
?tdf.Name
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.When you get the error, click on the Debug button and then in the Immediate Window, enter the following:
Make sure the result is a table name that actually exist in the backup db.Code:?tdf.Name
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?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