Importing Text Stream to Access Table w/ VBA 1

[krizma5683]

I'm trying to import a text file using a textstream in VBA and when my codes hits the currentdb.execute command it is randomly skipping records of the text stream for no obvious reason.

However, when I count the records, it does count all records in the text file, but doesn't execute on all records.

Code is quite lengthy, but I can post if needed.

Any suggestions are welcomed - thanks!!

 

[Frink]

Hallo,

It may be helpful to post your code, or some of it anyway.
Your open and read lines would be good for starters

- Frink

 

[krizma5683]

I decided to just post the whole code, mainly there are approx. 72 columns I'm bringing in, which has created the code to be so long.

Sub GetElecRawdata()

Dim strLine As String
Dim strBudYr As String
Dim strDist As String
Dim strRegion As String
Dim strResZn As String
Dim strSerAreaCd As String
Dim strSerArea As String
Dim strOpsZn As String
Dim strArea As String
Dim strProjType As String
Dim strPlanPriority As String
Dim intWR As String
Dim strWRDesc As String
Dim strBusProc As String
Dim strPlanApprove As String
Dim dtPlanAppDate As Date
Dim strPlanAppBy As String
Dim strProjMgr As String
Dim strPlanner As String
Dim strProjPriority As String
Dim dtFinishDate As Date
Dim intFinCrewHQ As Integer
Dim intWOinstall As String
Dim curBudYrDol As Currency
Dim intEVA As String
Dim intBCR As String
Dim strCompany As String
Dim dtSchedStart As Date
Dim intStatus As Integer
Dim strStatDescr As String
Dim dtWRentered As Date
Dim curMatl As Currency
Dim curEquipOH As Currency
Dim curTruckOH As Currency
Dim curMatlTrkStock As Currency
Dim curAddItem As Currency
Dim curCoLabor As Currency
Dim curContLabor As Currency
Dim dtNewAsOf As Date
Dim strOperAssign As String
Dim curLTDIRM As Variant
Dim curYTDIRM As Variant
Dim strDistEng As String
Dim intOperUnit As Integer
Dim dtWRComplete As Date
Dim intSortPriority As Integer
Dim strScoped As String
Dim intFeederID As String
Dim strSubName As String
Dim strBusReason As String
Dim strBusObj As String
Dim curBudYrProjDol As Currency
Dim curTotalBudDol As Currency
Dim curBudYrCrDol As Currency
Dim curTotalCrDol As Currency
Dim curTotalProjDol As Currency
Dim dtScopedYr As Date
Dim strBusProcDescr As String
Dim intSortReq As Integer
Dim strRedlineNeed As String
Dim intEngLaborHrs As Integer
Dim intConstLaborHrs As Integer
Dim strPopDens As String
Dim int1PHmi As Integer
Dim int2PHmi As Integer
Dim int3PHmi As Integer
Dim intCoppMi As Integer
Dim strWaterXing As String
Dim intVoltage As String
Dim dtStartDate As Date
Dim intConstHrs As String
Dim intWORemove As String
Dim intLaborPct As Integer
Dim strProjCode As String
Dim strSuperProjCode As String
Dim strIntExt As String
Dim curDerBudYrDol As Currency
Dim curDerBudYrProjDol As Currency
Dim intLaborPctCont As Integer
Dim intConstPctLabor As Integer
Dim intEngPctLabor As Integer
Dim intContPctConst As Integer
Dim intIntRate As Integer
Dim intExtRate As Integer
Dim strJobType As String
Dim strProjType2 As String
Dim curTotalDerBudDol As Currency
Dim curTotalDerProjDol As Currency
Dim intEngPctContract As Integer
Dim curRedBudYrDol As Currency
Dim curTotalRedBudDol As Currency
Dim curDerRedBudYrDol As Currency
Dim curTotalDerRedBudDol As Currency
Dim int1PHmiRemove As Integer
Dim int2PHmiRemove As Integer
Dim int3PHmiRemove As Integer
Dim strComments As String
Dim intHighNeutMi As Integer
Dim strInd90day As String
Dim strSLCApply As String
Dim strReasonChange As String
Dim curDerPrelimEst As Currency
Dim curDerDesign As Currency
Dim curLTDIR As Variant
Dim curYTDIR As Variant
Dim curBudDolContEngLabor As Currency
Dim curProjDolContEngLabor As Currency
Dim dtEstCompletion As Date
Dim strConstMgr As String
Dim fso As New Scripting.FileSystemObject
Dim ts As Scripting.TextStream
Dim log As TextStream
Dim fd As FileDialog
Dim vrtSelectedItem As Variant
Dim recordcount As Integer
Dim filename As String
Dim intpos, intpos2, intpos3 As Integer
Dim strSQL As String
Dim dbsPEP As Database

