Using VBA? [My THINKING] to LINK a BE (1 Viewer)

[mloucel]

Hello All:

I have a DB that has 2 different sides [now].
- 2 different Offices that must have their own data.
- The BE is 100% similar to each other except for the data contained.
- The FE is Exactly the same for Both, except for the BE

FE for A or B are exactly the SAME except for the name
FE-A and FE-B

BE a and b are exactly the same except for the data.
BE-a and BE-b

My Problem:
How can I SOMEHOW..
Work on my Test DB with a fictional BE [which mimics the production one, up to the path exactly],
then when I move the FE to the production site be able to use some VBA code to re-link the correct BE for the EU.

I have created inside my DB [FE] a settings Table, in that settings table, I am able to locally thru the admin menu enable a Yes/No check mark on ONLY 1 office.
Which I use to display a label on the SPLASH/LOGIN and MENU forms to display the correct Office Name, and is working fine.
THAT IS AS FAR AS I HAVE GONE.

My thought is:

- IF I am able to delete all linked tables in my FE.
- Then provide the End User an updated version of my FE, 1 per-Office
- When The EU opens the NEW updated FE [either FE-A for some and/or FE-B for others]
- The NEW updated FE checks the local settings Table and see which office I previously checked mark.
- Here is MY THOUGHT.. Create some VBA code that re-links the correct BE based on that check mark THE FIRST RUN so that the EU can continue.
-- since is the first time running the FE-A or FE-B will NOT have any linked tables, so on the first run the program must link the tables.
-- Of course there I will add an extra record called LinkedTables, if is True that means the tables are now linked nothing to do, VBA will simply continue, if it is false then Link the tables using VBA

I am not sure if I explain myself what I want to do, but right now I have to link manually and finally distribute the correct FE so that each EU updates their FE with all the Corrections/Updates I made.

I hate to delete all links, then Re-Link all tables,
then save the new corrected/updated FE and distribute.

- I now have 2 exact FE just named FE_A and FE_B
- I Work and update / correct "A" then once that is corrected/Updated I compile and save it as FE_A
== Now I use the same FE-A, delete all links and re-link using BE_b
Compile again the FE but now the new compiled version will be saved as FE_b
Now I have 2 Different files:
FE-A -- Pointing to BE-a
FE-B -- Pointing to BE-b
and is working perfectly.

Since in my test area the BE contains just sample data I created my own sample BE files a and b.
Once the FE is in the EU of course it automatically picks the correct BE, because the Data I linked in my test DB is an exact replica, except for the data.
YES IS WORKING 100%

But the question remains.
Can I simply delete all the linked tables, change LinkedTables in my local settings to FALSE, Change OfficeNumber to 1 or 2 then VBA code to re-link the correct BE using the settings table?

If LinkedTables = False then
    MsgBox "Please wait while maintenance is performed" & vbNewLine _
        & "this may take a couple of minutes" & vbNewLine _
        & "The program will continue after is done" & vbNewLine _
        & "--> DO NOT TURN OFF THE COMPUTER <--"
    search for OfficeNumber
    Select Case OfficeNumber
        Case 1
            Link Tables BE-A
        Case 2
            Link Tables BE-B
    End Select
    LinkedTables=True
endif

Is this Possible?

Thanks
Maurice.

This code of course will be added to the SPLASH screen

 

[The_Doc_Man]

Maurice, look in the "Similar Threads" section to see if any of those articles helps.

 

[mloucel]

Maurice, look in the "Similar Threads" section to see if any of those articles helps.

I did search:
" link BE with VBA "
No luck, I guess I don't know how to correctly search, sorry Doc and thanks, I will do it again maybe different wording.

 

[DHookom]

I would not delete any linked tables. Instead simply update the connection property using code found readily on the web.

Another option I have used is to create a mapped drive and folder structure to mimic a customer environment. If their back-end is on a an “L” drive, I will map a folder as a virtual L drive and then create the appropriate folders in the L drive. I can then link the FE to this mapped BE and distribute it to my customer.

 

[The_Doc_Man]

Here's a suggested article

Dynamically relink Access tables - Split Database

