using a DOS Command-Line via VBA.

[DerickD]

Hi All,

I want to run a Command-Line function 'bcp' (It comes with SQLServer as a Bulk Copy Program), and it will only run from within dos.

I have been testing using the code :

Call Shell bcp 'ServerName'dbo.'TableName' 'OutputName' -e c:\errors.txt -U  -P  -S 'ServerName' -f MosaixDownload.fmt

It should open cmd promt and run the command, but the dos window just flashes open and nothing is executed..,.?? (I have copied the text to the Command Promt and it works fine, so there is no problem with the command.

Is there a way I can run the bcp.exe from within Access, either through the Command line or directly within VBA including all the needed parameters..

Thank in advance.

Derick

 

[beetee]

Try this (use whatever command line you want as the argument to the ExecCmd function):


' this code was lifted from the internet.
' purpose: to shell out a command and wait until
' the command is done.

Option Compare Database
Option Explicit

Private Type STARTUPINFO
cb As Long
lpReserved As String
lpDesktop As String
lpTitle As String
dwX As Long
dwY As Long
dwXSize As Long
dwYSize As Long
dwXCountChars As Long
dwYCountChars As Long
dwFillAttribute As Long
dwFlags As Long
wShowWindow As Integer
cbReserved2 As Integer
lpReserved2 As Long
hStdInput As Long
hStdOutput As Long
hStdError As Long
End Type

Private Type PROCESS_INFORMATION
hProcess As Long
hThread As Long
dwProcessID As Long
dwThreadID As Long
End Type

Private Declare Function WaitForSingleObject Lib "kernel32" ( _
ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long

Private Declare Function CreateProcessA Lib "kernel32" ( _
ByVal lpApplicationName As String, ByVal LpCommandLine As String, _
ByVal lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As String, _
lpStartupInfo As STARTUPINFO, lpProcessInformation As PROCESS_INFORMATION) As Long

Private Declare Function CloseHandle Lib "kernel32" _
(ByVal hObject As Long) As Long

Private Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, lpExitCode As Long) As Long

Private Const NORMAL_PRIORITY_CLASS = &H20&
Private Const INFINITE = -1

Public Function ExecCmd(CmdLine As String)
DoCmd.Hourglass True
Dim Proc As PROCESS_INFORMATION
Dim Start As STARTUPINFO

Start.cb = Len(Start)

Dim ret As Long
ret = CreateProcessA(vbNullString, CmdLine, 0&, 0&, 1&, _
NORMAL_PRIORITY_CLASS, 0&, vbNullString, Start, Proc)

ret = WaitForSingleObject(Proc.hProcess, INFINITE)
Call GetExitCodeProcess(Proc.hProcess, ret)
Call CloseHandle(Proc.hThread)
Call CloseHandle(Proc.hProcess)
ExecCmd = ret
DoCmd.Hourglass False
End Function

 

[mikevh]

Derick, the following is from the VBA help. Note the
second sentence.

To call a Sub procedure from another procedure, type the name of the procedure and include values for any required arguments. The Call statement is not required, but if you use it, you must enclose any arguments in parentheses.

So, if you're going to use Call, surround the arguments to
Shell with parens. Or just say Shell without Call and forget about the parens.