General Qns about Access 3

[tekila]

1. What happens when the Access database gets very big over the years?

2. Is it possible to create a form that allows user to create backups of the database and restore backups, other than the conventional way?

These questions just happened to cross my mind...

 

[allanon]

1) Access databases will continue to grow until they use up ALL available space if they are not compacted. As an example there is a user here that likes to create his own access databases. Our main shared network drive was nearly out of space so we ran a job to give us everyone's disk usage and this guy came up with the most. One of his databases was at 950meg. I compacted it down to 38meg.

2) This is really a preference type of question. There are any number of unconventional was of backing the data in an application. One way that I have used in the past was:
I had a separate back end when the use clicked a button the app would run a bat file passing a file name created from the date. This bat file would then run pkzip on the back end saving it to the file name passed to the bat file.
Another method that I have used (which is some what slow) is to write out all of the data to text files. This has a couple of advantages in that it allows you to save specific tables. This of course would take up a lot less room then backing up all of the data. Very useful for small databases where most of the data is static.

Anyways, that's a couple of unconventional ways.

 

[Junior1544]

personaly, i have a form open up hidden... so that no one see's it... nothing on it at all... just an event procedure that is on close...

so when some one closes the database, this form closes, and the event runns...

the event procedure copies every thing from my hosting directory to a sub directory by date... so that i have a backup when ever any one exits... and if some one exits multiple times a day, it over writes each time... the advantage to this, is that when the last person leaves at night, it back's up the database one last time... and i have backup's for every day, as of when the last person was in the database...

hope this logic helps a litte...

--Junior
JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[tekila]

Junior, I'm rather interested in your method. Could you kindly give me the details, coding esp.

What happens when muliple users are updating the database and close it at the same time?

Well, I plan to launch my database application on the company's intranet and I'm unsure of the matters that I need to consider.

 

[Junior1544]

i have a splash screen that my users need to hit ok on to get into the database... the first thing that button does is open my hidden form... this hidden form does nothing, takes up extreemly little memory, and doesn't slow the app down at all (untill it's time to close it...)
then this form goes to work... it checks first to see if i'm on the computer that is hosting the database... that is the computer i use most while development... so i don't want to waste time waiting for it to talk to itself through the network

then it breaks down the date and puts it into a variable in this format

05172002

then it makes that directory in a sub directory called backup from the db directory... it also over writes each time so when another user exits later in the day, i have a newer backup... i only have 5 users including myself, so i'm not worried if two people try to exit at the same time... the reason i wanted to do this is because i wanted to have a backup at the end of the day after every one is out of the database... this way i don't have to be there to do it...

hope this code helps, it's almost exacly what i have... i've just changed comp names and directorys to post here... but it's valid code, just change any computer names and directorys...

--Junior

on the form, i have this code...


Private Sub Form_Close()

Dim strRootPath
Dim strFileName

Dim objScript

Dim strdate
Dim strMonth
Dim strDay
Dim strYear

Dim strSource
Dim strTarget

