Button activated Backup Access 2016

[LeonS]

I have created a new database in Access 2016. It has 3 forms and a number of tables, queries and macros. On the Utilities form I want to put a Button to automate a Backup procedure, saving a backup copy to the same directory as the database. I have watched YouTube videos but they seem so complicated. As yet, I have not split the database. Help anyone?

[Micron]

Not hard to find code for this, methinks. Here's one that should work:
https://www.iaccessworld.com/how-to-...ckup-database/

You lose/corrupt an un-split db and you lose everything, including data. Backing up a complete db is not the way to protect that data, and they say with an un-split db, it's a matter of when, not if.

[LeonS]

Thank you very much for your quick reply! I will have a look at the link you suggested. Yes, I am planning on splitting the Db into FE and BE. My only concern at the moment is retaining referential integrity within the BE. The Db is quite small in its functionality and the size of the tables will depend on each user, as they will have independent tables. It is when I want to update the FE, perhaps with new functionality, the problem may occur. Again, thanks.

[LeonS]

Micron, wonderful!!! It was so simple - and it worked. I was a bit concerned at first, seemed complicated but the code achieved exactly what I wanted. Excellent. Thanks, again!

[Micron]

Glad to have been of some help.

My only concern at the moment is retaining referential integrity within the BE.

That should not be affected by whether or not the db is split.

the size of the tables will depend on each user, as they will have independent tables.

That doesn't sound right. I'm guessing it means that you will have tblSomeTableName for each user for each table (or some number of tables). If so, why?

[LeonS]

No, the table names are the same for each user. The size will depend on how many records/ contacts each user has. The Master1 table has the 'growing' number of records. The others are pretty well static. The current total file size is about 30 Mb, after compacting. There are 11 tables, 26 queries, 24 forms, 2 reports, 14 macros and 14 modules. In my version, within the Master1 table there are currently 180 records. I do have some photos of the individuals but these are stored in an Image folder and I use the attachment system. The reason I am giving you the extra detail is so that you have a better picture of my database. Leon PS My next topic will be splitting and preserving the referential integrity. There are 4 main tables - Master1 (people), Countries, Companies and Industries - the last 3 being linked to the Master1 table.

[Micron]

That sounds like 7 tables (or 4) of the 11 will be in the fe (front end). If that is the case, have you considered what happens to that data when you have to issue a new fe? You might want to consider posting a pic of your relationships window if you have created them, either in this or your next thread.

[LeonS]

Dear Micron, thanks for that. trying to work out how to attach a PDF of the Relationships!

[LeonS]

Dear Micron, found out how!!! I hope it is attached!! Leon

[Micron]

Not the way it's normally done. The following is meant to be constructive and not condescending criticism. These days, it seems one has to make that distinction.

1st, the ID fields from the 3 tables should be linked to master table like CountryIDpk > CountryIDfk (primary to foreign) so that what you see in the master would be the autonumber ID (I presume that's what IDCountries is) from Countries. You likely are using the country name fields in the joins.

2nd, Master is built like a spreadsheet (a no-no in a properly normalized db) and the 1st 9 fields indicate it is info about a person. In that case, several fields after that don't seem to belong.

3rd no special characters in field names, plus Male/Female is data. Use Gender and either put M (or F, or whatever) as data. Better to have tblGender and use its PK autonumber value.

4th, I presume something can be either Yes or No, not both, so many records will have nothing in one of those 2 fields. Not to mention that they are both reserved words anyway (same goes for First and Count). Similar to Gender, but the field names give no clue as to what is Y or N so no suggestion there.

5th, suggest you do not store images in your db. You will likely run into db size issues at some point. Better to store the image path and pass that to the image control.

6th, FullName seems redundant when you have First and Last. If FullName is both of the other fields, then you have a calculated field - another not so good idea.

Last, company address fields are an example of repeating data in fields - even if there is only one value (which also causes a lot of holes like the Yes No thing). So another no-no. Better to have tblCompAddress. If a company A has 1 address, there is 1 record in it. Company B has 3 so 3 records thus the Companies.ID value would be there once for each record (3 times, 3 records).

You can fix now or plod ahead, in which case I think you will struggle. Suggest you research normalization, naming conventions and perhaps reserved words. In fact if you adopt a naming convention you will seldom use a reserved word in code or many object. The exception being field names perhaps.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Naming conventions - http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

[LeonS]

As it says in the banner above, this is a Quick Reply !! Thank you so much for all of that!! And you do not need to say that my "workings" are not perfect. I know that. In my defence, I have to say that what I have produced so far, does work. I fully appreciate that I am not an expert programmer like you. I would like the time to learn (and actually understand) more. This was supposed to be quick!! I will read and take in your comments. And I will get back to you. I just wanted to say Thank You!!!

[Micron]

IMO, no need to defend yourself. It has taken me about 20 years to get this far, and compared to many I might be 3 or 4 on a scale of 10. Perhaps I'm being conceited there, but at least I have some idea of what I still don't know. Also, my answers tend to be somewhat explanatory in the hopes of teaching as that's my nature.

BTW, I might say that a well designed db is like a reliable car while poorly built db's are like old junkers.
Both of them do a job but one won't get you very far or do that job for long. After 9000+ posts I can tell you that many people come here repeatedly because they find they can get data in but can't get it out in ways they need to, and the root cause is always design issues.

According to post 4, you have a solution to your original issue. Perhaps you should mark this one as solved and if need be, start another one about your design question(s) once you've studied normalization?

[LeonS]

Dear Mr Micron – Thanks for that. Useful advice and guidance. I have “tidied” up my Master1 table. I have removed some fields that were no longer being used, by anyone, anywhere (including Count!). I have change Male/Female to Gender – and made all the necessary changes in the Queries, Forms, Table, Macros (QFTM). My database still works. I have tried to delete the links from the T references I showed you. Delete them I can. But how do I link them for the future.
I do not store my people photos in the database. I store them in a separate folder using the “Attachments” facility. I finally found out how to include a person’s photo in a ‘Contact Report’. I could get them onto the F already, but for the R – major problem. Eventually I deleted the field/box and started again. This time it worked – great!
I am wading through the topics you sent me. Thank you. Very interesting, and I do realise that I have not “obeyed” those rules all the time. Purely through ignorance! In spite of being at the London launch of Access in November 1992, I have only used it on and off. So, I have tended to ‘remember’ how to do things rather than ‘learn and understand’. I will try and mark this thread as Solved! I will start another thread which will be about Getting Outlook Calendar into my database, being called by a button. Leon.