When to split?

[HelenJ]

Could you please give me the design reasons for splitting a database - ie
front end back end.

I am building a database as it is being used (not ideal but life I fear!) -
up till now I have taken a copy away with me and then imported the new forms/
reports etc once I have developed them.

Would this be easier if I split the database?

Thanks

 

[Ken Snell [MVP]]

See these articles for more info about splitting a database:

http://www.granite.ab.ca/access/splitapp/index.htm

http://www.allenbrowne.com/ser-01.html

Easier if you split? Yes, especially for down the road.

 

[HelenJ]

Thanks Ken - having read those I guess I'd better split it

 

[Lynn Trapp]

Could you please give me the design reasons for splitting a database - ie

front end back end.

It would be a much shorter list if we listed the reasons for NOT splitting a
database... <g>

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Roger Carlson]

I think you should split when you've worn out your welcome!

(Sorry. It's turning into one of "those" days)

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Allen]

OK, I understand Peter V.'s reconnect function but not how to implement it.
Where do I put it and what invokes it?
I have split a database in a development environment. When I install it
into a production path and launch it the FE is looking for the BE in the
development path, not the same directory the FE is installed in. Can I tell
the production FE that the BE will always be in the same directory as the FE
or in a folder relative to the FE?

THanks, Allen.

 

[Ken Snell [MVP]]

"Peter V.'s reconnect function"? Not sure what you mean by that.

Implementing a VBA code to reconnect a front end to a back end can be done
in many ways. How I do it is this:

(1) Have a table in the front end that holds various possible reconnection
paths (I use this so that I can keep development and working paths in the
front end, enabling me to relink to whichever one I want). I assign a
priority number to each path.

(2) Open a form when the database first opens. In this form, run code (I use
the Timer event, with timer set to a few milliseconds) that gets the desired
linking path from the table and then relinks all the linked tables. In my
code, I cycle through all the paths in the table (using priority as the
sorting order) until it finds a valid backend path (i.e., the code finds a
backend file where the path says it should be). Once found, the relinking is
done. (My code is a variation / enhancement of the code posted at
http://www.mvps.org/access/tables/tbl0007.htm)

(3) This first form then closes and opens a switchboard/menu form for use.

--

Ken Snell
<MS ACCESS MVP>

 

[Allen]

Peter V. reconnect function was found at some unofficial Access support web
site. Same idea as your function.

I can't believe it is this "difficult" to "fix" something that is suggested
(splitting a database). I split my DB in the development environment, copied
the FE and BE to a different machine where they are in the same folder but
the folder has a different name from the development environment. Opened the
FE and got error 3024, could not find file name (of the BE database) it was
looking in the same path as the original folder on the development machine
where the split occured. Several postings indicate the Linked Table Manager
can be used to fix this but when I try to open the Linked Table Manager after
getting error 3024 it is not available (grayed out on drop-down menu).

I like the idea of your approach and I am working on the function you
recommended now.

Thanks, Allen.

 

[Ken Snell [MVP]]

Once you implement such a function/code, relinking happens with no thought
or effort!

 

[Allen]

I think this statement can or should be true of any IT implementation.
I am assuming from the lack of any other suggestions that there is no manual
way to relink these tables once put on the production machine. I am learning
Access after 20+ years of mainframe work so I know my mindset is a twisted
but this just seems to be a big shortcoming in the development and
distribution of databases.

I am trying to invoke your function from an autoexec macro. I know you
mentioned a startup form but is this doable or am I waiting my time.

Many thanks for your patience and understanding.

 

[Ken Snell [MVP]]

You could invoke the relinking code from an AutoExec macro. You'll need to
write a function that either runs the relinking code or else calls the code
that does the relinking. AutoExec can only call a function, using the
RunCode action.

You can manually relink tables. Use Tools | Database Utilities | Linked
Table Manager. You will need to check the box at the bottom of the window
saying "Always prompt for new location". I don't like it because (1) you
must navigate to the backend file; (2) you must hope that no errors occur,
as then you will need to navigate for each individual table in the list
(this happened to me a lot when doing development work, as sometimes my
front end linked to tables in my development backend, but the actual backend
didn't contain those tables yet); (3) if you let users do this, invariably
someone will not do it correctly and then you have to come do it for them.


--

Ken Snell
<MS ACCESS MVP>

 

[Allen]

Thanks much Ken, but what would cause the Tools | Database Utilities | Linked
Table manager to be inactive (grayed out)?

Allen.

 

[Ken Snell [MVP]]

Sorry, I had forgotten that you'd posted that info in your earlier note.

Try opening the front end file while holding down the Shift key. (I assume
you must have some code running when the file is opened normally.) Then try
the Linked Table Manager.

--

Ken Snell
<MS ACCESS MVP>

 

[Allen]

That did it. Thanks so much Ken.

Sorry, I had forgotten that you'd posted that info in your earlier note.

Try opening the front end file while holding down the Shift key. (I assume
you must have some code running when the file is opened normally.) Then try
the Linked Table Manager.