Upsizing

[pozzo]

I have a MS Access 2003 database that is getting corrupted frequently. I am following all the rules to avoid this problem but it keeps boring me with "Access can´t recognize this database format" and I lose all my data.
I´ve sent the db to experts to try to recover it and they told me that it was impossible. So, I´m planning to change my Backend (BE) and keep my Access Frontend (FE), because I like Access very much.
My question: what is the best solution for BE?
SQL Server? Firebird? another one?
I hope by changing the BE I won´t have problems anymore...
Thanks in advance
Renato

 

[GaryPanic]

keep me in the loop on this one

G

I know that <MS had a kinda sq server express that was a freebie

however not sure if it will run on 2007 so this could rule out this option as if you upgrade at a later stage - your stuffed

 

[boblarson]

Quick question - is the same front-end mdb file being accessed by multiple users, or do each have their own copy of the front end? Are you running over a WAN?

 

[pozzo]

Bob Larson asked me:

Q:"is the same front-end mdb file being accessed by multiple users, or do each have their own copy of the front end? ?"

A: So far the same front-end mdb file is being accessed by multiple users because it is changing everyday. When it becomes more stable I´ll create one FE for each user. Do you think my problem is exactly here?

Q: "Are you running over a WAN?"

A: No, I´m running over a LAN.

Renato

 

[GaryPanic]

as they are using the same front end
this is probably your problem

recommend you try seperate FE
g

 

[boblarson]

I would almost guarantee that your problem is that multiple users are using the same front end and that is corrupting your database because while users are in it a disconnection in the network (all it takes is a microsecond) occurs. We had a similar thing happening at my current job and that's what led me to creating the tool that enables front-ends to autoupdate so I didn't have to touch anyone's files, but I could make changes to the front-ends and then 100 users could immediately have their front-end updated when they next used the program. That way, everyone could have their own copy and we haven't suffered any corruption since.

Check it out here:
http://www.access-programmers.co.uk/forums/showthread.php?t=111132
and download the latest version from here:
http://downloads.btlarson.com/AWF/Samples/Access_Front-End_AutoUpdating_Utility_rev01-02-2007.zip

 

[pozzo]

Bob

I´m testing your cool tool that enables front-ends to autoupdate.
Everything is OK, EXCEPT that it sometimes don´t reopen the new FE version.
Instead of not reopening, it indeed updates the FE.
Have you any idea of what I could be doing wrong?

 

[boblarson]

You know, I've had a problem with that too at times and I have not tracked down exactly why it's happening. I have instructed my users that if that happens to them to just reopen it from their shortcut.

 

[pozzo]

Bob

Perhaps the problem is here:

In the code I´ve downloaded in Public Sub UpdateFrontEnd() the line

' sets the restart file name
strRestart = """" & strKillFile & """" (the file to be killed, not the one to be restarted)

should be

' sets the restart file name
strRestart = """" & strReplFile & """" (this is the correct file to be restarted)
*************************
Public Sub UpdateFrontEnd()
Dim strCmdBatch As String
Dim notNotebook As Object
Dim FSys As Object
Dim TestFile As String
Dim strKillFile As String
Dim strReplFile As String
Dim strRestart As String

' sets the file name and location for the file to delete
strKillFile = g_strFilePath
' sets the file name and location for the file to copy
strReplFile = g_strCopyLocation & "\" & CurrentProject.Name
' sets the file name of the batch file to create
TestFile = CurrentProject.Path & "\UpdateDbFE.cmd"

' sets the restart file name
strRestart = """" & strReplFile & """"

' creates the batch file
Open TestFile For Output As #1
Print #1, "Echo Off"
Print #1, "ECHO Deleting old file"
Print #1, ""
Print #1, "Del """ & strKillFile & """"
Print #1, ""
Print #1, "ECHO Copying new file"
Print #1, "Copy /Y """ & strReplFile & """ """ & strKillFile & """"
Print #1, ""
Print #1, "CLICK ANY KEY TO RESTART THE ACCESS PROGRAM"
Print #1, "START /I " & """MSAccess.exe"" " & strRestart
Close #1
'Exit Sub
' runs the batch file
Shell TestFile

'closes the current version and runs the batch file
DoCmd.Quit

End Sub
*****************************