Using the ShellExecute command to activate a program

[gknierim]

Heres my situation. I have a csv file that I want to read and put into a SQL Server 6.5/7 database. I already have a VB app running and I was going to make this function part of the existing program. My question is how exactly would I do this? Code examples would be great as I have no idea where to start. Some other things that might be important: I want to use the BCP utility that comes with SQL Server because this program is supposed to take care of reading the file and putting into the database. The problem I have is writing code to get to that point. (i.e. Opening connection..activating shell command for BCP,..closing connection.) This means I need some code from beginning to end. If anyone has even a remote idea on how to do this, I would greatly appreciate any sugggestions. Also, I have searched alot of VB code sites and can't seem to find any for this example. If you know of one that does, let me know.

Thanks,
Greg

[Johnny101]

Here is the code I use for this exact operation:

private Declare Function OpenProcess Lib "kernel32.dll" (byval _
dwAccess as Long, byval fInherit as Integer, byval hObject _
as Long) as Long

private Declare Function WaitForSingleObject Lib "kernel32" (byval _
hHandle as Long, byval dwMilliseconds as Long) as Long

private Declare Function CloseHandle Lib "kernel32" (byval _
hObject as Long) as Long

private Const SYNCHRONIZE = &H100000
private Const INFINITE = -1&

Sub BCPFile()
Dim lProcessID as Long
Dim lProcessHandle as Long
Dim lTimeOut as Long
Dim lReturn as Long
Dim sFldSep as string
Dim sCmd as string
Dim nBatFile as Integer
Dim nResultsFile as Integer

'set field seperator
sFldSep = "~"

'Build command string
sCmd = "bcp "
sCmd = sCmd & " " & gsDatabaseName & ".." & gsTableName
sCmd = sCmd & " in "
sCmd = sCmd & gsBCPFileName
sCmd = sCmd & " /e " & gsBCPErrFile
sCmd = sCmd & " /t " & sFldSep
sCmd = sCmd & " /c"
sCmd = sCmd & " /U " & gsUID
sCmd = sCmd & " /P " & gsPWD
sCmd = sCmd & " /S " & gsServer
sCmd = sCmd & " > " & gsBCPResultsFile

'now save the above bcp command to a .bat file

'Create batch file
nBatFile = FreeFile
Open gsBCPBatFile for Output as #nBatFile
print #nBatFile, sCmd
Close #nBatFile

'now execute the command and wait for the process to finish...
lProcessID = Shell(gsBCPBatFile, vbMinimizedNoFocus)
lProcessHandle = OpenProcess(SYNCHRONIZE, true, lProcessID)
WaitForSingleObject lProcessHandle, lTimeOut
CloseHandle lProcessHandle
End Sub




You will have to modify this for you own use, of course, but this should do what you need it to. It has been my experience that the more unique the field seperator is the less likely the bcp program is to error out. A comma is nice, but if your field data has a comma, bcp will think that there's an extra column in the source and SQL Server will get an insert error (number of fields specified dont match number fields provided-or something like that). Hence the tilda for my seperator.

Hope this helps,
John


John Pirkey
MCSD
www.ShallowWaterSystems.com

[gknierim]

Thanks, Johnny. This will come in very helpful. However, yesterday I have found a bcp.ocx utility that will do exactly what I need also. There is of course alot more options but it eliminates the use of the ShellExecute command. The ocx is shareware but for a registration fee ($35), you can get the full OCX. It can be viewed at http://www.bbss.com/products.htm Check it out when you can. Thanks again.