Good morning to everyone,
With your help I have done the following:
1. I have read Link
http://rounduptheusualsuspects.org/?p=1386 which Lightwave sent me and finally I managed to create a routine into my base which does exactly what is described in Link, (to do Compact & Repair regarding the example). I have succeeded to create a vbs file in which I put the example code into them.
Following attached is the code:
Firstly create the following function in your target MS Access database.
Public Function CompactDatabase()
Dim vStatusBar As Variant
DoCmd.SetWarnings False
If FileLen(CurrentDb.Name) > 2000000 Then
Application.SetOption ("Auto Compact"), 1
Application.SetOption "Show Status Bar", True
vStatusBar = SysCmd(acSysCmdSetStatus, "The application will be compacted on close during compaction please do not interrupt")
Else
Application.SetOption ("Auto Compact"), 0
End If
End Function
Next open up notepad paste in the following code save it as a txt file and then in explorer edit the suffix to vbs. Then simply double click on the file within explorer to run it. You should see the access database you have identified in the VB script open and then immediately close at which point it carries out the compact. The eagle eyed will have spotted that the above function doesn’t actually perform a compact. All it does is set the compact on close option to True within Access settings. The VB Script then instructs the database to close and Access compacts the database on exit.
set oAccess = createobject("Access.Application")
oAccess.OpenCurrentDatabase "C
ath\TargetDatabaseyouwantCompacted.mdb"
oAccess.visible = true
wscript.sleep 1000
oAccess.Run "CompactDatabase"
oAccess.closecurrentdatabase
set oAccess = nothing[/I]
2. After the above example, I change the code which msbad199 sent me in order to create Shortcut on Desktop. Already i test the code using a command button direct into my database in order to check if the shortcut has been created. The code is working into the following problem.
The code creates the shortcut on desktop but giving different name of the file (File Name=1.accdb). So, the first issue is that and i thing its simple to fix it...
3. Then I have created the Vbs file based on the above example, converting it to do what I want. (create shortcut on desktop). I use the following code into the vbs file:
set oAccess = createobject("Access.Application")
oAccess.OpenCurrentDatabase "C:\CheckIT\Test.accdb"
oAccess.visible = true
wscript.sleep 1000
oAccess.Run "Call CreateDesktopShortcut"
oAccess.closecurrentdatabase
set oAccess = nothing
Please note that:
1. The vbs file is in the same folder with my database. (C:\CheckIt\Test.accdb)
2. If I run the vbs file I'm getting the attached error in the photo but i saw the database to open normally without to create the shortcut on desktop.
Any help...?
