Sharing a database with a friend for their use. Suggestions? (split, local drive, etc.) (1 Viewer)

[GoVikings]

Hi there, newbie / 1st time post.

I have a database using Access 2016 that I would like to give a friend a copy to use. It organizes different customers and their invoices. We do not need to share data (i.e. no common back-end), so everything can remain in the same local folder. I would give him my database for him to install on his on computer folder.

Objectives: I want to split the database so I can update the front end if needed in the future. I want to prevent unauthorized copying of the database for someone else's use.

I was thinking of splitting the database, then using a solution here to use vba code to re-link to the local folder each time (which I found at stackoverflow) though I only know how to cut and paste (screenshot below). This way, he could keep both front and back end files in the same folder, wherever he wishes. Or is there a better way to give him the 2 files and keep them linked, without more work for me?

I would compile and give him the accde version to prevent modifying/accessing the structure of the forms/reports. I would keep his identifying information (company address, his name on the invoices, etc.) in the front end so that it could not be modified and given to another person/company to use.

Thoughts? I'm ready to be blasted with criticism Thanks

 

[jdraw]

Seems well thought out in my view. Much more thought and consideration than most newbie or first time posters.

 

[Pat Hartman]

I agree. Split is the right decision. Using code to find the BE in the same folder as the FE works just fine for this type of application. I don't use that code because my apps always share a common BE but using code to help the user is always a good idea.

 

[GoVikings]

Thanks jdraw & Pat Hartman! It seems to be going well so far, haven't quite ventured into the coding part, but splitting and compiling seems to achieve my objectives so far. Without the code so far, I am forced to manual re-link the FE and BE after first copying to the new directory. I can probably live with that, but maybe I'll tinker and use the code above. Fun!

 

[Gasman]

If you put the db's in the same place as they are on your computer, no re-linking required.

 

[GoVikings]

If you put the db's in the same place as they are on your computer, no re-linking required.

Hi, I see. I'm curious if that would work on a network terminal. That is, the workplace sets up each terminal with access to your own documents folder, under a user folder name. So mine would be something like C:\users\GoVikings\Documents. I don't think I can get the path names to be the same, if I am unable to save to local the drive in a non-personalized destination like the C:\ root drive or C:\Users\ folder.

 

[Pat Hartman]

What's wrong with the code you posted?

 

[GoVikings]

What's wrong with the code you posted?

Oh, nothing that I know of. Just haven't gotten to that step (and I guess a bit intimidated by code!) Actually, I have successfully split the database, compiled the FE, and it seems to work well so far! I still have to manually re-link the BE tables when I move it. So next step, the code I guess! If it works, I realize it is a way more robust solution than forcing the user to pick a specific path. I'm done with it for today, but I'll post an update when I work on that last code step. Wish me luck! and thanks again

 

[GoVikings]

Took a crack at incorporating that code. It seems I don't understand how to incorporate it at all, and it feels like it would take more time than I have to learn enough basics to proceed. So I'll have to stop there for now. Thanks again for the replies here!

 

[Gasman]

Hi, I see. I'm curious if that would work on a network terminal. That is, the workplace sets up each terminal with access to your own documents folder, under a user folder name. So mine would be something like C:\users\GoVikings\Documents. I don't think I can get the path names to be the same, if I am unable to save to local the drive in a non-personalized destination like the C:\ root drive or C:\Users\ folder.

I did say the same place?
When I worked in a bank all our various Access DBs were stored in C:\DB and perhaps a subfolder. Users had roaming profiles.
I used the same logic when I had to distribute my DBs.

 

[Edgar_]

Took a crack at incorporating that code. It seems I don't understand how to incorporate it at all, and it feels like it would take more time than I have to learn enough basics to proceed. So I'll have to stop there for now. Thanks again for the replies here!

Maybe try this, add it to a module, call it any way you want from any where.

Sub relink()
    Dim item As Variant
    For Each item In CurrentDb.TableDefs
        If InStr(item.Name, "MSys") = 0 Then
            item.Connect = ";DATABASE=C:\Your\File\Path\file.accdb"
            item.RefreshLink
        End If
    Next item
End Sub

It might be necessary to take a look at all your tables .Connect properties before that in order to know what you're dealing with, so, run this first and check your immediate window

Sub getPaths()
    Dim counter As Long
    Dim item As Variant
    For Each item In CurrentDb.TableDefs
        If InStr(item.Name, "MSys") = 0 Then
            counter = counter + 1
            Debug.Print item.Name & "----" & Left(item.Connect, 40)
'            Stop
        End If
    Next item
    Debug.Print "total tables: ", counter
End Sub

If you need assistance, let me know.

 

[KitaYama]

Sub relink()
    Dim item As Variant
    For Each item In CurrentDb.TableDefs
        If InStr(item.Name, "MSys") = 0 Then
            item.Connect = ";DATABASE=C:\Your\File\Path\file.accdb"
            item.RefreshLink
        End If
    Next item
