Solved

Converting this vba to vbs

Posted on 2011-09-27
5
248 Views
Last Modified: 2012-05-12
The code below opens an access database by calling the Access Run Time.  This is exactly what I need to do, but I need to do it from vbScript.  Can anyone help by translating the below code into working .vbs for me?

Thanks.
Sub OpenRunTime()
     Dim accpath As String, dbpath As String
     On Error Resume Next
     dbpath = "C:\My Application\MyApp.mdb"
     Set objAccess = GetObject(dbpath)
     If Err <> 0 Then
       If Dir(dbpath) = "" Then 'dbpath is not valid
         MsgBox "Couldn't find database."
         Exit Sub
       Else  'The full version of Microsoft Access is not installed.
         accpath = "C:\Program Files\Common Files\Microsoft Shared" & _
              "\Microsoft Access Runtime\MSAccess.exe"
         If Dir(accpath) = "" Then
            MsgBox "Couldn't find Microsoft Access."
            Exit Sub
         Else
            Shell pathname:=accpath & " " & Chr(34) & dbpath & Chr(34), _
              windowstyle:=6
            Do 'Wait for shelled process to finish
              Err = 0
              Set objAccess = GetObject(dbpath)
            Loop While Err <> 0
         End If
       End If
     End If
   End Sub

Open in new window

0
Comment
Question by:snyperj
  • 3
  • 2
5 Comments
 
LVL 8

Expert Comment

by:GundogTrainer
ID: 36710199

There are a few bit that need to be done differently but...

 Can you confirm this is what is should do:

1, check C:\My Application\MyApp.mdb exists
2. check MSAccess.exe exists
3. spawn msaccess.exe to open the mdb file in full screen and wait for it to exit.
4. quit
0
 
LVL 8

Accepted Solution

by:
GundogTrainer earned 500 total points
ID: 36710437
Just a quick explanation of the nasty string in the Wshshell bit at the end.

As its executing it from the shell script it needs to be in quotes to maintain names with spaces, I know you can do it with 2 double quotes but it get confusing real quick if you have any problems so I choose to use char(34) rather than being too clever.
openruntime

Sub OpenRunTime
     Dim accpath , dbpath
'    On Error goto 0
     dbpath = "C:\My Application\MyApp.mdb"
     accpath = "C:\Program Files\Common Files\Microsoft Shared\Microsoft Access Runtime\MSAccess.exe"
     Set objFSO = CreateObject("Scripting.FileSystemObject")
     set LogFile=objfso.opentextfile("dvdlog.txt",2,True)

       If objFSO.FileExists(dbpath) = false then
         MsgBox "Couldn't find database."
         exit sub
       end if
       If objFSO.FileExists(accpath) = false then
            MsgBox "Couldn't find Microsoft Access."
         exit sub
       End If

       'If the database of Exe didnt exist we wount have got here !
     Set WshShell = WScript.CreateObject("WScript.Shell")
     Return = WshShell.Run(chr(34) & accpath & chr(34) & " " & chr(34) & dbpath & chr(34) , 3, true)
   End Sub

Open in new window

0
 

Author Comment

by:snyperj
ID: 36710795
Great job thank you very much- works like a champ.

Can you explain to me what this line does?

set LogFile=objfso.opentextfile("dvdlog.txt",2,True)



0
 
LVL 8

Expert Comment

by:GundogTrainer
ID: 36710835
Doh...
thats the old grabed a line of code that I always use when opening FileSystem Objects and forget to delete it !!! (I tend to keep a log in most of my scripts as an audit trail) its doing nothing - please delete it with my appologies.


0
 

Author Comment

by:snyperj
ID: 36710852
No worries, I figured as much!  Thanks again.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Messed up my code 6 34
ASP CDO - Limiting Connections 2 40
Problem to With line 4 43
File download script VBSCRIPT fail on download PPT files 2 36
Unlike scripting languages such as C# where a semi-colon is used to indicate the end of a command, Microsoft's VBScript language relies on line breaks to determine when a command begins and ends. As you can imagine, this quickly results in messy cod…
Welcome back!  My apologies for taking so long to write part two of this series; it's been a long time coming!  As I promised in Part 1, this article will focus on how to locate those elusive AD properties that you are searching for.  Why is this us…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now