NO HELP REQUIRED TODAY, IM JUST PROVIDING INFORMATION SO THAT OTHERS CAN USE. I have a split database with the tables in an Access database (2013) as the backend and all the forms and queries in another for the front end. The system will be distributed to various users on different setups for...

www.access-programmers.co.uk

 

[mloucel]

I would not delete any linked tables. Instead simply update the connection property using code found readily on the web.

Another option I have used is to create a mapped drive and folder structure to mimic a customer environment. If their back-end is on a an “L” drive, I will map a folder as a virtual L drive and then create the appropriate folders in the L drive. I can then link the FE to this mapped BE and distribute it to my customer.

I'll look for that code.. thanks.
And yes that is exactly what I have:
From Post #1:
Work on my Test DB with a fictional BE [which mimics the production one, up to the path exactly],

Thank you.

 

[mloucel]

Here's a suggested article

Dynamically relink Access tables - Split Database

NO HELP REQUIRED TODAY, IM JUST PROVIDING INFORMATION SO THAT OTHERS CAN USE. I have a split database with the tables in an Access database (2013) as the backend and all the forms and queries in another for the front end. The system will be distributed to various users on different setups for...

www.access-programmers.co.uk

Thanks Doc, that is SORT OF what I want to do, the code assumes you have 1 BE and and when the EU uses the new FE re-links to the BE in that environment (or so I think).

In my case I have 2 different FE files, I need to somehow send to the code which office I want to relink, depending on the FE if it is Officice1 or Office2 or possibly in the future even Office3, then I will have to create 3 FE files.

I will try with that code and see if I am able to tweak it for my purposes, but there's a lot I need to learn first there is code I have never seen.

Thank you so much for your help.
Maurice.

 

[DHookom]

I'll look for that code.. thanks.
And yes that is exactly what I have:
From Post #1:
Work on my Test DB with a fictional BE [which mimics the production one, up to the path exactly],

Thank you.

I wasn’t sure what you meant by “up to the path”. If you mean matches the path exactly, why would you need to do anything with delinking? If the connection paths are the same, just deploy the new FE.

 

[mloucel]

I wasn’t sure what you meant by “up to the path”. If you mean matches the path exactly, why would you need to do anything with delinking? If the connection paths are the same, just deploy the new FE.

Because the BE is different.

FE-A uses BE-a
FE-B uses BE-b

This done in my LAB, all paths are a mimic of the production.
FE is exactly the same program, when I save (Compile "Make ACCDE") is when I do the work:
If I am working with BE-a
Just Compile "Make ACCDE" and save as FE-A

to make the FE-B:
I delete all the tables
Re-link this time using BE-b
Compile "Make ACCDE" and save as FE-B

Hope this helps.

 

[DHookom]

Aren’t all the BEs consistent in table structures with the only difference the data? If so, there no need to relinquish as long as the paths are the same.

 

[Albert D. Kallal]

Your "use" case is actually quite common.
So, on program startup, you check if re-linking is required.
But, how do you change what location, so correct linking is to occur?

Well, you have several choices.

One choice I used in the past was having a local table in the front end. It had several rows of data, (with the correct and desired back end location).

So, when working, I just opened that table, and checked that row (it had a true/false column).

So, I would thus/then check box the row with the local (developer path link), and run my re-link code.

But, to deploy live?
Well, I would open up my re-link table, check box the production link - say for site "A".
I then compile to a accDE - (make sure you hold down shift key - since you can NOT link to a path name that does not exist on your dev computer).

At this point, you can now distribute that compiled FE to all folks for "site A".

You can then open up the accDE, and now select the site B link, and then simply distribute the new updated FE to all folks for site B.

However, why limit the design to say 3 sites (developer, site A, and site B).

Why not adopt a design in which you can have un-limited sites?

So, a REALLY great solution?
Include a .text file (I actually used a .ini file), and that .ini/.txt file has the path name you wish to link to.....

So, on startup code, you simple read that .txt file, get the path to BE, and hten your re-link code can check/test if that's the current path for linked tables, and if not, then re-link.

I STRONG suggest you do NOT delete the table links - no need to do so.

And I STRONG suggest you do NOT re-link every time on startup - it's not required......

