Hmm, interesting. I can't imagine why you would need to back up the FE. You should always keep a "master copy" of the FE in a safe place, and use it to refresh all other copies of the users' FEs. Instead, what you would typically need to backup is the BE or the data.
Hi. Thanks for the clarification. As I was saying though, your current backup process keeps making a backup of the FE objects, which shouldn't have changed in between backups. Maybe it's just the convenience aspect that's why you're doing it this way, but I would probably recommend just exporting the linked tables into Excel files (or event Access tables into an empty database file) to backup the data only. This way, you won't need to waste any space storing the FE objects (forms/reports/etc.) and also don't have to figure out how to unlink the tables in the backup file. I am not saying it's not possible, I am just offering a potentially (in my perspective) simpler approach for creating a local backup copy of your data.
Hi. Like I said, if you are just concerned about the data, you should be able to export them out of the front end into either Excel or Text (CSV) or another Access file. That would be easier to code than the approach you're trying now (although your approach is not impossible).
LOL. I guess you're stuck with it.
Hi. I haven't started with my experiments yet, but I'd like to clarify the above statement. Typically, you would use code to automate manual tasks. So, to make sure I completely understand what you're trying to do, could you please explain to us the manual steps you would use to create a backup copy of your company's data from SharePoint?
your steps are accurate, then i would go to each table and right click and select "convert to local" we have roughly 20 - 30 tables that i have to make local for the backup to be finished. 1 or 2 of them roughly take like 5 min each but majority are almost instantaneous. After the tables are local just exit the database and go home for the weekend.
so in doing this would this pick up the most recent copy of the DB or just a new DB? I am trying to make the button copy the FE and then open that specific copy to make the tables local.Welcome aboard
You would define a db object and assign it to the database you want to open rather than Currentdb Once you have that db object, you can manipulate tabledef objects.
However, why are you doing this? Typically when you want to replace the FE with an updated one, you take YOUR development copy which should be linked to a test BE and relink it to the production BE before moving it to the master folder on the server. You should be using either a batch file or a special database to distribute the updated FE to each user as he opens the app. I cannot envision a scenario where you would want to reach out and update some user's copy of the FE this way.
If you tell us more about what you are trying to accomplish, we can offer advice.
Okay, thanks for the clarification. Considering the ultimate goal is to have an exact duplicate of the FE with local copies of the linked SharePoint tables, there are several ways to go about getting it done. The way you want to do it, in my humble opinion, is the "most expensive" approach, because you will continually recreate existing (no changes) objects, which I would consider not necessary and could be deferred later when you actually have to use the backup copy of your database.
Public Function SP2Local(strDB As String) As Boolean
'thedbguy@gmail.com
'10/27/2021
'converts SharePoint linked tables to local tables
'pass the complete filepath and filename of the backup db to this function
'for example: SP2Local "C:\BackupFolder\BackupFilename.accdb"
Dim app As Object
Dim db As DAO.Database
Dim tdf As DAO.TableDef
'open external db
Set app = CreateObject("Access.Application")
Set db = app.DBEngine.OpenDatabase(strDB)
'convert linked tables
For Each tdf In db.TableDefs
With tdf
If InStr(.Connect, "ACEWSS") > 0 Then
app.DoCmd.SelectObject acTable, .Name, True
app.DoCmd.RunCommand acCmdConvertLinkedTableToLocal
End If
End With
Next
MsgBox "All done!", vbInformation, "Info"
'cleanup
db.Close
Set db = Nothing
Set app = Nothing
End FunctionThat was a good idea!
I checked my sample file and it had Type = 6 also.
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.
