By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,937 Members | 1,532 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,937 IT Pros & Developers. It's quick & easy.

How to link front end accde to split backend accdb file

P: 57
Hi All,

I have prepared my application in A2010. But only one user at a time can access the application which should not happen.

I read somewhere that splitting the DB will do the work. But no luck.

What I did till now.

-I splitted my appl in front end and Back end.
-Saved front end in accde format.
-locked accdb file

Now, I have some questions:

-I am going to put this app in shared drive. And I read that for more than 1 user to access simultaneously, we need to distribute the copy of front end in their drive(ie. personal drive),is it true?
If it is true, than how the DB stored in shared drive will be linked with the front end?

Please help me to clear this doubts.

I already tried linking the tables but it only creates lots of tables. My question is : Do we have to link each front end from each users personal drive with the backend present in shared drive?

Or

We need to Link the tables and than distribute them to users?

I referred below link for linking.
Please share knowledge based on this.

http://www.hitechcoach.com/index.php...split-database

<>

Looking forward for reply. :D
May 21 '15 #1
Share this Question
Share on Google+
16 Replies


jforbes
Expert 100+
P: 1,107
You should probably read this http://bytes.com/topic/access/insigh...ront-back-ends as it addresses the issues you have brought up as well as a few others you will probably encounter.

One other thing... Make sure you have multiple backup copies of your database in multiple safe places while you figure out your deployment process.
May 21 '15 #2

P: 57
Hi jforbes,

Thanks a lot for your reply.

I followed the link which was really helpful. But, my question is still not clear.

See, I have done with DB split and FE,BE.

Now the thing is I distributed it to all users local drive with that Database.cmd file's copy.As written in that article.
Expand|Select|Wrap|Line Numbers
  1.  when you set up your users to use the Database, create a shortcut to the Command Script, not the Database. This will copy the Database to the local folder and run it from there
  2.  
Whatsoever users are saving it is not reflecting in the original DB.
And m not getting those datas saved in the BE file stored in Shared drive.

I made the appropriate changes in .cmd file as per the Location. but the problem still persist.

Please guide if am going wrong somewhere. As I need to distribute this files on Monday. I need to fix all the issues till tonight.

Thanks in advance.
May 22 '15 #3

P: 57
Also, highlight about linking the tables. Like when to do that.

Questions:

1) When to link tables?
2) After linking table distribute the FE file?

What I did is. I linked the tables and still it is not fetching data :(
May 22 '15 #4

jforbes
Expert 100+
P: 1,107
From what I can tell, this is a list of your current questions:
  1. When to Split the Database?
  2. When to Link the Tables?
  3. What to Distribute to your users?

When you feel comfortable that you have enough copies of your database to reverse anything you are about to do, you can split your Database. This will give you two new files that you will use going forward. Once your Database is split, you are done with splitting and wont need to split them again unless you have to revert to one of the backups you made at the beginning of this paragraph.

Now, since your databases have been split, you will need to maintain both databases. When you need to make changes to your Data Structure, you make them in the Backend Database then and for the most part, the changes will be reflected in your Frontend; except for when you add Tables to your Backend, you will have to add them to you Frontend by linking them.

In a typical Application, there is a Production Environment and a Development Environment. The idea of Deployment is to move all the program and data changes into the Production Environment without loosing the Production Data. This can get tricky if you don't plan for it.

How you Deploy your application, is kind of up to you. You will need to either take a copy of the Production Backend and apply all the changes that are in your latest version, or import the data out of your Production environment Database into a copy of your latest version of your Backend. How to accomplish that would be a whole other thread.

Regardless how the Backend is updated/deployed, during deployment, a copy of the new Development Frontend will need to be pointed at the Production Backend. You accomplish this through the Linked Table Manager either from the Ribbon or from right-clicking a linked table in the Navigation Pane. In the Linked Table Manager, you can point the Frontend to a different Backend. After you have updated the Linked Tables, you can create the .accde.

Once you have a compiled and redirected a copy of the Frontend (to Production) and placed it in the directory for your deployment script to pickup, you are finished rolling out your updated application.


For the first time you set this all up, you'll need to actually create the Deployment Script which is the piece of code that copies the Frontend Database to the Local users computer. Twinnyfo's thread explains how to create this, but for your clarification this is what is supposed to happen for your users:
  • You supply them with a shortcut to the Deployment Script which Twinnyfo has named Database.cmd. To stage this, I would make a Folder on a Server and copy the Database.cmd, Frontend Database, and a Shortcut to the Database.cmd into this Folder. This is the Folder your Deployment Script should point to as well.
  • Then deploy the Shortcut to the user either through email, a link to the directory with the Shortcut, or walk over to their computer and copy the Shortcut yourself. You could copy the Deployment Script to your users computers, but if you ever need to change the script, things could get difficult as you would need to address this for each user. This is why you use a Shortcut and have only one place to make the change.
  • Once the user launches your Application through the Shorcut, the latest version of the Frontend database will be copied down (by the script) to their computer and then the Frontend database will be launched in Access.

I think that covers your questions.
May 22 '15 #5

P: 57
Hi J,

Thanks a lot. Your answer is just perfect :)

