Hello everyone I am brand new to this forum and have a question that has plagued me for a few days now.
I created a cmd button to do backup and i want to open one of the copied database front ends to unlink the tables but can't seem to figure out how to open the specific new database.
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.
dim accessapp as object
set accessapp = createobject("access.application")
accessapp.opencurrentdatabase "path to file"
this has the benefit of not opening the other file from 'within' the same instance of access where the code is running, which will generally complicate things for you, depending on what you want to do with the other file
I am trying to perform a complete backup with 1 button push on our operational FE. I have the button creating a copy and putting it into our backup folder i want the button to also open that specific FE non visible and unlink all the tables so finish the backup process per our procedures.
I am trying to perform a complete backup with 1 button push on our operational FE. I have the button creating a copy and putting it into our backup folder i want the button to also open that specific FE non visible and unlink all the tables so finish the backup process per our procedures.
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.
Here's one approach I use to backup the BE. Why are you backing up the FEs? Just curious...
Currently we are linked to Sharepoint so all of our backend is online so if i just copy the FE and unlink the tables it creates its own standalone backup for our information.
Currently we are linked to Sharepoint so all of our backend is online so if i just copy the FE and unlink the tables it creates its own standalone backup for our information.
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).
So i brought up that we should be doing it your way but was told that since the way that was first asked is an actual viable solution they would rather we go the harder route.......
So i brought up that we should be doing it your way but was told that since the way that was first asked is an actual viable solution they would rather we go the harder route.......
Currently we are linked to Sharepoint so all of our backend is online so if i just copy the FE and unlink the tables it creates its own standalone backup for our information.
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?
I imagine you might say something like this:
Right-click on the FE and select Copy
Navigate to the Backup folder, right-click and select Paste
Open the newly copied FE
and then what?...
I am trying to see what is the step to make the online data become local data. Thanks!
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?
I imagine you might say something like this:
Right-click on the FE and select Copy
Navigate to the Backup folder, right-click and select Paste
Open the newly copied FE
and then what?...
I am trying to see what is the step to make the online data become local data. Thanks!
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.
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.
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.
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.
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.
Still, to get the same result, you can also use other approaches where you only perform the table conversion process and still end up with a full backup copy of the database with all the objects and local tables together. These approaches are "less expensive" than the first approach, but I won't consider them optimal. However, as you said, the marching orders from the higher ups is not to use the optimal approach, but just to get it done.
So, in the spirit of just automating what you already started with, I came up with the following:
Code:
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 Function
What you are doing is very Excel-Like and therefore very dangerous.
I understand the desire for backups. Point in time backups are especially difficult to obtain. Is this what you are looking for?
Regardless, to make this SAFER and to keep people from updating the backups by accident, you should make a couple of changes.
1. When the database opens, check one of the normally linked tables and determine if it is local rather than linked. You can do that easily enough by creating a dLookup() that gets data from MSysObjects
If ObjType = 1, the table is local. I'm not sure what the value will be for SharePoint. It might be 4. I don't have any way of checking because I have no current apps linked to SharePoint. I would appreciate someone reporting the Type value if they check so I can update my notes.
2. If the tables are local, set a tempvar named dbBackup = True so you know that you are working in a backup database.
3. Change every form and subform capable of updating to check this tempvar when the form loads and set the form/subform's
Me.AllowEdits = False
Me.AllowUpdates = False
Me.AllowDeletes = False
This will keep people from accidentally updating the backup.
Just FYI, here's my list of Object types. If you have others not in this list, please reply with the Type and whatever you know about the object itself.