So, for a few known locations and sites? then I suggest just creating a table in the front end (not the back end). You can thus have 2-5 rows, each one with a different path name. So, you have ID, LocationName, PathName, Active

So, you can thus just add rows, or edit rows to set the path name, and which row is Active.
Then your re-link code on startup can read this table, and get/grab the path name, check say the first linked table, and if path names don't match, then you re-link all tables.....

And, if the path name is to be un-known ahead of time? Or there are many different locations? Then I suggest the text or .ini file approach.
That way, you can roll out a new front end, but NOT include the .ini or text file in that update, and thus all workstations will correctly read that .txt file on startup, and again, do a check if re-linking is required.....

R
Albert

 

[mloucel]

Aren’t all the BEs consistent in table structures with the only difference the data? If so, there no need to relinquish as long as the paths are the same.

Yes BE's are consistent, the main issue is that the NAMES are different.
BE-a and BE-b

FE-A is linked to BE-a
FE-B is linked to BE-b

FE is the base program but in order to get the data from either office I have to re-link the BE with the corresponding office.

I usually work with FE-A and BE-a, do all the changes (FE) once they are done MAKE AN ACCDE and save as FE-B

Now to create FE-B I delete the linked tables from BE-a, link the BE-b, MAKE AN ACCDE and save it as FE-B

Hope this helps.

 

[isladogs]

You only need one FE both for development purposes and for distribution.
As Albert said, this can be linked to an unlimited number of BE files.

The connection strings can be set using a local table or an external text file.
As the developer, I preferred the local table approach but both work equally well.

When distributing the FE to each company via my website I first remove all the links.
The program admin downloads the file and on opening is prompted to specify their organisation.
In fact, I automate this as well by having a separate configuration file in the same folder as the FE and which stores the relevant paths/ passwords for their organisation. This data is automatically retrieved from the configuration file on first opening the unlinked FE.
This then triggers relinking to the relevant BE (or multiple BEs) which happens automatically.
I used this approach for both Access BEs and more often SQL Server BEs

The program admin then distributes the now relinked FE to all users.
The process is simple to administer and 100% reliable.

 

[mloucel]

You only need one FE both for development purposes and for distribution.
As Albert said, this can be linked to an unlimited number of BE files.

The connection strings can be set using a local table or an external text file.
As the developer, I preferred the local table approach but both work equally well.

When distributing the FE to each company via my website I first remove all the links.
The program admin downloads the file and on opening is prompted to specify their organisation.
In fact, I automate this as well by having a separate configuration file in the same folder as the FE and which stores the relevant paths/ passwords for their organisation. This data is automatically retrieved from the configuration file on first opening the unlinked FE.
This then triggers relinking to the relevant BE (or multiple BEs) which happens automatically.
I used this approach for both Access BEs and more often SQL Server BEs

The program admin then distributes the now relinked FE to all users.
The process is simple to administer and 100% reliable.

Thanks, I understand your logic, 2 points:
1) Managers in the office want to have access to BOTH data so the reason I created 2 programs.
--- I fully understand that thru the way you explain this it could be possible maybe at the splash level or somewhere in between to have somehow a dropdown (I imagine) for managers where they can choose which BE they want to work with.

2) My only problem is that I have NO IDEA whatsoever how to accomplish that or how to code it, it is beyond my coding abilities, since I've never done anything like it, so when you and Albert kindly explain to me about text files, I have no idea.
Also, I do have a LOCAL table called settings (I actually steal the idea from 1 of your example files).

As you said, my idea as well was to distribute the FE without the LINKED BE, and code on the settings table enough data so that thru VBA I could automatically relink the correct BE instead of leaving that to the EU, better and more practical and not leaving anything to chance.

But I have no idea what to do, I'm sorry if I sound too frustrating, but I am learning tricks as I progress with my program, for a long time my method has been working but I thought it will be great if I can automate the relinking of the BE instead of me having to do it manually, no matter what at least for the managers I need 2 different programs, my boss wants to open both at the same time [WORTHLESS] if you ask me since data is different, but I guess if that is what she wants, that is what she gets.

Please accept my apologies, I am really trying hard.

Maurice.

 

[June7]

- 2 different Offices that must have their own data.
- The BE is 100% similar to each other except for the data contained.

