Splitting the Database - Access 2007 - SOLVED

User avatar
teachesms
2StarLounger
Posts: 170
Joined: 05 Feb 2010, 18:04
Location: Jacksonville, NC

Splitting the Database - Access 2007 - SOLVED

Post by teachesms »

My original problem has been solved. So am going to post what happened here in case anyone else runs into this stupid stupid wall.

1. I built the database in 2007 as everyone but me uses 2007 Access
2. I tried to split it using 2010 which is on my office computer and no one elses.
3. When I went to any of the users workstations who will use this database and did the split there it worked like a charm. So now, all I have to do is visit each workstation and copy the frontend onto their desktops.

LESSON LEARNED. If you made it in 2007 access, split it in 2007 access. DO NOT try to use 2010 Access. And don't ask me why...but it just won't work that way!

Nannette


Good afternoon,

I need to split a database (which I know how to do). I am going to put the front-end on 12 peoples workstations. I am going to put the back end on an R:\drive in a folder that IT has set up for me. Each of the 12 have access to this drive and folder.

My question is this:

1. When I split the database the wizard walks you thru the steps. The database is on my thumb drive right now. Should I place the database on my desktop first and do the split? Will the wizard allow me to put the back-end on the R:\ and leave the front-end on my desktop?

2. I figured this would be the best route as when I go to each persons workstation to load the front-end, the path to the R:\ should be the same on everyones computer so making a connection should go without a hitch. I would assume it would automatically see the back-end.

Am I correct in doing it this way. Is there a better way? Am I doing it wrong. It's been 8 years since I did this myself, so I have forgotten.

Nannette
Last edited by teachesms on 27 Oct 2011, 13:55, edited 1 time in total.
If you can't convince them, confuse them - Harry S. Truman

Nannette

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Splitting the Database - Access 2007

Post by HansV »

1) I'd copy the database to hard disk first (for example to your desktop, but it can be another folder too).
The wizard lets you specify where the backend should be placed. This can be on another drive, such as the R: drive.

2) If everyone has the same path mapped to the R: drive, you can simply copy your frontend to the other computers. The linked tables should still work.
Best wishes,
Hans

User avatar
teachesms
2StarLounger
Posts: 170
Joined: 05 Feb 2010, 18:04
Location: Jacksonville, NC

Re: Splitting the Database - Access 2007

Post by teachesms »

Thank you Hans...I just wanted the assurance of my favorite guru.

Nannette
If you can't convince them, confuse them - Harry S. Truman

Nannette

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Re: Splitting the Database - Access 2007

Post by agibsonsw »

Hi.
I attempted to re-link a front-end copy on an USB pen but I ended up with corruption, so I would recommend always copying it to a hard drive firstly. Admittedly this was with an Access 2010 database, but still..

I'm no expert, but what I did recently was:

I created a subfolder within (or, better, would be near) the back-end database call 'Front Ends';
When modifying the front-end I copy the most recent version (of the fe) to my personal drive;
(don't modify the front-end copy from within the shared folder - it's not recommended, or necessary)
Once modified and tested I copy my new front-end copy to the shared 'Front Ends' folder, amending the name to include a higher version number;
I advise the users that there is a new front-end copy. They have been shown how to copy (the highest number version) to their personal drive, and delete their previous version;
For anyone not comfortable copying the fe, I will either do it for them or email a copy with instructions.

The/my server is backed-up daily but it would still be advisable to keep a separate/local copy of a few recent front-end versions. And, of course, this process depends on the competence/confidence of the users.

But I'm sure someone will point out the flaw(s) in my approach :cheers:
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

Mark L
3StarLounger
Posts: 331
Joined: 11 Feb 2010, 03:55
Location: Land O Lakes, FL

Re: Splitting the Database - Access 2007

Post by Mark L »

Nanette,

I don't recommend relying on a drive letter. I would place both the frontend and the backend on a server folder, then link the front to the backend using the full UNC for the backend. That is, something like \\servername\somefolder\mybackend.mdb". You can then copy the frontend to each workstation and they will be linked. The problem with using a drive letter is that you have to rely on every workstation to correctly map to this drive. Too much trouble. Using the UNC eliminates this problem.
Mark Liquorman
Land O Lakes, FL
see my website http://www.liquorman.net for Access Tips and Tricks, and for my Liquorman Utilities.

User avatar
teachesms
2StarLounger
Posts: 170
Joined: 05 Feb 2010, 18:04
Location: Jacksonville, NC

Re: Splitting the Database - Access 2007

Post by teachesms »

HansV wrote:1) I'd copy the database to hard disk first (for example to your desktop, but it can be another folder too).
The wizard lets you specify where the backend should be placed. This can be on another drive, such as the R: drive.

2) If everyone has the same path mapped to the R: drive, you can simply copy your frontend to the other computers. The linked tables should still work.
I did this Hans:

But the image I am sending shows that it is open somewhere and it is not open anywhere.
When done, I do get a backend on the R:\Flyer Request folder, but the front-end does not have an arrow by the table as it should.

I am not sure what is going on, but the other methods people have shown seem to be a complicated work around. Why doesn't it just do what it's supposed to do. It shouldn't be complicated.