On Error GoTo EH

Set dbsPEP = CurrentDb

'Setting log file location (using variable)
Set log = fspenTextFile("C:\PEPEleclog.txt", ForAppending, True) 'true value will create text file if it doesn't already exist

'Pick electric text file to import
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Application.FileDialog(msoFileDialogFilePicker).InitialFileName = ("W:\Work_Planning\PEP Update\*.txt")
With fd
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
Set ts = fspenTextFile(vrtSelectedItem)
filename = vrtSelectedItem
Next vrtSelectedItem
Else
End If
End With
ts.SkipLine 'skip first line in text file (headings)

recordcount = 0
Do Until ts.AtEndOfStream
strLine = ts.ReadLine 'read line in .txt file
intpos = InStr(strLine, Chr(9)) 'finds first tab
'inStr - finds charitures in a line
strBudYr = Left(strLine, intpos - 1) 'find text until tab & don't bring me the tab (tab delimited text file)
intpos2 = InStr(intpos + 1, strLine, Chr(9)) 'only use on middle records, don't use for last record
intpos3 = intpos2 - intpos 'only use on middle records, don't use for last record
strDist = Mid(strLine, intpos + 1, intpos3 - 1) 'start after first tab
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strRegion = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strResZn = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strSerAreaCd = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strSerArea = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strOpsZn = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strArea = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strProjType = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strPlanPriority = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
intWR = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strWRDesc = Mid(strLine, intpos + 1, intpos3 - 1)
If strWRDesc = "*'*" Then
strWRDesc = Replace(strWRDesc, "'", "")
End If
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strBusProc = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strPlanApprove = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
dtPlanAppDate = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strPlanAppBy = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strProjMgr = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strPlanner = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strProjPriority = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
dtFinishDate = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
intFinCrewHQ = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
intWOinstall = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curBudYrDol = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
intEVA = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
intBCR = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strCompany = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
dtSchedStart = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
intStatus = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strStatDescr = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
dtWRentered = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curMatl = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curEquipOH = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curTruckOH = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curMatlTrkStock = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curAddItem = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curCoLabor = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curContLabor = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
dtNewAsOf = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strOperAssign = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curLTDIRM = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curYTDIRM = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strDistEng = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
intOperUnit = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
dtWRComplete = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
intSortPriority = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strScoped = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
intFeederID = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strSubName = Mid(strLine, intpos + 1, intpos3 - 1)
If strSubName Like "*'*" Then
strSubName = Replace(strSubName, "'", "")
End If
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strBusReason = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strBusObj = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curBudYrProjDol = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curTotalBudDol = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curBudYrCrDol = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curTotalCrDol = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curTotalProjDol = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
dtScopedYr = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strBusProcDescr = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
intSortReq = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strRedlineNeed = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
intEngLaborHrs = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
intConstLaborHrs = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strPopDens = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
int1PHmi = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
int2PHmi = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
int3PHmi = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
intCoppMi = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strWaterXing = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
intVoltage = Mid(strLine, intpos + 1, intpos3 - 1)
On Error Resume Next
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
dtStartDate = Mid(strLine, intpos + 1, intpos3 - 1)
Resume
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
intConstHrs = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
intWORemove = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
intLaborPct = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strProjCode = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strSuperProjCode = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strIntExt = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curDerBudYrDol = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curDerBudYrProjDol = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
intLaborPctCont = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
intConstPctLabor = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
intEngPctLabor = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
intContPctConst = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
intIntRate = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
intExtRate = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strJobType = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strProjType2 = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curTotalDerBudDol = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curTotalDerProjDol = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
intEngPctContract = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curRedBudYrDol = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curTotalRedBudDol = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curDerRedBudYrDol = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curTotalDerRedBudDol = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
int1PHmiRemove = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
int2PHmiRemove = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
int3PHmiRemove = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strComments = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
intHighNeutMi = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strInd90day = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strSLCApply = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
strReasonChange = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curDerPrelimEst = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curDerDesign = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curLTDIR = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curYTDIR = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
curBudDolContEngLabor = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
If intpos2 = 0 Then
curProjDolContEngLabor = Mid(strLine, intpos + 1)
dtEstCompletion = #12:00:00 AM#
Else
curProjDolContEngLabor = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
intpos2 = InStr(intpos + 1, strLine, Chr(9))
intpos3 = intpos2 - intpos
dtEstCompletion = Mid(strLine, intpos + 1, intpos3 - 1)
intpos = InStr(intpos + 1, strLine, Chr(9))
strConstMgr = Mid(strLine, intpos + 1)
End If


