Question Access automation

[dz2k7]

Looking for vbs script or something to do the following things during the night time while I'm home:

1. Open the Access DB
2. Run the Delete queiry in Access DB
3. Compact the Access DB
4. Run the macro in Access DB to build and update the new table.
5. Close the DB

Thanks to everybody

 

[motleyjew]

You can also use the shedule task in Programs/Accessories/System Tools/Schedule Tasks to activate your VBscript.


Try this with VBscript.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Read from a Microsoft ACCESS database

Option Explicit

dim oaccess

'execute another Access macro
set oaccess = createobject("access.application")
oaccess.opencurrentdatabase "C:\File\Database Name.mdb"
oaccess.docmd.runmacro "Your Macro Naem Here"
oaccess.closecurrentdatabase
oaccess.quit
set oaccess=nothing


WScript.Quit(0)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
You can also set a form in your database to open a form when it opens and set the on open event/on timer to your macro.

Good luck

Gregg

 

[dz2k7]

Wow!
That's a grate thing!
Thanks!

It works fine, but how I can compact it?

 

[motleyjew]

I'm not sure about a macro but you can set the database to compact when its closed.

Options
General
check "compact on close"

 

[dz2k7]

neee ...
I need to compact it after i deleted the table.
Otherwise it can't populate the new one as it's too big.
There's no sence to compact it after the table has been populated.

I'm so close to it!

Please help!

Thanks.

 

[motleyjew]

The only suggestion I can think of is to run 2 jobs with the job scheduler. One can execute the script that will run the delete query and then set the database to compact on close. Then run another job schedule to activate another vbscript that activates another macro that will run the new table query. I can't see being able to compact and repair and continue a macro. I have seen code on the boards where there is compact VBA code. That may be worth looking into.

Gregg

 

[dz2k7]

Well I did that in one script
I just close and open it again.
But anyway there should be a way.

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''
'Update Microsoft ACCESS database
Option Explicit
dim oaccess
set oaccess = createobject("access.application")
oaccess.opencurrentdatabase "D:\db\AllCanadaInventory.mdb"
oaccess.docmd.openquery "qry1deleteolddata"
oaccess.closecurrentdatabase

oaccess.opencurrentdatabase "D:\db\AllCanadaInventory.mdb"
oaccess.docmd.runmacro "makeallinv"
oaccess.closecurrentdatabase
oaccess.quit
set oaccess=nothing

WScript.Quit(0)

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''

 

[dz2k7]

I found the script to compact the Access DB using VBS script
Just in case sombody's searching


'====================================
' File: CompactDB.vbs
' Author: Q-Built Solutions; www.QBuilt.com
' Date: 20 Aug. '04
' This script can be used to compact an Access
' database on a Windows 2000 or 2003 server that
' does not have Access installed. This script can
' compact any Microsoft Access database version
' 2000, 2002, or 2003, unless it has a database
' password.
' Windows Script and MDAC 2.1 or higher must be
' installed.
'====================================
Option Explicit
Call getFileInfo
Public Sub getFileInfo( )
On Error Resume Next
Dim sJetCnxn
Dim sDir
Dim sDB
Dim src
Dim dest
Dim fso
Dim ErrDesc
sJetCnxn = "Provider=Microsoft.Jet.OLEDB.4.0;Data source="
sDir = "D:\my documents\wolseley corporate\db files\" ' Set directory path here.
sDB = "All Canada Inv.mdb" ' Set database file name here.
src = sJetCnxn & sDir & sDB
dest = sJetCnxn & sDir & "Temp" & sDB
Set fso = CreateObject("Scripting.FileSystemObject")
If (Not (fso.FileExists(sDir & sDB))) Then
MsgBox "Database not found:" & vbCrLf & _
sDir & sDB, vbExclamation, _
"Compaction Failure!"
Else
If (fso.FileExists(sDir & "Temp" & sDB)) Then
MsgBox "Previous compaction incomplete." & _
vbCrLf & "Deleting old database.", _
vbInformation, "Compacting . . ."
fso.DeleteFile (sDir & "Temp" & sDB)
End If
ErrDesc = compactDB(src, dest)
'-----------------------------------------------------
' Determine whether compaction was successful.
' When error description = 0, then it's successful.
' Otherwise, textual error description was a failure
' that needs to be reported to the user.
'-----------------------------------------------------
If (IsNumeric(ErrDesc)) Then
fso.DeleteFile sDir & sDB
fso.MoveFile sDir & "Temp" & sDB, sDir & sDB
MsgBox "Compaction completed on" & _
vbCrLf & sDir & sDB, vbInformation, _
"Success!"
Else
MsgBox ErrDesc & vbCrLf & vbCrLf & _
"Compaction failed on" & vbCrLf & _
sDir & sDB, vbExclamation, _
"Compaction Failure!"
End If
End If
Set fso = Nothing
End Sub

Public Function compactDB(ByVal srcDB, ByVal destDB)
On Error Resume Next
Dim jet
Set jet = CreateObject("JRO.JetEngine")
jet.CompactDatabase srcDB, destDB
If (Err.Number = 0) Then
compactDB = False
Else
compactDB = Err.Description
Err.Clear
End If
Set jet = Nothing
End Function