Please help with my code

[AccessHelp]

Good morning,

I have the following code to compact and repair the BE database just
clicking a button on the form of a FE database.

On Error goto Error_Handler
Dim strSource as string
Dim strDestination as string
strSource = "C:\T&T.mdb"
strDestination = "C:\T&T1.mdb"

CompactRepair = Application.CompactRepair (SourceFile:=strSource,
DestinationFile:= strDestination, LogFile:=True)
Kill strSource
Name strDestination As strSource

On Error goto 0
Exit sub
Error_Handler:
CompactRepair = False
Msgbox "The compacting of the database was not done. Please try again."
End sub

The code seems to work up to the point "CompactRepair = Application....".
It created a destination file and deleted the original source file. However,
instead of naming the destination file "T&T1.mdb", it names "T_T1.mdb".
Therefore, when it gets to the code "Name strDestination As strSource", it
failed because it couldn't find a file name "T&T1.mdb".

Does anyone know why Access uses "_" in place of "&"? How can I make the
code to accept "&" for the destination name?

In addition, according to the code, a log file should be created. I saw it
created then deleted itself. Anyone knows why?

On the other hand, if you have a better code to compact & repair the BE from
FE, please share with me.

Thanks.

 

[Klatuu]

I believe you are confusing the compiler. The & character is VBA is a
concatenation symbol

X = "ABC"
Y = "123"
Z = X & Y
Z is now "ABC123"

Use a different character.
In naming, it is always best to avoid any special characters other than the
underscore. Also, don't name anything Date, Name, Description, Time, or any
other Access reserved words.

 

[AccessHelp]

Hi Klatuu,

Thanks for you help. Do you have any suggestions on my code to compact and
repair the BE database? Would you do it any better?

Thanks again.

 

[Klatuu]

I, personally would not do it from code; however, If I had to, I would rename
the original rather than delete it until the new has been successfully copied
into place.

 

[AccessHelp]

Hi Dave,

If you don't mind, please share with me how you would do it if you won't use
the code to compact and repair the BE database in my situation. My situation
is once I finish with the database, I will handle it over to the users. They
will then use it and maintain it. Therefore, I won't have to involve in
maintaining it. The other thing is the BE will be saved in a sql server, and
I don't even want the users to know there is one.

Is it a good practice on designing a database? Thanks again.

 

[Klatuu]

If you don't want to be involved in the maintenance of the database and don't
have one of the users trained in how to watch for bloat and do occasional
compacts, then you will have to do it from code.

I did not understand the context of this question:

 

[AccessHelp]

Hi Dave,

My question meant whether it is a good practice to design a database by
storing the BE in a remote area (meaning the users will not have access to
it) and by having all the maintenance features (like compacting the BE
database) built on the FE database.

Thanks.

 

[Klatuu]

Users have to have access to and full rights to the folder where the BE is
located; otherwise, they would not be able to connect to it or run it. So,
If you are trying to hide it, that will not work. The problem with the
compact in code is that to do the compact, you have to be the only one to
have it open. IHMO, t is more hassle than it is worth. I would designate
one of the more knowlegable uses as the DBA and train them on the basic
functions.