Front End?

[AC5FF]

I have read a lot of posts and been advised quite a few times that I should put a front end on the DB that I use here - shared out to anyone/everyone in the organization.
My question though; it's been sort of tough for me to really understand...

If I want to make a change to the DB. Does the Front End file for everyone need to change?
I.E.
- change a table format
- change the way a query runs
- change the format of a report
- change the way a form looks/runs

What about if I have a Front End that everyone links to - I.E. on a shared network location. That's how the DB is running now, but can you do that with a Front End?

Just trying to wrap my brain around the problem before I begin

Thx

 

[boblarson]

1. Backend has the tables which store the data.

2. Frontend has everything else.

3. Frontend should be on each user's computer. Running a frontend (or full database for that matter) over a network is like playing Russian Roulette with corruption.

If you make changes to the objects in the frontend you can use a tool like Tony Toews or mine (see here: http://www.btabdevelopment.com/main/MyFreeAccessTools/tabid/78/Default.aspx )
to auto update on each users machine so you only have to make changes to the master and change the version number and then the user automatically gets a new copy of the frontend.

 

[Mike375]

change a table format That carries through to the FEs but if changing tables means you also changed some of the stuff below then those changes don't carry through. However, the way it is supposed to be done is the BE only has tables.

- change the way a query runs
- change the format of a report
- change the way a form looks/runs

Those, plus code, modules and macros are all changed on the FE. If all the FEs are to be the same you ony change one and then copy across.

Different FE's can have different looking forms, different, macros/code etc while all of them use the FE for the data.

Since each FE can be different this allows you to such things as restrict what category of records someone will see.

 

[AC5FF]

Thanks Guys..
This DB we use here has been running for years off one networked computer and everyone accesses it via a shortcut! Knock on wood - no serious troubles yet!

I would really like to create the FE/BE setup - but I worry about how it will work related to how it works now - getting everyone (100+) on the same page to use the same setup - etc etc etc... What I really need to do is just create the FE and test it myself for a while to see how things go.

I take it - if I took our MDB file and created the FE/BE than the DB would be different - i.e. right now everyone shortcuts to "Ellsworth.MDB"; if I change that to the FE/BE and someone connected to the BE file things wont work - correct?

 

[boblarson]

Thanks Guys..
This DB we use here has been running for years off one networked computer and everyone accesses it via a shortcut! Knock on wood - no serious troubles yet!

Just as an FYI - you have just been VERY lucky as doing so is like playing Russian Roulette for a long time. The odds are bound to tip against you at some point. But it is possible to stay alive for quite a while, but it is RARE.

I would really like to create the FE/BE setup - but I worry about how it will work related to how it works now - getting everyone (100+) on the same page to use the same setup

That's what my FREE Frontend Auto Update Enabling Tool is for -

What I really need to do is just create the FE and test it myself for a while to see how things go.

Yes, but don't wait too long - you have been living on the edge and it is reallly just a matter of time before BAM!

I take it - if I took our MDB file and created the FE/BE than the DB would be different - i.e. right now everyone shortcuts to "Ellsworth.MDB"; if I change that to the FE/BE and someone connected to the BE file things wont work - correct?

You give them the frontend which will be connected to the backend. And if you need to make changes, you don't have to do anything but change the version numbers in the master frontend and backend (if you use my tool). Then, the next time they open the FE, it tells them their FE is out of date, closes, deletes the old file, copies the new one down from your master location, and automatically reopens for them.

 

[AC5FF]

Thanks Bob
I've downloaded your stuff - now I have to see if my IT office will let me install it!

I browsed quickly - but didn't see what I was looking for - do you have any documentation on splitting a DB into a FE/BE setup?

Thx!

 

[boblarson]

do you have any documentation on splitting a DB into a FE/BE setup?

See if this helps:
http://members.shaw.ca/AlbertKallal/Articles/split/index.htm

 

[AC5FF]

Thanks Bob..
Unfortunatly this site is blocked for me at work. "Web Hosting"
I'll pop on this evening and check it from home.

Edit:
Great link - really explained a lot. Looks like it's test time again!

 

[AC5FF]

Bob / others...
I am having a pretty slow day today so I thought I would begin testing this FE/BE setup. I am working on a copy obviously, but can't find out how to do it!
Yeah, that link you provided was great - but I'm using Access2007 - and items just don't line up.
Any pointers on doing this in 2007 ver?

 

[JANR]

I don't have 2007 myself only 2003 but the principal should be the same.

First create an empty accessbase, then IMPORT all your tables from your original accessbase and close it. (the backend or BE)

Second create a new db and IMPORT all the queries, forms, reports, macros and modules from the same original db, this is your frontend or FE.

Now go and LINK all your tables from the BE to this db and you are good to go.

JR

 

[Simon_MT]

After all the splitting and link run the database analyzer to optimise any field within a tables that may need an index. This is not critical but it eliminates an issue now that you at looking at a FE/BE solution.

Simon

 

[AC5FF]

Simon...
My DB is so "patchworked" together from so many years of use when I run the DB Analyzer I have around 200 recommendations! Mainly "Index Recommendation" types...
I'm hoping to give this FE/BE a shot today. Keeping my fingers crossed

 

[AC5FF]

Problems! (Go Figure Right???? LoL)

I created a new DB - Named Mapdata Trial FE and imported all queries/forms/reports/macros/modules into it - took a while, but it all transferred.

I then created a new BE - Named Mapdata Trial BE and imported all the tables.
However, one table hung up saying "can't import this type object"
???????
It's definatly a table - I don't see anything wrong with the table, other than it contains around 26000 records.

Any ideas on what could be going on? It did this with both a 'select all' table import and then trying to import just this one table.

I copied the table and tried to paste it into the new BE file and got the same error. Now I've locked up my main DB; after copying the table data over (which appears to have worked) I came back to the original DB to close the table and it started saying "rendering Data to format: [column name]" and it hung up on a column (Been there now for 10min)... Arrrggh

 

[Simon_MT]

It could be corrupted Memo field showing #Error although I have not seen that message.

Simon

 

[AC5FF]

I may have fixed it... I ran a compact/repair and it's not giving me that error now.

Okay; so I have created the two new files. One is on a network drive, and the FE is on my desktop. I opened the front end, chose "External Data" then "Access", chose to link the tables, selected the BE file, and selected all tables. I now have all the tables listed in my FE w/little arrows by them (assuming that means linked).

As I said before - this DB is HIGHLY in need of some SERIOUS rewriting. But when I now open my FE and attempt to open the user interface form, I am getting TONS of prompts; i.e. Field [X]![Y] then the line for me to enter what this should be. That's not suppose to happen...

I know there is a LOT of VB code with the DB as well. Could I have missed that? What's the best way to ensure that all gets moved over as well?

Thanks!!

I kinda wish that there was a program like Bob's AutoUpdate tool that would split your DB into a FE/BE automatically...

 

[AC5FF]

Sorry - Back again...

I may have figured out that 'linking' problem and VB issue..
I copied the MDB file and deleted all the tables. I then closed/reopened and tried to link the tables. I saved as a new file, closed, and reopened. From what I can tell that APPEARS to have worked.

Onwards I press

Need to go back n read that link Bob posted again. I remember something about saving this now as a MDE file??? Not 100% sure about that yet.. more digging, more research required!

 

[HiTechCoach]

This may help:

Splitting a access database, or how to run ms-access in a multi-user mode.

 

[boblarson]

This may help:

Splitting a access database, or how to run ms-access in a multi-user mode.

same link I had posted earlier.

 

[Mike375]

I never knew this could be so hard

 

[HiTechCoach]

same link I had posted earlier.

Sorry Bob. I did not recognize the URL.