Run VBA through .bat file

[Tigerlili3]

Is it possible to run and MS Access module through a .bat file without creating a macro? If so what is the syntax?

Thanks!
Debra

 

[oharab]

The closest you can get to this is: have a macro that calls your vba, then in your bat file use the /x switch on the command line.

eg c:\Office\access.exe "d:\apps\myDB.mdb" /xMyMacro

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------
Want to get great answers to your Tek-Tips questions? Have a

 

[Tigerlili3]

Thanks! That's what I've been doing. I was just hoping there was a more direct route.

 

[AccessGuruCarl]

Tigerlili3,

What does the module do...
You may be able to convert it to a vb script file.
These are similiar to bat files, but run vb code!
Depending on what the module is actually doing, vb script will work!

I'm not the one to ask about this vb script stuff, just learning about it myself!

I'm sure if you post the code in the VB Script forum,

http://www.tek-tips.com/threadminder.cfm?pid=329

with a heading like, Can I convert this VBA to VB Script,
they'll be able to solve your problem.

Hope this helps.
Carl

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[Tigerlili3]

Thanks Carl! I'll give that a shot.

The code exports an Access table to an excel file, imports an access table, runs two SQL queries (one delete, one append), and sends an email stating that the procedure has taken place.

 

[AccessGuruCarl]

Well, I think your reaching....
But one never knows, these guys have performed miracles...


Good Luck,
Carl

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[oharab]

Well, I think your reaching

Not really, pretty much anything you can do in VBA, you can do in VBS by using the access object model. The only limitation are api calls.
Post your code and I'll have a look at it for you.

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------
Want to get great answers to your Tek-Tips questions? Have a

 

[Tigerlili3]

Thanks oharab! Here it goes...

Function UpdateStructureFile()

Dim strFullPath As String 'path and name of found files
Dim strWhere As String 'Where clause of SQL string
Dim strFilename 'file name of structure file update

'turns off system warnings
DoCmd.SetWarnings False
'searches F drive for update files
Set fs = Application.FileSearch
    With fs
        .newsearch
        .LookIn = "F:\Access\"
        .filename = "*.mdb"
        If .Execute() > 0 Then
            For i = 1 To .foundfiles.Count
            'captures filename
            strFullPath = .foundfiles(i)
            strFilename = Left(Right(strFullPath, 10), 6)
            'if file(s) are found then the Where clause of e SQL statement are set
                If strFilename = "NatRev" Then
                    strWhere = "(((tblStructure.EVT)='NAT')" & _
                                " AND ((tblStructure.STAT)='REV'))"
                ElseIf strFilename = "NatNon" Then
                    strWhere = "(((tblStructure.EVT)='NAT')" & _
                                " AND ((tblStructure.STAT)='NREV'))"
                ElseIf strFilename = "MorRev" Then
                     strWhere = "(((tblStructure.EVT)='MOR')" & _
                                " AND ((tblStructure.STAT)='REV'))"
                ElseIf strFilename = "MorNon" Then
                    strWhere = "(((tblStructure.EVT)='MOR')" & _
                                " AND ((tblStructure.STAT)='NREV'))"
                ElseIf strFilename = "FetRev" Then
                    strWhere = "(((tblStructure.EVT)='FET')" & _
                                " AND ((tblStructure.STAT)='REV'))"
                ElseIf strFilename = "FetNon" Then
                    strWhere = "(((tblStructure.EVT)='FET')" & _
                                " AND ((tblStructure.STAT)='NREV'))"
                End If
                'archives tblStructure when there is an update to the structure files
                DoCmd.TransferSpreadsheet acExport, 8, "tblStructure", "F:\Access\Archive\tblStructure_" & Format(Now(), "mmddyy"), False, ""
                'imports the new structure file if there is one
                DoCmd.TransferDatabase acImport, "Microsoft Access", "F:\Access\" & _
                strFilename & ".mdb", acTable, strFilename, strFilename, False
                
                'runs SQL statement to delete existing records in tblStructure
                DoCmd.RunSQL "DELETE tblStructure.EVT, tblStructure.STAT, tblStructure.ID, tblStructure.Seq," & _
                                " tblStructure.FullLabel, tblStructure.AbbrevLabel, tblStructure.Pos," & _
                                " tblStructure.Start, tblStructure.End, tblStructure.VarLabel, tblStructure.Type," & _
                                " tblStructure.Length, VarName, Apps" & _
                                " FROM tblStructure" & _
                                " WHERE" & strWhere & ";"
                                
                'runs SQL statement to insert new records into tblStructure from the new file
                DoCmd.RunSQL "INSERT INTO tblStructure ( EVT, STAT, ID, Seq, FullLabel, AbbrevLabel, Pos, Start, [End]," & _
                                " VarLabel, Typ, Length, VarName, Apps )" & _
                                " SELECT " & strFilename & ".EVT, " & strFilename & ".STAT, " & strFilename & ".ID, " & strFilename & ".Seq, " & strFilename & ".FullLabel, " & strFilename & ".AbbrevLabel, " & _
                                strFilename & ".Pos, " & strFilename & ".Start," & strFilename & ".End," & strFilename & ".VarLabel, " & strFilename & ".Typ, " & strFilename & ".Length, " & strFilename & ".VarName, " & _
                                strFilename & ".Apps " & _
                                " FROM " & strFilename & ";"
                                
                'deletes the newly imported table since its records have been appended to tblStructure
                DoCmd.DeleteObject acTable, strFilename
                'deletes the database file from the F drive
                'Kill strFullPath
            Next i 'loop
            'sends email notification when file has been updated
            Set appOutLook = CreateObject("Outlook.Application")
            Set MailOutLook = appOutLook.CreateItem(olMailItem)
            With MailOutLook
                .To = ""
                .Subject = " Test Structure File Update"
                .Body = "This is only  test.  The structure file in was updated on " & Format(Now(), "mm/dd/yyyy") & "."
                .Send
            End With
        Else
            'exit
            DoCmd.Quit
        End If
    End With
'turns on system warnings
DoCmd.SetWarnings True
DoCmd.Quit
End Function

The line lengths are quite different on this screen than in the VBA window, so I apologize for how messy it looks.

Thanks for your help!