End Sub

@Edgar_ I know you're aware o this, but just for the sake of perfection, this method doesn't help that much.
If you're linking the FE for the first time, since there's no table in FE, it does nothing.
Also, during developing stages and new versions, if you add a new table to BE, the above code won't bring it in too. You have to link it manually.

So, I think looping through FE's tableDef is useless.
I tend to loop through BE's tables (Access Database, Sql Server) and check if FE has the same table, If it exists, I edit/refresh its connect string.
If not, I add a new linked table by DoCmd.TransferDatabase TransferType:=acLink....
And at the end delete unnecessary linked tables (if any)

 

[Pat Hartman]

@KitaYama I disagree. The FE was linked to the developer's BE at the time it was developed so It always has tables linked. I would NOT use the path in the linked tables to determine where the BE should be. I would find the path for the FE and then make that the path for the BE. Use CurrentProject.Path to get the path for the FE. Then concatenate the BE name in your link procedure.

Another reason, BE's might be shared by apps and each FE does not necessarily use all the BE tables.

 

[KitaYama]

The FE was linked to the developer's BE at the time it was developed

During developing process, specially in the first stages of designing a database, there are a lot of changes to the structure of the schema. Adding tables, Deleting un-necessary ones. I hate to manually delete or add linked tables to FE. My Relink process do it with one click from Admin tab of the ribbon. Both from Sql server or Access Database.

I would NOT use the path in the linked tables to determine where the BE should be.

I didn't say I'm using the path in linked table to determine where the BE should be. At the start of linking process, BrowseFile opens a dialog, I browse and select the BE. the rest is as I explained above.

I would NOT use the path in the linked tables to determine where the BE should be. I would find the path for the FE and then make that the path for the BE. Use CurrentProject.Path to get the path for the FE. Then concatenate the BE name in your link procedure.

To be honest, I don't understand this section.

Another reason, BE's might be shared by apps and each FE does not necessarily use all the BE tables.

Yes, you are correct. I had this problem before. fortunately in my case, we have a policy in the naming of tables. So adding an IF solved it all.
Or if the un-necessary tables are few, a string variable can be used to keep a list. I think it's still better (for me) than going through the hassle of adding/deleting linked tables manually.
Of course, different persons, different tastes.

May I ask about the steps you take when you add new tables to BE (even if it's rare) or delete them?.
Do you add/Delete them manually?

thanks

 

[KitaYama]

I'm at home and don't have access to my work PC.

It's a simplified version of what I meant (air code, not tested.)

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
   
    BE = BrowseFile("Select Back End Database ....", CurrentProject.path, "db", False)
    If BE = "" Then Exit Sub
    Set dbs = OpenDatabase(BE)
    Set db = CurrentDb
   
    ProgressBar.iCount = CInt(dbs.TableDefs.Count)
    ProgressBar.SetupProgressBar
   
    '** loop through table in back end
    For Each tdf In dbs.TableDefs
        If Left(tdf.Name, 3) = "tbl" Or IsNumeric(tdf.Name) Then
            ProgressBar.ShowThisMsg = "Re-Linking ....." & tdf.Name
            If DCount("[Name]", "MSysObjects", "[Name] = '" & tdf.Name & "'") = 1 Then
                ' table exists. Refresh the link
                db.TableDefs(tdf.Name).Connect = ";Database=" + BE
                db.TableDefs(tdf.Name).RefreshLink
            Else
                ' table doesn't exits. create a new linked table
                AddLinkedTable BE, tdf.Name
            End If
            LinkedTables = LinkedTables & tdf.Name & "|"
        End If
        ProgressBar.UpdateProgressBar
        DoEvents
    Next

I have another sub in case the BE is sql server.
The code is different, but the base is the same.

 

[Pat Hartman]

I hate to manually delete or add linked tables to FE.

I didn't say to do that. All I said was that the FE would always be linked to BE tables and to change that, I implied but didn't say - would require deleting the linked tables.

I didn't say I'm using the path in linked table to determine where the BE should be

I didn't say you were. It was the sample code that was doing that.

 

[Edgar_]

I understand the points raised by you guys.

@KitaYama I don't know how the resulting frontend file will look for the OP, but I believe that snippet would work. I added the second snippet so OP could analyze the aforementioned resulting frontend file, but I assume it will have tables.

@Pat Hartman Yes, that snippet could be improved, decoupling the file path could look like this:

Function relink(BENameWithExt As String)
    Dim item As Variant
    For Each item In CurrentDb.TableDefs
        If InStr(item.Name, "MSys") = 0 Then
            item.Connect = ";DATABASE=" & CurrentProject.Path & BENameWithExt
            item.RefreshLink
        End If
    Next item
End Function

With that, the OP could reuse the function in other databases, assuming he backend will live in the same folder as the frontend.

 

[Pat Hartman]

Thanks @Edgar_ I like that version much better especially since it actually conforms to the original request