Two offices in same org, same network? What does "100% similar" mean? If same table structure why not 1 BE and apply filters appropriate for user? Can even have multiple instances of a form open with different sets of data for side-by-side viewing.

Controlling user options for data interaction is a matter of having a Users table with details about user parameters. If Joe is in office A and should see only that data, then apply appropriate filters to forms and reports (of course, there must be a field identifying office record belongs to). Can either require user to "login" or code can automatically identify user from their Windows login username (Environ("USERNAME") can do that) and then do a lookup to the Users table (I do this, with process that if username is not in Users table then they are prompted for some input and create record - it is assumed if they can login to network they are legitimate users). Code can manage what actions are available to user (Joe can't see office B data but Lisa as manager can so controls on forms can be hidden if appropriate).

Relinking tables with VBA and user login are both common topics.
Another source for linking https://www.microsoft.com/en-us/mic...les/?msockid=30d2830e61b069f803b396ff600a68a3
And for login https://www.accessforums.net/showthread.php?t=23585

Consider SQLServer for backend (I use Express version which is free) https://learn.microsoft.com/en-us/s...rity/row-level-security?view=sql-server-ver17

You have to decide where/how to direct resources (coding for 1FE/1BE vs multiple with relinking for distribution of updates). For now, linking code is probably easier path for you.

 

[mloucel]

Two offices in same org, same network? What does "100% similar" mean? If same table structure why not 1 BE and apply filters appropriate for user? Can even have multiple instances of a form open with different sets of data for side-by-side viewing.

Controlling user options for data interaction is a matter of having a Users table with details about user parameters. If Joe is in office A and should see only that data, then apply appropriate filters to forms and reports (of course, there must be a field identifying office record belongs to). Can either require user to "login" or code can automatically identify user from their Windows login username (Environ("USERNAME") can do that) and then do a lookup to the Users table (I do this, with process that if username is not in Users table then they are prompted for some input and create record - it is assumed if they can login to network they are legitimate users). Code can manage what actions are available to user (Joe can't see office B data but Lisa as manager can so controls on forms can be hidden if appropriate).

Relinking tables with VBA and user login are both common topics.
Another source for linking https://www.microsoft.com/en-us/mic...les/?msockid=30d2830e61b069f803b396ff600a68a3
And for login https://www.accessforums.net/showthread.php?t=23585

Consider SQLServer for backend (I use Express version which is free) https://learn.microsoft.com/en-us/s...rity/row-level-security?view=sql-server-ver17

You have to decide where/how to direct resources (coding for 1FE/1BE vs multiple with relinking for distribution of updates). For now, linking code is probably easier path for you.

-- Two offices in same org, same network? What does "100% similar" mean?
-Yes same Network and data files are exactly structurally the same except for the data.

-- If same table structure why not 1 BE and apply filters appropriate for user?
Due to administration restrictions databases MUST be kept separate, each office must have its own set of data, all the BE are housed in the same folder but each is accessed by each different office, only managers have access to the 3 offices and can see the data of each office.

-- Controlling user options
- Thanks for the suggestion but as you can see, I am limited, and per management the BE files must be separate, so if an EU moves from one office to the other, that user will have 2 or 3 accounts, one per office.

--Your First link uses pretty much the same code as I have shown before, but here it explains something is not in the other one, I believe I can follow this one, I have seen the code and with instructions I believe is exactly what I need.

-- I am using a login screen, it works pretty good, just what I need and nothing too fancy.

-- SQL will be a dream, but first I need to learn SQL and I am barely getting Access in my head, but yes I know eventually I will have to.

Question: The BE data is now password protected, thanks to @theDBguy and @isladogs I was able to accomplish that perfectly, but now that I am going to try, to do the re-link using VBA how and where can I add the password? so that it does not ask for it, it will defy it's purpose.

Thank you kindly.

 

[June7]

Just to nitpick, you are already learning SQL. SQL is a language and Access uses it to build queries. When you use query designer to create a query, it generates SQL. Switch to SQL view to see the statement.
SQLServer is a database application. And yes, there is a bit of a learning curve but I managed to install it on my laptop to use as localhost database just for myself to learn. I tried MySQL and gave up (twice).
All 3 of these db platforms and many others use a variation of SQL.

However, since you are sticking with 2 BEs, probably don't need to leave Access - unless your BEs grow to more than 2GB. SQLServer Express has 10GB limit.

Password protection - the answer is in the cloud https://www.access-programmers.co.uk/forums/threads/relink-to-password-protected-be.302801/

 

[isladogs]

Thanks, I understand your logic, 2 points:
1) Managers in the office want to have access to BOTH data so the reason I created 2 programs.
--- I fully understand that thru the way you explain this it could be possible maybe at the splash level or somewhere in between to have somehow a dropdown (I imagine) for managers where they can choose which BE they want to work with.

2) My only problem is that I have NO IDEA whatsoever how to accomplish that or how to code it, it is beyond my coding abilities, since I've never done anything like it, so when you and Albert kindly explain to me about text files, I have no idea.
Also, I do have a LOCAL table called settings (I actually steal the idea from 1 of your example files).

