Open a new database from inside access (1 Viewer)

Valentine

Member
Local time
Yesterday, 23:50
Joined
Oct 1, 2021
Messages
167
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:50
Joined
Feb 19, 2002
Messages
35,834
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.
 

Isaac

Lifelong Learner
Local time
Yesterday, 20:50
Joined
Mar 14, 2017
Messages
6,270
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
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:50
Joined
Oct 29, 2018
Messages
18,742
Hi. Welcome to AWF!

One simple approach to open a file is by using the FollowHyperlink method. Do you know the full filepath of the file you want to open?
 

Valentine

Member
Local time
Yesterday, 23:50
Joined
Oct 1, 2021
Messages
167
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.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:50
Joined
Oct 29, 2018
Messages
18,742
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...

 

Valentine

Member
Local time
Yesterday, 23:50
Joined
Oct 1, 2021
Messages
167
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.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:50
Joined
Oct 29, 2018
Messages
18,742
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:50
Joined
Feb 19, 2002
Messages
35,834
Isn't your SharePoint data backed up by the IT people? You might want to ask them about the procedure to get stuff back.
 

Valentine

Member
Local time
Yesterday, 23:50
Joined
Oct 1, 2021
Messages
167
The sharepoint part of this is run by a different section and we would just like to have our own backup incase things hit the fan.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:50
Joined
Oct 29, 2018
Messages
18,742
The sharepoint part of this is run by a different section and we would just like to have our own backup incase things hit the fan.
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).
 

Valentine

Member
Local time
Yesterday, 23:50
Joined
Oct 1, 2021
Messages
167
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.......
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:50
Joined
Oct 29, 2018
Messages
18,742
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.......
LOL. I guess you're stuck with it. :)

I'll have to run some tests to see how we can make that happen. I'll let you know what I find.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:50
Joined
Oct 29, 2018
Messages
18,742
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:
  1. Right-click on the FE and select Copy
  2. Navigate to the Backup folder, right-click and select Paste
  3. Open the newly copied FE
  4. and then what?...
I am trying to see what is the step to make the online data become local data. Thanks!
 

Valentine

Member
Local time
Yesterday, 23:50
Joined
Oct 1, 2021
Messages
167
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:
  1. Right-click on the FE and select Copy
  2. Navigate to the Backup folder, right-click and select Paste
  3. Open the newly copied FE
  4. 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.
 

Valentine

Member
Local time
Yesterday, 23:50
Joined
Oct 1, 2021
Messages
167
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.
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.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:50
Joined
Oct 29, 2018
Messages
18,742
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
Hope that helps...
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:50
Joined
Feb 19, 2002
Messages
35,834
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

ObjType = dLookup("Type", "MSysObjects", [Name] = 'sometablename'")

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.
Code:
Type    TypeDesc
-32768    Form
-32766    Macro
-32764    Reports
-32761    Module
-32758    Users
-32757    Database Document
-32756    Data Access Pages
1    Table - Local Access Tables
2    Access Object - Database
3    Access Object - Containers
4    Table - Linked ODBC Tables
5    Queries
6    Table - Linked Tables or Files
8    Relationships
9    Constraints
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:50
Joined
Oct 29, 2018
Messages
18,742
Regardless, to make this SAFER and to keep people from updating the backups by accident, you should make a couple of changes.
That was a good idea!

I would appreciate someone reporting the Type value if they check so I can update my notes.
I checked my sample file and it had Type = 6 also.

Cheers!
 

Users who are viewing this thread

Top Bottom