Do you think it has to be opened exclusive or something? (Nope...just tested this theory and it didnt' help)

I also tried to simply split it on my desktop. It worked perfectly on the desktop, placing the frontend on the desktop...doing the split and putting the backend on my desktop...NOW, I want this to work as fluently on the R:\ and it doesn't...this would be the optimal solution as everyone using this database uses the R:\ and teh path will be the same.

See image.
You do not have the required permissions to view the files attached to this post.
If you can't convince them, confuse them - Harry S. Truman

Nannette

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Splitting the Database - Access 2007

Post by HansV »

It's hard to say what could cause this. Try closing the database. Then hold down the Shift key while opening it Then try to split it again.
Best wishes,
Hans

User avatar
teachesms
2StarLounger
Posts: 170
Joined: 05 Feb 2010, 18:04
Location: Jacksonville, NC

Re: Splitting the Database - Access 2007

Post by teachesms »

HansV wrote:It's hard to say what could cause this. Try closing the database. Then hold down the Shift key while opening it Then try to split it again.
Hans,
I did what you said. (There is no bypass coding go on in here or anything anyway, but I still held down the shift key while opening it and tried the split again, and got the same error message...could not update; currently locked. And it doesn't do this to me if I try to do the whole process on my desktop...works like a charm there. Only when I try to split it and put the backend on the R:\ . It places the backend there, but doesn't create a proper front end as shown in the next picture. There is no arrow next to the table.
You do not have the required permissions to view the files attached to this post.
If you can't convince them, confuse them - Harry S. Truman

Nannette

User avatar
teachesms
2StarLounger
Posts: 170
Joined: 05 Feb 2010, 18:04
Location: Jacksonville, NC

Re: Splitting the Database - Access 2007

Post by teachesms »

Mark L wrote:Nanette,

I don't recommend relying on a drive letter. I would place both the frontend and the backend on a server folder, then link the front to the backend using the full UNC for the backend. That is, something like \\servername\somefolder\mybackend.mdb". You can then copy the frontend to each workstation and they will be linked. The problem with using a drive letter is that you have to rely on every workstation to correctly map to this drive. Too much trouble. Using the UNC eliminates this problem.
It does the same thing with this method. It will not split on the R:\ without giving me the same error as in the graphics I posted. And it doesn't do the frontend as it is supposed to
If you can't convince them, confuse them - Harry S. Truman

Nannette

User avatar
teachesms
2StarLounger
Posts: 170
Joined: 05 Feb 2010, 18:04
Location: Jacksonville, NC

Re: Splitting the Database - Access 2007

Post by teachesms »

teachesms wrote:
HansV wrote:It's hard to say what could cause this. Try closing the database. Then hold down the Shift key while opening it Then try to split it again.
Hans,
I did what you said. (There is no bypass coding go on in here or anything anyway, but I still held down the shift key while opening it and tried the split again, and got the same error message...could not update; currently locked. And it doesn't do this to me if I try to do the whole process on my desktop...works like a charm there. Only when I try to split it and put the backend on the R:\ . It places the backend there, but doesn't create a proper front end as shown in the next picture. There is no arrow next to the table.
Hans could I send you a copy of this thing...If you are able to look at it via email?
If you can't convince them, confuse them - Harry S. Truman

Nannette

User avatar
teachesms
2StarLounger
Posts: 170
Joined: 05 Feb 2010, 18:04
Location: Jacksonville, NC

Re: Splitting the Database - Access 2007 - UPDATE

Post by teachesms »

Okay...this is what I did. (STILL NOT WORKING THOUGH)

1. I opened the database on my desktop and did a split TO MY DESKTOP.
2. I took the backend and moved it to the R:\Flyer Request folder
3. I opened the database on my desktop (frontend)
4. I used the Linked table manager and REPOINTED the front end to the back end on the r drive.
5. I got the following message when I tried to open something
You do not have the required permissions to view the files attached to this post.
If you can't convince them, confuse them - Harry S. Truman

Nannette

JohnH
3StarLounger
Posts: 287
Joined: 09 Mar 2010, 23:16
Location: Canberra Australia

Re: Splitting the Database - Access 2007 - SOLVED

Post by JohnH »

I find it far simpler to just manually split, rather than reply on the wizard.
Regards

John

User avatar
teachesms
2StarLounger
Posts: 170
Joined: 05 Feb 2010, 18:04
Location: Jacksonville, NC

Re: Splitting the Database - Access 2007

Post by teachesms »

Mark L wrote:Nanette,

I don't recommend relying on a drive letter. I would place both the frontend and the backend on a server folder, then link the front to the backend using the full UNC for the backend. That is, something like \\servername\somefolder\mybackend.mdb". You can then copy the frontend to each workstation and they will be linked. The problem with using a drive letter is that you have to rely on every workstation to correctly map to this drive. Too much trouble. Using the UNC eliminates this problem.
Mark,
Thank you for this post. I did not use it then, but have since found a reason to use this method, and am so glad that you posted it. So...in this new instance and project I am completing this works best (not relying on a drive letter). kudus!
Nannette
If you can't convince them, confuse them - Harry S. Truman

Nannette