opps! I made a mistake! dang!!! (1 Viewer)

[accessfleet]

I make a table named Vehicle _type that was intended to be a lookup field in the units table. I made it incorrectly I selected number field as the type. After I made it correctly as Vehicle_type2( a text field) I was unable to delete Vehicle_type field in the units table. The message indicated that "I can't delete the field because it has 1 or more relationships" I tried to change the type from number to text and got a similar error message. I backed up the BE file and tried to remove the link line in the relationships (window) I found it listed in the units table but there was no line that I could delete. ( What have I done wrong) ( can it be fixed)

muchas grassious in advance

John.

 

[boblarson]

What do you mean it was to be a lookup field in the table? You don't use lookups at table level. You do them at form level.

So, you store the ID (number) of the item in the table but you have a table with the ID and description which you can then DISPLAY the description in the combo box or list box on your form.

Or did I misunderstand the question?

 

[accessfleet]

I made three tables Veh_type, Veh_sub_type & eq_sub_type.

I populated all three tables with the data I wanted to use to select from on the fe

I then added a look up field for the veh_type table on the units table on the be. It didn' t work, I tried to delete the field, it wouldn't let me delete or change the type.

Now my main menu button doesn't work for units. all other buttons work fine.

fore some reason I can't see the units table from the fe even though it can be accesed from the be after F11

Thanks for the speedi follow-up.
John..
607-737-5760

 

[accessfleet]

UNITS table is nolonger linked, not sure why, but there is a unitsmaster table which is a mirror image of the units. Linking the two tables together should fix the problem I think!? Can I just link any matching fields or is there more magic required?

I found what I found by looking at the links and movinging the main table links to the left, moving the unlinked tables to the right, then unraveling the spagetti in the middle.

I need a little guidance from a real expert on this one. I am a little "weak in the knees" of making an uninformed move now. I turned my db into junk really fast and spent a few hours getting to this point.

I need to climb on the back of a giant or two on this one!!!!!!!

muchas grassious in advance

John..

 

[Lightwave]

What do you mean it was to be a lookup field in the table? You don't use lookups at table level. You do them at form level.

I would qualify this statement. It is the perceived wisdom that you should not do lookups at the table level. Ironically Access will let you do them at table level and when I first started building databases I frequently did this rather than at form level because I didn't know any better!

I don't do it now but the applications were usable none the less. In my opinion it is one of the likely design errors people starting out will make.

 

[Lightwave]

yes back up both front end and back end and "experiment" on new copy. Retrospectively removing and adding links can be a bit of a headache as the historical inputed information may have multiple links.

Difficult to tell without just systematic investigation but one of the main reasons why you want to try and get your back end structure in place at the start and as far as possible leave it alone.

 

[accessfleet]

Well I got everthing working again. All the digging I did uncovered this result.


I made new front end folders
M:\office fe
M:\notebook fe
M:\garage fe
M:\parts fe

The back end folder is

M:\backend data


I was looking in Access Options at the default database folder on each computer.

the office pc =\\neptune\home\jcantrell\My Documents\
the notebook pc=C:\Users\JCC\Documents\
the garage pc=M:\garage fe
the parts pc=m:\parts fe

This is my 1st split database, I have learned a lot in the last seven months, but the trouble I had yesterday reminded me I am really still a newbie.

Should I change the default db folder for the office pc and the notebook pc to M:\office fe and M:\Notebook fe

I think this discrepancy may have had something to do with why I butched things up yesterday.

muchas grassious in advance.
John..

 

[Lightwave]

My take on this is... the front end holds the link to the database irrespective of what default is.

Default is simply a matter of preference although I personally feel it is a good idea to be careful when choosing names for mapped network drives such that M on one drive is not a different location from the M on another. Easy to get mixed up with that kind of thing.

I actually keep all my frontends on the local disk.

c:\programfiles\MSAccessFE\

They are kind of programs and there should be a new copy for each individual.

Prior to splitting I keep all databases in a single directory on the network which is just my general working directory for databases.

I know its a crime/ looked down on but I sometimes let some of my users alter their front ends and customise it for themselves. Especially those people that sit next to me in the office. They really start to see that there front end is different and specific to them.

They enjoy it and its a good learning tool for them. If they muck it up I can copy them a new front end in seconds.

Absolutely never give them access to the back end though.

 

[accessfleet]

Thanks Lightwave:

It is a little tricky keeping track of where every thing is. When I messed things up the other day , I think I made the problem worse by trying to restore from a file with a different path. That is why I am thinking uniformity has some value for me. Maybe it won't be as important when I become a more seasoned Access user.

I really appreciate your interest and help..

John

 

[Lightwave]

This almost comes under the more general file and network storage.

I personally have some conventions in place I (try) to stick to.

I create a single folder for each file with particular suffix

Therefore all excel files are in xls directory
all word files are in doc folder
all mdbs in mdb folder
I simply name the folder the suffix

I then name all files
Yearsuffixincrementdescription

so the first mdb created in 2011 would be

2011mdb001FirstDatabaseof2011

You still find yourself having to create odd directorys every now and then but largely it means I can easily transfer all files and folders between computers. Each filename should be unique works better for some things than others. It has the advantage that roughly you know when you created it so you can just flick through with your eye back to 2009 for instance and have a look.

I still mislay things mainly because I break my own rules and get confused.

 

[accessfleet]

Thanks Lightwave:

I use a similar methodology for my weekly meeting schedule department head report and monthly safety meeting reports.

Each time I change the db I go into access options\current database\application title and update the date. This help me keep tract of version. I'll try date as part of my file naming.

thanks again..

John.