Odd New Error (1 Viewer)

Wapug

Registered User.
Local time
Today, 06:52
Joined
Apr 14, 2017
Messages
51
Ive been using the same database for quite some time. The only recent change has been that I have code to backup the db on closing. Here's the issue. I have a series of steps (with buttons set up) to import data, analyze data & Export Data. I click on these buttons from a form. The last two days that I have used the db, the final step has caused me to get an error that says Microsoft Access has stopped working, and it shuts down the database when I click ok. No Idea why this happens, however I have found out that If I got into the db and open the form with the buttons, in design view and then open the code for the button to export, then Close it and reopen the form in form view and run the "Export Data" macro by pushing the button, it works fine. Any idea what's going on here?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:52
Joined
Oct 29, 2018
Messages
21,358
Sounds like maybe it could be a sign of corruption.
 

Alc

Registered User.
Local time
Today, 06:52
Joined
Mar 23, 2007
Messages
2,407
I had a similar problem, some time back.
In my case, the db had also increased in size by a fair amount. Running a compact and repair fixed the problem.
It turned out that one user didn't like the small amount of extra time it took for the back up process to run when the db closed (we're talking under a minute). As a result, he used to close the db using task manager to kill the Access process. Over time, this caused bloat.
Try the compact and repair option. Worst case? It does nothing to help you. Best case? Problem solved.
 

Wapug

Registered User.
Local time
Today, 06:52
Joined
Apr 14, 2017
Messages
51
I tried the Compact Repair thing, no luck.
 

Alc

Registered User.
Local time
Today, 06:52
Joined
Mar 23, 2007
Messages
2,407
I tried the Compact Repair thing, no luck.
Have you tried importing everything into a new, blank db?
Takes longer than the compact and repair, but it's helped me in the past.
 

Wapug

Registered User.
Local time
Today, 06:52
Joined
Apr 14, 2017
Messages
51
I deleted my backup code. I was backing up the db at close and something about my code was causing an issue.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:52
Joined
Oct 29, 2018
Messages
21,358
I deleted my backup code. I was backing up the db at close and something about my code was causing an issue.
Was it causing corruption?
 

Alc

Registered User.
Local time
Today, 06:52
Joined
Mar 23, 2007
Messages
2,407
I deleted my backup code. I was backing up the db at close and something about my code was causing an issue.

If it's any use, I use this on close in multiple databases, to back up the various back ends. No problems, thus far.

Code:
Function Backup_Backend()
    Dim strPath As String
    Dim strFile As String
    Dim strBackUpFile As String
    Dim strTempFile As String
    Dim strBackUpFolder As String
    Dim fso As Object
    Dim strOldPath As String
    Dim strNewPath As String

    strOldPath = {path including name of db back end}
    strBackUpFolder = {folder for back up copy}
    strNewPath = strBackUpFolder & {name of back end} & Format(Date, "Dddd, dd-mm-yyyy") & ".accdb"

    If FileExists(strNewPath) Then
        Kill (strNewPath)
    End If

    Set fso = CreateObject("Scripting.FileSystemObject")

    fso.CopyFile strOldPath, strNewPath
    Set fso = Nothing
End Function
 

Wapug

Registered User.
Local time
Today, 06:52
Joined
Apr 14, 2017
Messages
51
Was it causing corruption?
Honestly, I'm not sure what it was doing, but once I removed it the problem stopped. Now I have no idea how to backup the database, other than doing a manual backup. I cant even imagine how a function tied to a button that backs up and closes the database would corrupt it in such an odd way. Man Im flustered today.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:52
Joined
Oct 29, 2018
Messages
21,358
Honestly, I'm not sure what it was doing, but once I removed it the problem stopped. Now I have no idea how to backup the database, other than doing a manual backup. I cant even imagine how a function tied to a button that backs up and closes the database would corrupt it in such an odd way. Man Im flustered today.
Not sure what code you were using before, but this is what I use, which is similar to what Alc just posted, and I don't remember getting any corruption issues from it. Hope it helps...
 

Wapug

Registered User.
Local time
Today, 06:52
Joined
Apr 14, 2017
Messages
51
I don't have a split database, so IM not sure I can use that. I use this code

Public Function fMakeBackup() As Boolean
Dim Source As String
Dim Target As String
Dim retval As Integer

Source = CurrentDb.Name
Target = "c\backuphere" & " "
Target = Target & Format(Date, "mm-dd") & " Open "
Target = Target & Format(Time, "hhmm") & ".accdb"
Target = Target & ".accdb"
' create the backup
retval = 0
Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
retval = objFSO.CopyFile(Source, Target, True)
Set objFSO = Nothing
End Function
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:52
Joined
Oct 29, 2018
Messages
21,358
I don't have a split database...
Ahh, that would be your first issue. You should really consider splitting your database, if it's being used by multiple users at the same time. Improperly sharing a database can lead to file and data corruptions.
 

Alc

Registered User.
Local time
Today, 06:52
Joined
Mar 23, 2007
Messages
2,407
Ahh, that would be your first issue. You should really consider splitting your database, if it's being used by multiple users at the same time. Improperly sharing a database can lead to file and data corruptions.
Was halfway through typing the same thing. :)
 

Wapug

Registered User.
Local time
Today, 06:52
Joined
Apr 14, 2017
Messages
51
The database is only used by one person, it doesn't make sense to have it split. Is it possible to back it up with a command button, or tying a function to a button like I already have? By one person I mean, there are never multiple people accessing it, its always one person using it each day (usually me).
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:52
Joined
Oct 29, 2018
Messages
21,358
The database is only used by one person, it doesn't make sense to have it split. Is it possible to back it up with a command button, or tying a function to a button like I already have? By one person I mean, there are never multiple people accessing it, its always one person using it each day (usually me).
Hi. It's still recommended "best practice" to split a database even if there's only one user using it at a time. You mentioned multiple users are accessing it, although only one at a time. Does this mean the database file is stored in a shared network location? If so, do you happen to do a Compact & Repair on it? If so, exactly how do you do it? Performing a C&R over the network is also another potential source of corruption.
 

Users who are viewing this thread

Top Bottom