You answered my all queries. Now I got what steps I need to follow and in what order. I wanted to know how it exactly works. I got that very clearly.

I did the same as u said.
-Split DB
-Prepare .accde
-Database.cmd
-created the shortcut of it
-placed it on the path mentioned in it.

But still when I try to open the Database.cmd file from the shortcut only text file is opening instead of FE.

I checked, the path and the file name. What could be the reason for that.

This is what I included in .cmd file

Expand|Select|Wrap|Line Numbers
  1. @ECHO OFF
  2. IF NOT EXIST %userprofile%\Documents\DBUser\[TRACKYOURDAY].accde MKDIR %userprofile%\Documents\DBUser
  3. COPY \\T:\Global Business\ASP\[TRACKYOURDAY].accde %userprofile%\Documents\DBUser\[TRACKYOURDAY].accde /Y
  4. START /I "MSAccess.exe" %userprofile%\Documents\DBUser\[TRACKYOURDAY].accde
May 22 '15 #6

jforbes
Expert 100+
P: 1,107
I guessing you mean that your .cmd file is opening up in Notepad when you run your shortcut. I'm not sure what exactly would cause this to happen but I have a feeling it has to do with how the shortcut was created.

I would recommend trying to create your shortcut like this:
  • Open up Windows Explorer and browse to the Directory that your .cmd file is located.
  • Right-click on the .cmd file and select Create Shortcut.
  • The Shortcut should now be created in the File List. You can rename it and/or change the icon.
  • Lastly, you can copy this on to your clipboard and Paste it on your Desktop, flashdrive or attempt to email it. (Exchange tends to strip off everything anymore, including shortcuts)
May 22 '15 #7

jforbes
Expert 100+
P: 1,107
Ooh, also, you'll want to remove the brackets [] in your .cmd file. I doubt your filename is really [TRACKYOURDAY].accde, you'll probably want to change it to TRACKYOURDAY.accde
May 22 '15 #8

P: 57
Hi J,

Thanks again for your response.

but the problem still persists.

My .cmd file is opening like a txt format.

I did what you suggested, but still no change.

can you please suggest me something?
May 26 '15 #9

jforbes
Expert 100+
P: 1,107
Sandy1992,
I'm most confused by it opening up Notepad. The two things that I can think of to do now:
  • Attempt to run the .CMD file without the shortcut and see what happens.
  • Like ZMDB asked, post the code that you are using to see if we can figure something out.
May 26 '15 #10

P: 57
Hi J,

Even when I run the .cmd file without the shortcut, I still see it as Notepad file.

I dnt know what could be the reason for it.