As you said, my idea as well was to distribute the FE without the LINKED BE, and code on the settings table enough data so that thru VBA I could automatically relink the correct BE instead of leaving that to the EU, better and more practical and not leaving anything to chance.

But I have no idea what to do, I'm sorry if I sound too frustrating, but I am learning tricks as I progress with my program, for a long time my method has been working but I thought it will be great if I can automate the relinking of the BE instead of me having to do it manually, no matter what at least for the managers I need 2 different programs, my boss wants to open both at the same time [WORTHLESS] if you ask me since data is different, but I guess if that is what she wants, that is what she gets.

Please accept my apologies, I am really trying hard.

Maurice.

You can provide the same ability to swop between BE files as you have as the developer.
However, only only one dataset would (or should) ever be connected at any time.

As an example, here is a relink form from one of my an old databases for schools (circa 2010).
You can tell how old it is as it includes links to the now deprecated web databases!

This particular app had up to 8 BE files used at once (a SQL Server & 6 Access) though not all were always used.
Most of my schools apps had fewer BE files

The connection strings for each BE can be edited (by admin users with requisite permissions) from another form:

As the developer, I used the drop down to swop between the datasets for each school (both real data & practice datasets) then clicked relink all tables. This removed all existing links and then looped replaced with those with all linked tables for the selected school.
The process typically took about 30 seconds over the network with anything up to 400+ linked tables.

As already mentioned, the version distributed to clients via my website had all links already removed first.
The info for the BE files on this form came from the configuration BE file (the first Access BE in the list above) which was populated on first time installation. Clients only had access to their own data.

Creating this system took many hours of development work. Its not difficult but obviously needed to be tested VERY thoroughly as it was used both for first time configuration with new clients and with all new FE update versions.

If you don't feel capable (or have time) to create something similar yourself, then I strongly recommend using one of the commercially available relinker apps. I've never had a reason to use it but I know that the J Street Relinker app is very highly regarded. The J Street company is run by long-time Access MVP, Armen Stein.
Alternatively, you can contact me privately to discuss doing this for you on a contract basis (but do look at what's already available first).

 

[Ken Sheridan]

For many years I've included code in front end databases' unbound opening form which checks to determine if the current links are valid. I have a number of different versions of this for different contexts. The attached zip archive includes a RefeshLinksSingle_07.accdb file which illustrates the simplest version, which assumes that the back end is a single Access .accdb file.

To use this in your database you'd add the code from the demo to your front end's opening form. This must either be unbound or bound to a local table in the front end. If you are currently using a form bound to a table in the back end as the opening form, then you'd need to add a little unbound form similar to that in the demo, with a Continue button or similar to open the current bound opening form.

You'd also need to add the BackEndLocation table, the frmUpdate_Links form and the basBrowse module to your front end.

When a front end file with invalid links is opened, the frmUpdate_Links form will open at start-up, allowing the user to browse to a back end file to refresh the links. This can be tested in the demo by making a copy of any .accdb file, and adding a linked table from it, using the frmAddLinkedTable form. Then move the linked file to a new location and open the demo again. To change from one back end to another the frmUpdate_links file can be opened at any time, provided none of the linked tables are currently in use.