strSQL = "INSERT INTO ecplrawdata( [Budget Year], [District], [Region], [Resource Zone], [Service Area Code], [Service Area Descr], [Operations Zone], [Area], [Project Type], [Planning Priority], [WR#], [WR Description], " & _
"[Business Process], [Planning Approval], [Date of Planning Approval], [Planning Apprvl Entrd By], [Project Manager], [Planner], [Project Priority], [Required Finish Date], [Financial Crew HQ], [Work Order Install], " & _
"[Bud Yr $], [EVA], [BCR], [Company], [Scheduled Start], [Status], [Status Description], [Date WR Entered], [Material Amt], [Equipment Overheads Amt], [Truck Stock Overheads Amt], [Material Truck Stock Amt], " & _
"[Additional Item Amt], [Company Labor], [Contract Labor], [New As Of], [Operator Assigned], [LTD], [YTD], [Distribution Engineer], [Oper Unit], [Date WR Completed], [Sort Priority], [Scoped?], [Feeder ID#], " & _
"[Substation Name], [Business Reason], [Business Objective], [Bud Yr Projected $], [Total Bud $], [Bud Yr Cr $], [Total Cr $], [Total Projected $], [Scoped Year], [Business Process Description], " & _
"[Sort Requirement], [Redline Needed?], [Engineering Labor Hours], [Construction Labor Hours], [Population Density], [1PH Miles Install], [2PH Miles Install], [3PH Miles Install], [Copperweld Miles], " & _
"[Water Crossing], [Voltage], [Actual Start Date], [Construction Hours], [Work Order Removal], [Labor Percentage], [Project Code], [Super Project Code], [Internal/External], [Derived Bud Yr $], " & _
"[Derived Bud Yr Projected $], [Labor % of Contractor Charges], [Construction % of Labor], [Engineering % of Labor], [Contractor % of Construction], [Internal Rate], [External Rate], [Job Type], " & _
"[Project Type2], [Total Derived Budget $], [Total Derived Projected $], [Engineering % of Contractor], [Redline Bud Yr $], [Total Redline Bud $], [Derived Redline Bud Yr $], [Total Derived Redline Bud $], [1PH Miles Remove], [2PH Miles Remove], " & _
"[3PH Miles Remove], [Comments], [High Neutral Miles], [Ind 90-day], [Service Level Applies], [Reason for Change], [Derived Prelim Est $], [Derived Design $], [LTD IR], [YTD IR], [Bud $ Cont Eng Labor], " & _
"[Proj $ Cont Eng Labor], [Estimated Completion Date],[Construction Manager]) Values ('" & strBudYr & "','" & strDist & "','" & strRegion & "','" & strResZn & "','" & strSerAreaCd & "','" & strSerArea & "','" & strOpsZn & "','" & strArea & "','" & strProjType & "'," & _
"'" & strPlanPriority & "','" & intWR & "','" & strWRDesc & "','" & strBusProc & "','" & strPlanApprove & "','" & dtPlanAppDate & "','" & strPlanAppBy & "','" & strProjMgr & "'," & _
"'" & strPlanner & "','" & strProjPriority & "','" & dtFinishDate & "','" & intFinCrewHQ & "','" & intWOinstall & "','" & curBudYrDol & "','" & intEVA & "','" & intBCR & "','" & strCompany & "'," & _
"'" & dtSchedStart & "','" & intStatus & "','" & strStatDescr & "','" & dtWRentered & "','" & curMatl & "','" & curEquipOH & "','" & curTruckOH & "','" & curMatlTrkStock & "','" & curAddItem & "','" & curCoLabor & "','" & curContLabor & "'," & _
"'" & dtNewAsOf & "','" & strOperAssign & "','" & curLTDIRM & "','" & curYTDIRM & "','" & strDistEng & "','" & intOperUnit & "','" & dtWRComplete & "','" & intSortPriority & "','" & strScoped & "'," & _
"'" & intFeederID & "','" & strSubName & "','" & strBusReason & "','" & strBusObj & "','" & curBudYrProjDol & "','" & curTotalBudDol & "','" & curBudYrCrDol & "','" & curTotalCrDol & "','" & curTotalProjDol & "'," & _
"'" & dtScopedYr & "','" & strBusProcDescr & "','" & intSortReq & "','" & strRedlineNeed & "','" & intEngLaborHrs & "','" & intConstLaborHrs & "','" & strPopDens & "','" & int1PHmi & "'," & _
"'" & int2PHmi & "','" & int3PHmi & "','" & intCoppMi & "','" & strWaterXing & "','" & intVoltage & "','" & dtStartDate & "','" & intConstHrs & "','" & intWORemove & "','" & intLaborPct & "'," & _
"'" & strProjCode & "','" & strSuperProjCode & "','" & strIntExt & "','" & curDerBudYrDol & "','" & curDerBudYrProjDol & "','" & intLaborPctCont & "','" & intConstPctLabor & "','" & intEngPctLabor & "'," & _
"'" & intContPctConst & "','" & intIntRate & "','" & intExtRate & "','" & strJobType & "','" & strProjType2 & "','" & curTotalDerBudDol & "','" & curTotalDerProjDol & "','" & intEngPctContract & "','" & curRedBudYrDol & "','" & curTotalRedBudDol & "', " & _
"'" & curDerRedBudYrDol & "','" & curTotalDerRedBudDol & "','" & int1PHmiRemove & "','" & int2PHmiRemove & "','" & int3PHmiRemove & "','" & strComments & "','" & intHighNeutMi & "','" & strInd90day & "'," & _
"'" & strSLCApply & "','" & strReasonChange & "','" & curDerPrelimEst & "','" & curDerDesign & "','" & curLTDIR & "','" & curYTDIR & "','" & curBudDolContEngLabor & "','" & curProjDolContEngLabor & "','" & dtEstCompletion & "','" & strConstMgr & "')"