Expand|Select|Wrap|Line Numbers
  1. @ECHO OFF
  2. IF NOT EXIST %userprofile%\Documents\DBUser\TRACKYOURDAY.accde MKDIR %userprofile%\Documents\DBUser
  3. COPY \\T:\Global Bus\AP\TRACKYOURDAY.accde %userprofile%\Documents\DBUser\TRACKYOURDAY.accde /Y
  4. START /I "MSAccess.exe" %userprofile%\Documents\DBUser\TRACKYOURDAY.accde
  5.  
Also, I have the doubt for %userprofile%, as do I need to keep it same of change it the respective user path?

Please advice.
May 27 '15 #11

jforbes
Expert 100+
P: 1,107
Hey Sandy,
We came across a few things helping Shem K out Yesterday that should help you also: http://bytes.com/topic/access/answer...cess-using-cmd

I've applied them to your .cmd file below:
Expand|Select|Wrap|Line Numbers
  1. @ECHO OFF
  2. IF NOT EXIST "%userprofile%\Documents\DBUser\TRACKYOURDAY.accde" MKDIR "%userprofile%\Documents\DBUser"
  3. COPY "T:\Global Bus\AP\TRACKYOURDAY.accde" "%userprofile%\Documents\DBUser\TRACKYOURDAY.accde" /Y
  4. START /I "MSAccess.exe" "%userprofile%\Documents\DBUser\TRACKYOURDAY.accde"
May 27 '15 #12

P: 57
Hi J,

Thanks for your reply, but no Luck.

I don't know where I am going wrong.

Again I would like to mention what exactly I have done, so that if I am going wrong somewhere please correct me.

I created the .cmd file in shared drive ,where .accde and .accdb files are already present.

Now, I am creating the copy of .cmd file and going to my Personal drive and saving it.( on the same path saved in .cmd file)

When I double click .cmd file, instead of opening the copy of .accde, it opens in Notepad format :(
May 27 '15 #13

jforbes
Expert 100+
P: 1,107
I'm beginning to think that your computer has the file association/default for .cmd file set to Notepad.exe. I didn't want to believe it, but I think this is the case. .cmd files shouldn't be associated with a program as they are to be run by/at the Command Prompt.

To see the current file association on your computer, go into: Control Panel | Programs | Default Programs | Associate a file type or protocol with a program. This may vary depending on what OS you are running, this is for Windows 7.
Now a list of the File Associations for the PC should be shown. Scroll down to .cmd and make sure it says that the Current Default is "Unknown Application". If it isn't "Unknown Application" and is "Notepad.exe", then we have found the culprit.

The only way I know how to clear the setting is to use Regedit.exe to remove this key: HKEY_CURRENT_USER\Software\Microsoft\Windows\Curre ntVersion\Explorer\FileExts\.cmd\UserChoice
Using Regedit.exe incorrectly can seriously mess up a computer, so if you are unfamiliar with it you may want to consider alternatives.

So the question to you is, what is listed as an association for .cmd for you computer?
May 27 '15 #14

zmbd
Expert Mod 5K+
P: 5,397
Just to follow up in JForbes last post:

I've not had a chance to watch this tutorial all the way thru; however, it should cover the basics:
Micro Soft Change which programs Windows uses by default - Win7

For windows XP

Windows 8.1


(Working in a lab, new-hires like to tinker and sometimes they break things :) )
May 27 '15 #15

P: 57
Hi,
Thank you so much for your reply.

Apologies for the late reply.

Yes I checked in the control panel but it states "Unknown Host" only.

but I guess I have a hint why it is not working.
we are working in secured environment, so the client may have not allowed it. Can that be the reason for that?
Jun 25 '15 #16

zmbd
Expert Mod 5K+
P: 5,397
You will have to ask your IT dept if they have enabled group policy that disables running scripts. Let them know what you are doing and they may be able to help.
Jun 25 '15 #17

Post your reply

Sign in to post your reply or Sign up for a free account.