Set objScript = CreateObject("Scripting.FileSystemObject&quot

strMonth = DatePart("m", Date)
If Len(strMonth) = 1 Then strMonth = "0" & strMonth

strDay = DatePart("d", Date)
If Len(strDay) = 1 Then strDay = "0" & strDay

strYear = Right(DatePart("yyyy", Date), 4)

strdate = strMonth & strDay & strYear


If Not objScript.FolderExists("C:\dbhostdir&quot Then
strRootPath = "\\hostingcompname\dbhostdir\"
strSource = strRootPath & "*.*"
strTarget = strRootPath & "backup\" & strdate & "\"
Else
strRootPath = "c:\dbhostdir\"
strSource = strRootPath & "*.*"
strTarget = strRootPath & "backup\" & strdate & "\"
End If



If Not objScript.FolderExists(strTarget) Then
objScript.CreateFolder (strRootPath & "backup\" & strdate & "\&quot
End If

objScript.CopyFile strSource, strTarget
Kill strTarget & "*.ldb"

Set objScript = Nothing

End Sub
JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[tekila]

Junior, how do I hide the form?

My database is currently saved in this directory, C:\Database\DatabaseName so do I replace dbhostdir with Database\DatabaseName and I suppose I don't have to create the backup file, rite?

 

[Junior1544]

DoCmd.OpenForm "Bkup", acNormal, , , , acHidden

opens the form name Bkup in hidden mode...

here, i'm going to rework the code i had given you to use all variables in the code and then we will just declare them at the begining, make it easier for you...

--Junior

i'll get back to you later with it...

--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[Junior1544]

ok, i took a bit out to make it a little easier to undestand what's going on, and it'll still work... it'll just be slow on the host computer if in a multi user enviroment...

the part in red you need to change to be accurate within your network enviroment... you can use a network address like i have in there now, or you can use a drive letter and directory like this:
c:\databasedir\

hope this helps a little more...

--Junior



Private Sub Form_Close()

Dim strRootPath
Dim strFileName

Dim objScript

Dim strdate
Dim strMonth
Dim strDay
Dim strYear

Dim strSource
Dim strTarget

Set objScript = CreateObject("Scripting.FileSystemObject&quot

strMonth = DatePart("m", Date)
If Len(strMonth) = 1 Then strMonth = "0" & strMonth

strDay = DatePart("d", Date)
If Len(strDay) = 1 Then strDay = "0" & strDay

strYear = Right(DatePart("yyyy", Date), 4)

strdate = strMonth & strDay & strYear


strRootPath = "\\hostingcompname\dbhostdir\"
strSource = strRootPath & "*.*"
strTarget = strRootPath & "backup\" & strdate & "\"[/color]


If Not objScript.FolderExists(strTarget) Then
objScript.CreateFolder (strRootPath & "backup\" & strdate & "\&quot
End If

objScript.CopyFile strSource, strTarget
Kill strTarget & "*.ldb"

Set objScript = Nothing

End Sub JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[tekila]

Bingo! Everything works perfectly but is it possible to do a custom backup via a form, whereby there's a "Browse" command button for the user to select the path he wants to create a backup. Cos what I'm doing now is hard coding the path, I thought it would be more flexible to provide another option.

 

[Junior1544]

yes there is... to bad i have no idea how to impliment it... sorry...

--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[tekila]

Junior, I just realized that your code makes a copy of the database in a new folder after everyday, in that case, the available space in my drive would be used up very soon. Shouldn't the backup overwrite itself all the time? How can I go about doing that?

By the way, I've kind of figured out how to do the custom backup, still in the process of debugging the code...

 

[Junior1544]

Here you go...

change the red to the directory of the database...



Private Sub Form_Close()

Dim strRootPath
Dim strFileName

Dim objScript

Dim strSource
Dim strTarget

Set objScript = CreateObject("Scripting.FileSystemObject&quot

strRootPath = "\\hostingcompname\dbhostdir\"
strSource = strRootPath & "*.*"
strTarget = strRootPath & "backup\"

'uncomment the below line to delete the
'backup directory befor recoping files there.
'kill strTarget & "*.*"

If Not objScript.FolderExists(strTarget) Then
objScript.CreateFolder (strRootPath & "backup\&quot
End If

objScript.CopyFile strSource, strTarget
Kill strTarget & "*.ldb"

Set objScript = Nothing

End Sub JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[Junior1544]

one thing i thought of after hitting submit... you're going to be implimenting this on a network... how many users are you planning on having... if it's more then just a few... (i would say more then 10) then you will need to do some additional things... and i can help you with that to...

you can feel free to ask me more if you want to.

--Junior

JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[tekila]

Gee... thanks a lot, I really appreciate your help. By the way, about twenty users will be accessing the database and I may plan to put it in the common drive instead of the intranet, due to time constraint and redundancy. Still working on the custom backup and I'm wondering if it's necessary to do a custom restoring of database.

 

[Junior1544]

backup is ok, if you still maintain the back up's... but if it's backup up on the network, tell the network people that you need a pretty big size... then say, once a week copy the files localy and back them up onto a cd or tape or some thing like that...

20 people using it, i would suggest making it a front end/back end setup... and having the front end on the local users pc...

but that's for another thred

--Junior
JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[tekila]

I've hard coded the source and target of the auto backup database in the original database's module, remember the one about the hidden form. What happens if I decide to restore a backup database from another path, won't the auto backup be performed on the original database, and not the restored one?

 

[Junior1544]

what ever is in that origanel directory get's copied to the backup dir... then it delete's any ldb files (the lock files, you don't need them in a backup)... then to restore you just copy the one that's in the backup dir over the current one...

keep in mind, if some one has exit'd the database, that is the vertion in the back dir... that is why i had used the date as part of the directory... that way you know the date of when it was done... and you have a backup that is a few days old, if you need to go back a few days... just a few things to think about...

--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[tekila]

I've completed custom backup and restore database facilities but encountered a minor problem. When I do a backup, I kill strTarget&"*.ldb". If I restore the backup and auto backup module does a backup of this restored database (which is a backup), there isn't strTarget&"*.ldb" to be killed, so I get an error msg to indicate that the file is not found. Is it okay if I don't implement Kill strTarget & "*.ldb"?

 

[Junior1544]

it's fine that you don't include that statment with the restore... all that statement does is delete the ldb's when you copy the one directory...

you need to keep in mind... if there is a problem, so they exit the database, the error is included in the backup... because it backed up on exit of the db... and it keeps over writing the backup... so you're still going to have to backup as offten as you want by hand... i tried to say this earlier... that's why i had put the dates in mine, so the next day there is a new backup... and once a week or so i go in and take the backup's off...

--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[tekila]

Junior, I've been encountering many problems when I carried out the pilot run of my DMS.

1. #Name? appears on forms and reports for date field (on certain PCs but not all, a particular one runs on NT).

2. Only data in the report headers are displayed when reports are emailed to recipients whose PCs run on Win98, and they have definately installed snapshot viewer. This is not a problem when recipients' PCs run on NT.

3. For PCs operating on Win98, when the users try to open the database, they receive an error; Microsoft Access can't change working directory to D:\Data|Access.

4. Just a question, I've created the workgroup files and saved them in my C drive. Do I have to create the similar files in every PC that will be accessing the DMS?

5. Another question, when more than one user open the database at the same time to insert new data, will user A get to see the updates of user B immediately after user B has stored the data in the database?

6. Last question, when I permit user to insert and edit data in the form, I must checked these permissions in the table that is bound to the form, rite. But how can I permit user to edit his own records via the form and not from the table, cos now he's able to edit any data from the table.