If you encounter a situation where the Client Access batch data transfer programs do no meet your needs, there are three alternatives:
'cscript [host options...] [script name] [script options and parameters 'cscript //B "C:\transfer.vbs" fileName 'ex. cscript //B C:\Transfer.vbs C:\Request.dtf Dim dbXfer 'As New cwbx.DatabaseTransfer 'Get input args for script. 'One input argument for transfer file name Dim I 'as Integer Dim fileName 'as String Dim objArgs Set objArgs = WScript.Arguments 'Print out arugments For I = 0 To objArgs.Count - 1 WScript.Echo objArgs(I) Next If objArgs.Count > 0 Then fileName = objArgs(0) 'Create the output file Set dbXfer = CreateObject("cwbx.DatabaseTransfer") 'Specify the user ID and password. If incorrect, user will 'NOT be prompted, and an error is raised. dbXfer.UserID = "MYUSR" dbXfer.Password = "MYPWD" dbXfer.Transfer fileName If dbXfer.Errors.Count = 0 Then WScript.Echo "File successfully transferred." End If Else WScript.Echo "Transfer request not specified." End If
'cscript [host options...] [script name] [script options and parameters 'cscript //B "C:\xfer.vbs" /parm1 /parm2 ' 'You can create a script file with the options: '[ScriptFile] 'Path=C:\WINNT\Samples\WSH\showprop.vbs '[Options] 'Timeout = 0 'DisplayLogo = 0 'BatchMode = 1 Const adClipString = 2 Dim cn 'As New ADODB.Connection Dim rs 'As ADODB.Recordset Dim FSO 'As Scripting.FileSystemObject Dim hFile 'As Scripting.TextStream Dim PcFileName 'Get input args for script. You can pass file names in... Dim I 'as Integer dim objArgs Set objArgs = WScript.Arguments PcFileName = Mid(objArgs(0), 2) 'display all input args 'For I = 0 To objArgs.Count - 1 'WScript.Echo objArgs(I) 'Next 'Create the output file Set FSO = CreateObject("Scripting.FileSystemObject") Set hFile = FSO.CreateTextFile(PcFileName) 'Download the target file Set cn = CreateObject("ADODB.Connection") cn.Open "DSN=RCHAS194;", "MYUSER", "MYPWD" Set rs = cn.Execute("Select * from qiws.qcustcdt", , -1) 'You can format and write the data yourself or use one of 'the ADO provided output types 'This saves as ADTG or XML 'rs.Save "C:\test.txt", adPersistADTG 'You can use GetString for simple tab or comma delimited file Dim rd 'as String rd = vbCr & vbLf 'comma delimited with crlf record delimeter S = rs.GetString(adClipString, , ",", rd, " ") hFile.Write S WScript.Echo "File successfully written" Upload (RFromPCx.vbs)
'cscript [host options...] [script name] [script options and parameters 'cscript //B "C:\rfrompcx.vbs" /MYLIB /MYFILE /C:\file.txt '[ScriptFile] 'Path=C:\WINNT\Samples\WSH\showprop.vbs '[Options] 'Timeout = 0 'DisplayLogo = 0 'BatchMode = 1 Const ForReading = 1 Dim cn 'As New ADODB.Connection Dim cmd 'As New ADODB.Command Dim md 'As ADODB.Recordset Dim NumCols Dim FSO 'As Scripting.FileSystemObject Dim hFile 'As Scripting.TextStream Dim Lib 'As String Dim TableName 'As String Dim PCFileName 'As String Dim S 'As String Dim I 'As Long 'Read in parms Dim objArgs Set objArgs = WScript.Arguments For I = 0 To objArgs.Count - 1 WScript.Echo objArgs(I) Next Lib =Trim( Mid(objArgs(0), 2) ) Tablename = Trim(Mid(objArgs(1), 2) ) PCFileName = Trim(Mid(objArgs(2), 2) ) 'Connect Set cn = CreateObject("ADODB.Connection") cn.Open "DSN=RCHAS194;","user", "pwd" 'Get some basic metadata on the AS/400 file. 'Could use Set md = cn.OpenSchema(adSchemaColumns, Array(vEmpty, Lib, FileName)) Set md = cn.Execute( _ "SELECT COLUMN_COUNT from QSYS2.SYSTABLES WHERE TABLE_NAME = '" & _ Trim(TableName) & "' AND TABLE_OWNER = '" & Trim(Lib) & "' ") If md.EOF Then 'File does not exist. End If NumCols = md(0).Value 'Clear file if desired cn.Execute "DELETE FROM " & Lib & "." & TableName 'Prepare the insert Set cmd = CreateObject("ADODB.Command") cmd.Prepared = True Set cmd.ActiveConnection = cn S = "INSERT INTO " & Lib & "." & TableName & " VALUES (" For I = 1 To NumCols - 1 S = S & "?, " Next S = S & " ? ) " cmd.CommandText = S cmd.Parameters.Refresh 'Prepare the statement 'Open the PC file Set FSO = CreateObject("Scripting.FileSystemObject") Set hFile = FSO.OpenTextFile(PCFileName, ForReading) 'Read in the file and insert the row (this assumes CSV) Dim RowData Do While Not hFile.AtEndOfStream LineNum = LineNum + 1 'Read on line S = hFile.ReadLine 'Parse the data 'This will handle a simple CSV with no double quoted strings or 'embedded commas. More elaborate parsing may be needed. RowData = Split(S, ",") If UBound(RowData) - LBound(RowData) + 1 <> NumCols Then Report error for column mismatch End If '"Cast" the data to the correct type. 'You can use variant for most types. If needed you 'can add special handling for each type below. Special handling 'may be needed for date/time/timestamp and binary(65535) fields. For I = 0 To NumCols - 1 Select Case cmd.Parameters(I).Type Case adChar, adVarChar, adLongVarChar Case adInteger, adSmallInt Case adDouble, adSingle Case adNumeric, adDecimal Case adBigInt Case adVarBinary, adBinary Case adDBDate Case adDBTime Case adDBTimeStamp Case Else cmd.Parameters(I).Value = RowData(I) End Select Next 'For I = 0 To NumCols - 1 cmd.Execute Loop 'While Not hFile.AtEndOfStream WScript.Echo "Successfully inserted " & LineNum & " rows of data."
Still have questions? We can help. Submit a case to Technical Support.