May 7 2012, 11:18 AM
I have a database that I have just split. The Back End is on a shared folder server. I and my partner are using the front ends on 2 seperate machines. I have a form with several buttons that are used to import different data from different excel files to various tables. The saved imports that we both have are specific to our own individual profiles, so the path to the excel files are going to be different simply because the path includes our profile name. Even if we had the excel files in the same folder as the front end of our database, I believe the path would be different.
So, if I made any changes to my front end, say I created a new report that we both needed to be able to generate, and I e-mailed my front end to him so that he could have the latest version of the front end with the new report, his saved imports would not be mapped correctly and when he clicked on a button to import the data, Access would not be able to find the Excel file.
Is there anything that we can do to make the path be the same on both of our machines, say include something like %profile% or some kind of a wild card or something that we can use to get around this?
I know that 2 options would be to duplicate the import form and have one for each of us, and have all of my saved imports and all of his saved imports stored on the front end files, but that doesn't seem very efficient. Also, what if I leave the company and someone new comes in and isn't Access savy. The other option would be that I can make changes to my front end and then kick my buddy off of his machine and make the same updates on my buddies front end, all while preserving our saved imports.
Did I confuse everyone enough? What is the best way to deal with this issue?
May 7 2012, 11:54 AM
This may be a bit of overkill ...
I faced a similar situation and got around it (KLUDGE WARNING!) by creating what I described as a "middle-end", consisting of reports and their related queries. Those reports and queries linked to the back-end for data, while I referenced this "middle-end" from the front-end.
This way, the front-end didn't change. New reports in the "middle-end" were available to all front-end users.
(like I said, KLUDGE ALERT!)
May 7 2012, 11:56 AM
There are a number of ways to accomplish what you want to do. The first easy one that comes to mind would be to put a combo box on the form that lists your and your partner's import path. Then, just select the one that applies to you.
May 7 2012, 02:19 PM
I think I see what you are saying. That would accomplish kind of the same thing as just copying my "Import Form" and mapping my saved imports to my form buttons, and my partners saved imports to his form buttons. But that doesn't solve the problem of future employees. You would have to keep repeating that process for each new user. I was hoping that there was some kind of a "variable" for the profile name part of the path to our files. Then I could specify that each user puts their copy of the access front end in the same folder, say My Documents/Database. But right now I have to deal with C:\documents and settings\profile name\my documents\database or whatever it is. Do you have any other ideas?
May 7 2012, 02:22 PM
I follow what you are saying, but I am not sure how to go about it. Are you saying that the middle end is where you would add reports, queries, etc. And the Front End is still distributed to the users? How would I go about connecting the front end to the middle end?
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here