Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

batch command into VBA code

Status
Not open for further replies.
aspnetuser

aspnetuser

Technical User
Sep 9, 2004
273
US
Hello.
I am currently using a batch file to execute a mass update when users open my db. Here is the batch file the click.

Update.bat
------------------------------------------------------

rem If the latest version file is on the PC, just start the database
if exist "PTS.1.24.txt" goto Startup

copy "\\path\share\DBApps\PTS.adp"
copy "TEST.adp" "C:\Documents and Settings\All Users\Desktop\TEST.adp"
copy "\\path\share\DBApps\1.24.txt"

:Startup
rem fire it up
"C:\Documents and Settings\All Users\Desktop\TEST.adp"

exit
---------------------------------------

This works fine but I want to move this mentality into my access db when it is started up. If it finds the text file locally, just open, if not, close and copy new version


possible?
 
Sort by date Sort by votes
Try this...

Use the Shell command.

Move below into Autoexec or On Open of startup form.

Dim stAppName As String

stAppName = "C:\LocationofBatchFile\Name.bat"
Call Shell(stAppName, 1)

Hope this helps.
 
Upvote 0 Downvote
file not found.

is there any way i can incorporate the batch code into the access autoexec startup. i don;t want to use batch files just pure access.
 
Upvote 0 Downvote
You might be able to do this using what is called a "Library Database" - which is nothing more than an Access database renamed with a .mda extension. Once you build the library database, you reference it in your project just like an activex control, and it can operate independently of the current database like an add-in.

It might also be possible to do it by creating a new Workspace object to Close() the current database and OpenDatabase(filename) around the file copy operation.

VBSlammer
redinvader3walking.gif

[sleeping]Unemployed in Houston, Texas
 
Upvote 0 Downvote
I think the startup form would be the easiest way to call the batch file:
Code: 
[green]'============= startup form =================[/green]

Private Sub Form_Load()
  If GetSetting(APP_NAME, "Updates", "Version") <> VERSION Then
    SaveSetting APP_NAME, "Updates", "Version", VERSION
  Else
    Call UpdateCheck
  End If
End Sub


[green]'================= module ===================[/green]

Public Const APP_NAME = "DB Pro"
Public Const VERSION = 1.24

Sub UpdateCheck()
  
  Dim strConfirmed As String
  Dim strBatch As String
  
  strConfirmed = "\\path\share\DBAPPS\" & VERSION & ".txt"
  strBatch = "\\path\share\DBAPPS\update.bat"
  
  If dir(strConfirmed) = "" Then
    SaveSetting APP_NAME, "Updates", "Version", ""    'clear reg value
    shell strBatch
    Application.Quit
  End If

End Sub

VBSlammer
redinvader3walking.gif

[sleeping]Unemployed in Houston, Texas
 
Upvote 0 Downvote
vbslammer,

Cool suggestion on the startup form,
do i leave my batch file as is andadd your code?

please advise?
 
Upvote 0 Downvote
also can you comment the code a what it writes to the client pcs, etc registry?


I am unfamilar with vba.
 
Upvote 0 Downvote
This is a way for the app to read/write a version setting to the registry, so it can compare the setting with a hard-coded version number at startup. A match indicates that the app is current. A mis-match means the app has been updated already, so a new setting is written. For every release, the hard-coded version just needs to get bumped up.

There might be a better way to do the file copies before shutdown using the FileSystemObject, and just let the batch file open the app after shutdown.

Code: 
[green]'============= startup form =================[/green]

Private Sub Form_Load()
  [green]'compare the registry's version key with the
  'hard-coded version number.[/green]
  If GetSetting(APP_NAME, "Updates", "Version") <> VERSION Then
    [green]'if not a match, we're starting up for the 
    'first time after an update, so write the version key
    'to the registry.[/green]
    SaveSetting APP_NAME, "Updates", "Version", VERSION
  Else
    [green]'the key matched, so check to see if an 
    'update is available.[/green]
    Call UpdateCheck
  End If
End Sub


[green]'================= module ===================[/green]

[green]'global constants[/green]
Public Const APP_NAME = "DB Pro"
Public Const VERSION = 1.24

