asked on

How would I close a command window after starting an Access session?

I'm using a batch file to start Access and open the main form to a specific record. I'm using excel vba to write the batch file.

if exist ""C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE"" (
"Q:\Development Lab Test Requests\DataBase\Development Lab Project Requests DB.accdb" /x mcrEmail /cmd 1716
goto install
) else if exist ""C:\Program Files (x86)\Microsoft Office\Office16\MSACCESS.EXE"" (
"Q:\Development Lab Test Requests\DataBase\Development Lab Project Requests DB.accdb" /x mcrEmail /cmd 1716
goto install
)

Project: 1716

Batch File To Start Access and open to the submitted request record.

Open in new window

Access may be either one of the applications as listed above. (office 14 or Office 16). The /x mcrEmail and the /cmd 1716 will start Access to record 1716 (this of course changes with each additional record added to the database.

I have tried adding exit in a separate line after GOTO Install and have tried adding GOTO exit and then adding :exit.

Nothing I have tried seems to work. I realize that the Access program that was started will continue to run but I was hoping to find a way of closing the batch file command window after starting the Access app on the record specified.

What should I try?
Thanks,
Jeff

NVIT

> I have tried adding exit in a separate line after GOTO Install
Once it goes to Install, it won't return to the separate line after GOTO Install. So, Exit never runs.

> ...and have tried adding GOTO exit and then adding :exit
I think you mean "...and then adding EXIT" (without the colon). Since :EXIT by itself doesn't do anything.

Jeffrey Davidson

ASKER

It's been so long since I made the batch file (>15 years) I can't even remember why I used the Goto Install.

When I remove the GOTO Install and add EXIT on it's own line, the Batchfile closes immediately without starting the access record.

I'd like it to run the Access start record and then close the command window.

-Jeff

-Jeff

oBdA

Use "start" to run something in the background; note that the empty pair of quotes is required.

if exist "C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE" goto Start
if exist "C:\Program Files (x86)\Microsoft Office\Office16\MSACCESS.EXE" goto Start
goto :eof
:Start
start "" "Q:\Development Lab Test Requests\DataBase\Development Lab Project Requests DB.accdb" /x mcrEmail /cmd 1716

Open in new window

Jeffrey Davidson

ASKER

oMdA - I will give that a try, next week I'm on vacation the rest of this week and Monday.

Thanks,
Jeff

Jeffrey Davidson

ASKER

oBdA

When I tried this:

if exist "C:\Program Files (x86)\Microsoft Office\root\Office14\MSACCESS.EXE" goto Start
if exist "C:\Program Files (x86)\Microsoft Office\Office16\MSACCESS.EXE" goto Start
goto :eof
:Start
Start "" "K:\R&D Dept\Development Lab\R&D Test Request System (For testing and training)\DataBase\R&D Project Requests DB.accdb" /x mcrEmail /cmd 1695

Open in new window

My current access file is in K Drive and a have a record that I made being 1695.

This flashes the command window and then closes it but does not load the access record.

Thanks,
Jeff

oBdA

Try it like this:

set Access14=C:\Program Files (x86)\Microsoft Office\root\Office14\MSACCESS.EXE
set Access16=C:\Program Files (x86)\Microsoft Office\Office16\MSACCESS.EXE
if exist "%Access14%" (
	set Access=%Access14%
	goto Start
)
if exist "%Access16%" (
	set Access=%Access16%
	goto Start
)
goto :eof
:Start
start "" "%Access%" "K:\R&D Dept\Development Lab\R&D Test Request System (For testing and training)\DataBase\R&D Project Requests DB.accdb" /x mcrEmail /cmd 1695

Open in new window

Jeffrey Davidson

ASKER

Sorry that had the same effect as the last code. The command window flashes and nothing else happens.

oBdA

I can't test your command line, so just remove the start "" from the last line, open a command prompt, and run the batch from there (not by double-clicking the file in Explorer):

"%Access%" "K:\R&D Dept\Development Lab\R&D Test Request System (For testing and training)\DataBase\R&D Project Requests DB.accdb" /x mcrEmail /cmd 1695

Open in new window

Jeffrey Davidson

ASKER

oBdA - Did the same thing

Jeffrey Davidson

ASKER

Doe anyone else know anything about this?

oBdA

Since it works totally fine when I use C:\Windows\system32\notepad.exe instead of access.exe, either the path to your msaccess.exe is incorrect, or your command line.
This will tell you if access was found at one of the supported paths; as before, start it from an open command prompt:

set Access14=C:\Program Files (x86)\Microsoft Office\root\Office14\MSACCESS.EXE
set Access16=C:\Program Files (x86)\Microsoft Office\Office16\MSACCESS.EXE
if exist "%Access14%" (
	set Access=%Access14%
	goto Start
)
if exist "%Access16%" (
	set Access=%Access16%
	goto Start
)
echo ERROR: Found no install of Microsoft Access
exit 1
goto :eof
:Start
echo Found Microsoft Access: %Access%
REM start "" "%Access%" "K:\R&D Dept\Development Lab\R&D Test Request System (For testing and training)\DataBase\R&D Project Requests DB.accdb" /x mcrEmail /cmd 1695
"%Access%" "K:\R&D Dept\Development Lab\R&D Test Request System (For testing and training)\DataBase\R&D Project Requests DB.accdb" /x mcrEmail /cmd 1695

Open in new window

Jeffrey Davidson

ASKER

I tried

set Access14=C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Access.exe
set Access16=C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Access.exe
if exist "%Access14%" (
    set Access=%Access14%
    goto Start
)
if exist "%Access16%" (
    set Access=%Access16%
    goto Start
)
echo ERROR: Found no install of Microsoft Access
exit 1
goto :eof
:Start
echo Found Microsoft Access: %Access%
REM start "" "%Access%" "K:\R&D Dept\Development Lab\R&D Test Request System (For testing and training)\DataBase\R&D Project Requests DB.accdb" /x mcrEmail /cmd 1695
"%Access%" "K:\R&D Dept\Development Lab\R&D Test Request System (For testing and training)\DataBase\R&D Project Requests DB.accdb" /x mcrEmail /cmd 1695

Open in new window

because that is where my Access.exe is.

The batchfile closed but the access record was not loaded, Access didn't even appear to try and start.

ASKER CERTIFIED SOLUTION

oBdA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeffrey Davidson

ASKER

Your batch file works when I copy and paste it into a batchfile. I'm generating the batchfile in excel like this:

Public Function BuildBatchfile(Counter As Integer)
Dim fnum As Variant
Dim MyFile As String
'first set a string which contains the path to the file you want to create.
'this example creates one and stores it in the root directory
MyFile = c_Main_Drive & c_Main_Folder & c_Main_BatchFiles & "Project " & Counter & " " & Format(Date, "mm-dd-yyyy") & ".bat"
'set and open file for output
fnum = FreeFile()
Open MyFile For Output As fnum
'Batch file to open Access
Print #fnum, "if exist " & Chr(34) & Chr(34) & "C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE" & Chr(34) & Chr(34) & " (" & vbCrLf & Chr(34) & c_Main_Drive & c_Main_Folder & c_Main_Database_Folder & c_ReqDBName & Chr(34) & " " & "/x mcrEmail /cmd " & Counter & vbCrLf & "goto install" & vbCrLf & ") else if exist " & Chr(34) & Chr(34) & "C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" & Chr(34) & Chr(34) & " (" & vbCrLf & Chr(34) & c_Main_Drive & c_Main_Folder & c_Main_Database_Folder & c_ReqDBName & Chr(34) & " " & "/x mcrEmail /cmd " & Counter & vbCrLf & "goto install" & vbCrLf & ")"
Write #fnum,
'write project info and then a blank line. Note the comma is required
Print #fnum, "Project: " & Counter
Write #fnum,
'use Print when you want the string without quotation marks
Print #fnum, "Batch File To Start Access and open to the submitted request record."
Close #fnum
'BuildBatchFile = "Project " & Counter & " " & VBA.Format(Date, "mm-dd-yyyy") & ".bat"
End Function

Open in new window

I'm having trouble converting yours to a vba written batch file. Was trying like this

Public Function BuildBatchFile(Counter As Integer) As String
Dim fnum As Variant
Dim MyFile As String
'first set a string which contains the path to the file you want to create.
'this example creates one and stores it in the root directory
MyFile = c_Main_Drive & c_Main_Folder & c_Main_BatchFiles & "Project " & Counter & " " & Format(Date, "mm-dd-yyyy") & ".bat"
'set and open file for output
fnum = FreeFile()
Open MyFile For Output As fnum

'Batch file to open Access
fnum = fnum & "@echo off"
fnum = fnum & " setlocal"
fnum = fnum & " for /f"
fnum = fnum & " tokens=2 delims=="
fnum = fnum & " %%a in ('ftype accesshtmlfile') do set Access=%%a"
fnum = fnum & " if not defined Access ("
fnum = fnum & " echo No Access installation found!"
fnum = fnum & " exit /b 1"
fnum = fnum & " )"
fnum = fnum & " echo Found Access: %Access%"
fnum = fnum & "s tart "" & " & "%Access% " & "K:\R&D Dept\Development Lab\R&D Test Request System (For testing and training)\DataBase\R&D Project Requests DB.accdb" & "/x mcrEmail /cmd 1695"
fnum = fnum & " exit /b 0"
Debug.Print fnum
Print #fnum, "if exist " & Chr(34) & Chr(34) & "C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE" & Chr(34) & Chr(34) & " (" & vbCrLf & Chr(34) & c_Main_Drive & c_Main_Folder & c_Main_Database_Folder & c_ReqDBName & Chr(34) & " " & "/x mcrEmail /cmd " & Counter & vbCrLf & "goto install" & vbCrLf & ") else if exist " & Chr(34) & Chr(34) & "C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" & Chr(34) & Chr(34) & " (" & vbCrLf & Chr(34) & c_Main_Drive & c_Main_Folder & c_Main_Database_Folder & c_ReqDBName & Chr(34) & " " & "/x mcrEmail /cmd " & Counter & vbCrLf & "goto install" & vbCrLf & ")"
Write #fnum,

'use Print when you want the string without quotation marks
Print #fnum, "Batch File To Start Access and open to the submitted request record."
Close #fnum
'BuildBatchFile = "Project " & Counter & " " & VBA.Format(Date, "mm-dd-yyyy") & ".bat"
End Function

Open in new window

but, I'm not sure how to handle some of the characters like the inline quotes.

Jeffrey Davidson

ASKER

Thru trial and error I got it to work.

Public Function BuildBatchFile(Counter As Integer) As String
Dim fnum As Variant
Dim MyFile As String
'first set a string which contains the path to the file you want to create.
'this example creates one and stores it in the root directory
MyFile = c_Main_Drive & c_Main_Folder & c_Main_BatchFiles & "Project " & Counter & " " & Format(Date, "mm-dd-yyyy") & ".bat"
'set and open file for output
fnum = FreeFile()
Open MyFile For Output As fnum

'Batch file to open Access
Dim bfile As String
bfile = ""
bfile = bfile & "@echo off" + vbCrLf
bfile = bfile & "for /f ""tokens=2 delims==""" & " %%a in ('ftype accesshtmlfile') do set Access=%%a" + vbCrLf
bfile = bfile & "if not defined Access (" + vbCrLf
bfile = bfile & "    echo No Access installation found!" + vbCrLf
bfile = bfile & "    exit /b 1" + vbCrLf
bfile = bfile & ")" + vbCrLf
bfile = bfile & "echo Found Access: %Access%" + vbCrLf
bfile = bfile & "start """" %Access% ""K:\R&D Dept\Development Lab\R&D Test Request System (For testing and training)\DataBase\R&D Project Requests DB.accdb"" /x mcrEmail /cmd " & Counter & vbCrLf
bfile = bfile & "exit /b 0" + vbCrLf
bfile = bfile & vbCrLf + vbCrLf
bfile = bfile & "Sub test()" + vbCrLf
bfile = bfile & "    @echo off" + vbCrLf
bfile = bfile & "    setlocal" + vbCrLf
bfile = bfile & "    for /f ""tokens=2 delims=="" %%a in ('ftype accesshtmlfile') do set Access=%%a" + vbCrLf
bfile = bfile & "    if not defined Access (" + vbCrLf
bfile = bfile & "        echo No Access installation found!" + vbCrLf
bfile = bfile & "        exit /b 1" + vbCrLf
bfile = bfile & "    )" + vbCrLf
bfile = bfile & "    echo Found Access: %Access%" + vbCrLf
bfile = bfile & "   start """" %Access% ""K:\R&D Dept\Development Lab\R&D Test Request System (For testing and training)\DataBase\R&D Project Requests DB.accdb"" /x mcrEmail /cmd " & Counter & vbCrLf
bfile = bfile & "    exit /b 0" + vbCrLf
bfile = bfile & "End Sub"

Write #fnum,

'use Print when you want the string without quotation marks
'Print #fnum, "Batch File To Start Access and open to the submitted request record."
Close #fnum

End Function

Open in new window