CurrentDb.Execute strSQL

recordcount = recordcount + 1
Loop

'Writing to text file (using variable)
log.WriteLine "User successfully appended " & recordcount & " new elec records to ecplrawdata table in PEP database on " & Now
log.WriteLine "File used for import located at: " & filename

Set fso = Nothing
Set fd = Nothing
Set ts = Nothing
Exit Sub

EH:
'Send user name & date to log file
ts.WriteLine " User attempted to append on " & Now

'Send description of error to log file
ts.WriteLine vbTab & Err.Description
Msgbox Err.Description & vbCrLf & " An Error Occured: Call a staff member in Work Planning"

Msgbox "Record Append Completed", vbOKOnly, "ECPLRAWDATA Records"
End Sub

 

[KenReay]

Hi

Try (temporarily) replacing

CurrentDb.Execute strSQL

with

docmd.runsql strSQL

this will display error and warning messages and allow you to (possibly) track down which lines and why are being skipped

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[krizma5683]

Thanks for the code suggestion, I changed out that code line and came up with the error message:

"Microsoft Access can't append all the record in the append query.

Microsft Access set 1 field(s) to Null due to a type conversion failure, and it didn't add 0 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 records(s) due to validation rule violations.
Do you want to run the action query anyway?
To ignore the error(s) and run the query, click Yes.
For an explanation of the causes of the violations, click Help."

I have made sure that no fields are indexed in my table.
I can't find anything fields in the failed record that are obviously violating any rules.

Any suggestions would be helpful. Thanks!

 

[KenReay]

Hi

"1 field(s) to Null due to a type conversion failure"

I would say you have some invalid data, eg maybe a date which is not a valid date (37 Dec 04 for example)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[DrSimon]

I agree with Ken, you need to do some data validation before trying to save. I do this sort of thing regularly, and unless the data is coming from a system where all the data items are tighly typed, there's nearly always some data item that fails. The way to stop that happening is to declare all data items as Text, Not Required & Allow Zero Length. But I suspect that wouldn't be appropriate here.

Looking at that code, had you considered using Split() rather than the code you used? It would be much easier to deal with. You could have some comments identifying the elements of the arrary produced if necessary. Also to get round the data validation you could have another array identifying the type of each element, using a Case statement to assign them. Then you would have a loop which goes through the data array and the type array to do the checks. I haven't checked the entire contents of StrSQL but it looks as though you could compile that in a loop by concatenating each data array element in order. It's just a thought, but that Do loop would be much easier to manage.
Simon Rouse

 

[Frink]

Hallo,

What about your Resume statement after On Error Resume Next?
Should it be On Error GoTo EH?

- Frink

 

[krizma5683]

Thanks to all of you who helped me with this. I used the CurrentDb.Execute and found out where some of my type failures were. I also found out I had an issue with some values having an ' in the text, and these records wouldn't append to the Access table. So I wrote code to replace the ' with " " and now all my records append.

Thank You!

 

[PHV]

So I wrote code to replace the ' with " "
If ac2k or above you may consider the Replace function:
[tt]strValue = Replace(strValue, "'", "''")[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[krizma5683]

Thanks that worked great!