Sub UpdateCheck()
  
  Dim strConfirmed As String
  Dim strBatch As String
  
  [green]'set your file locations as needed.[/green]
  strConfirmed = CurrentProject.Path & "\" & VERSION & ".txt"
  strBatch = "\\path\share\DBAPPS\update.bat"
  
  [green]'if the update has already run, a local file
  'with the current version will exist.[/green]
  If dir(strConfirmed) = "" Then
    [green]'if it doesn't, clear the registry key value
    'to let the new copy of the app know it's been updated.[/green]
    SaveSetting APP_NAME, "Updates", "Version", ""
    [green]'run the batch file.[/green]
    shell strBatch
    [green]'shut down - nothing more I can do.[/green]
    Application.Quit
  End If

End Sub

VBSlammer
redinvader3walking.gif

[sleeping]Unemployed in Houston, Texas
 
Upvote 0 Downvote
Thanks for the explanation, very helpful.

I am still neverous about writing registry, can you substitue that with writning a text file?

v1.1.txt to the C: drive?
 
Upvote 0 Downvote
The VBA registry methods are really just wrappers around the system API calls, so they are very safe. I've never had a problem writing a string key using these methods in any of my apps since VB5. The plus side is that you can even relocate the app or delete its entire directory and when it launches it can still check the version setting from the registry.

As far as reading/writing file values, the FileSystemObject is the best standard tool (COM) available to us. If you use a file format like Windows .INI files, such as:
Code: 
[Options]
sound=1
dropdowns=1
maxpages=4
runtime=0

[Updates]
version=1.24
updated=1/1/04
You can read/write key values in the file using the following functions like this:
Code: 
strKeyVal = GetIniValue("C:\Windows\MyApp.ini", "Updates", "version") 

blnSuccess = PutIniValue("C:\Windows\MyApp.ini", "Updates", "version", "1.25")

Here are the functions for reading and writing .INI file values:
Code: 
[green]'********************
'*  §lamKeys §oftware 2000® (VBSlammer)
'*
'*  @CREATED  :   1/25/2005 11:58:01 AM
'*  @PARAMS   :   strFileName - path and filename of ini file
'*            :   strSection  - section name, i.e. "Updates"
'*            :   strKey      - key name, i.e. "version"
'*            :
'*  @RETURNS  :   Key value, or "" if not found.
'*  @NOTES    :   Displays MsgBox if file not found.
'*  @MODIFIED :
'********************[/green]
Function GetIniValue(ByVal strFileName As String, _
                      ByVal strSection As String, _
                      ByVal strKey As String) As String
On Error GoTo ErrHandler
  Dim fso As New FileSystemObject
  Dim ts As TextStream
  Dim lngCtr As Long
  Dim strLine As String
  
  Set ts = fso.OpenTextFile(strFileName, ForReading)
  
  Do Until ts.AtEndOfStream
    strLine = ts.ReadLine
    If strLine = "[" & strSection & "]" Then
      Do
        strLine = ts.ReadLine
        If Left(strLine, Len(strKey) + 1) = (strKey & "=") Then
          GetIniValue = mid(strLine, Len(strKey) + 2)
          Exit Do
        End If
      Loop Until ts.AtEndOfStream
      Exit Do
    End If
  Loop

ExitHere:
  On Error Resume Next
  ts.Close
  Set ts = Nothing
  Set fso = Nothing
  Exit Function
ErrHandler:
  Select Case Err
    Case 53
      MsgBox "Could not find file: " & strFileName, vbInformation, "Invalid Filename"
    Case Else
      Debug.Print Err, Err.Description
  End Select
  Resume ExitHere
End Function

[green]'********************
'*  §lamKeys §oftware 2000® (VBSlammer)
'*
'*  @CREATED  :   1/25/2005 11:57:55 AM
'*  @PARAMS   :   strFileName - path and filename of ini file
'*            :   strSection  - section name, i.e. "Updates"
'*            :   strKey      - key name, i.e. "version"
'*            :   strValue    - key value, i.e. "1.24"
'*            :
'*  @RETURNS  :   True if success, false otherwise.
'*  @NOTES    :   Displays MsgBox if file not found.
'*  @MODIFIED :
'********************[/green]
Function PutIniValue(ByVal strFileName As String, _
                      ByVal strSection As String, _
                      ByVal strKey As String, _
                      ByVal strValue As String) As Boolean
On Error GoTo ErrHandler
  Dim fso As New FileSystemObject
  Dim ts As TextStream
  Dim lngCtr As Long
  Dim strLine As String
  Dim strDocument As String
  
  Set ts = fso.OpenTextFile(strFileName, ForReading)
  
  Do Until ts.AtEndOfStream
    strLine = ts.ReadLine
    strDocument = strDocument & strLine & vbCrLf
    If strLine = "[" & strSection & "]" Then
      Do
        strLine = ts.ReadLine
        
        If Left(strLine, Len(strKey) + 1) = (strKey & "=") Then
          strDocument = strDocument & strKey & "=" & strValue & vbCrLf
          PutIniValue = True
          Exit Do
        Else
          strDocument = strDocument & strLine & vbCrLf
        End If
      Loop Until ts.AtEndOfStream
    End If
  Loop
  
  ts.Close

  Set ts = fso.OpenTextFile(strFileName, ForWriting)
  ts.Write strDocument

ExitHere:
  On Error Resume Next
  ts.Close
  Set ts = Nothing
  Set fso = Nothing
  Exit Function
ErrHandler:
  Select Case Err
    Case 53
      MsgBox "Could not find file: " & strFileName, vbInformation, "Invalid Filename"
    Case Else
      Debug.Print Err, Err.Description
  End Select
  Resume ExitHere
End Function

In order to use the FileSystemObject you'll need to reference the "Micrsoft Scripting Runtime" using the [tt]Tools --> References[/tt] menu in the vba editor.

VBSlammer
redinvader3walking.gif

[sleeping]Unemployed in Houston, Texas
 
Upvote 0 Downvote
A note to the API people: I realize you can read/write to ini files using the API calls: GetPrivateProfileString() and WritePrivateProfileString(), but I wanted to demonstrate the FileSytemObject's usage for reading and writing files.

FileSystemObject methods:
[tt]
BuildPath
CopyFile
CopyFolder
CreateFolder
CreateTextFile
DeleteFile
DeleteFolder
DriveExists
FileExists
FolderExists
GetAbsolutePathName
GetBaseName
GetDrive
GetDriveName
GetExtensionName
GetFile
GetFileName
GetFolder
GetParentFolderName
GetSpecialFolder
GetTempName
MoveFile
MoveFolder
OpenTextFile
[/tt]

VBSlammer
redinvader3walking.gif

[sleeping]Unemployed in Houston, Texas
 
Upvote 0 Downvote
Change the VERSION value to a string:

[tt]Public Const VERSION = "1.24"[/tt]

VBSlammer
redinvader3walking.gif

[sleeping]Unemployed in Houston, Texas
 
Upvote 0 Downvote
VB
How can I check the registry update via regedit to see what is going on?
 
Upvote 0 Downvote
VBA programs all write to the same registry parent folder located at:

[tt]HKEY_CURRENT_USER\Software\VB and VBA Program Settings\[/tt]

Your settings will be in the folder named whatever you used for APP_NAME.

Click [tt]start button --> run --> type "regedit.exe" --> Click OK[/tt]

VBSlammer
redinvader3walking.gif

"You just have to know which screws to turn." - Professor Bob
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Moss100
  • Locked
  • Question
Filter vba problem
Replies
1
Views
729
Andrzejek
Andrzejek
Omarahmed22
  • Locked
  • Question
VBA Formating
Replies
1
Views
652
Andrzejek
Andrzejek
M
  • Locked
  • Question
display a calendar with appointments in body of html email with access vba
Replies
1
Views
4K
SoftwareRT
SoftwareRT
Moss100
  • Locked
  • Question
Vba code I can call from a query to reformat text
Replies
8
Views
623
Moss100
Moss100
TheAceMan1
  • Locked
  • Question
Delete from listi n XL open tab with VBA
Replies
2
Views
536
TheAceMan1
TheAceMan1

Part and Inventory Search

Sponsor

Back
Top