Loading Excel data into Access Hangs

[alexisb]

I am loading data using VBA code from an Excel sheet with 11977 rows. The file has 5 columns but I am loading the first 4 columns only. The load works fine until it hangs on row 8190. I have to kill the Excel session at that point. It seems as though I am hitting an Access limitation. Since 4 * 8190 = 32760 and 32768 is the number of objects limitation - could that be the problem? Does anyone have any suggestions as to how to load this file (note the file will grow as time goes on).

Thank you!
Alexis

 

[SgtJarrow]

Please post your code so we can take a look.....I have imports running 15 columns by 50,000+ records and it works fine. I imported once manually and created a spec for the import, then use vba to import the file (with the spec sheet usingt he DoCmd.TransferSpreadsheet) and everything works..... "Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." Albert Einstein.

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com

 

[alexisb]

Thank you. Here's my code:
Function ProcessFiles2()
Dim strSSN As String, strDeductionCode As String, strName As String
Dim strCurrName As String, strPrevName As String
Dim strPrevSSN As String, strCurrSSN As String, sqlApost As String
Dim iLineNo As Integer, iRecsProcessed As Integer
Dim dblDeductionAmt As Double
Dim reportDate As Date
Dim rst As Recordset, rstName As Recordset, rstApost As Recordset
Dim dbs As Database
Set dbs = CurrentDb
'delete all current recs in tbl_emp_deductions since we are reloading latest data
sqlDelete = "delete * from tbl_emp_deductions"
DoCmd.RunSQL sqlDelete
'get report date from row 6 col 2
report_date = Nz(Trim(xlSheet.Cells(6, 2)))
today = Date
USER_ID = "alexis"
' setup default line number to start on
iLineNo = 8
iRecsProcessed = 0
strPrevSSN = ""
strPrevName = ""
Do While True
strCurrSSN = Trim(xlSheet.Cells(iLineNo, 1))
strCurrSSN = Left(strCurrSSN, 3) & Mid(strCurrSSN, 5, 2) & Right(strCurrSSN, 4)
strCurrName = FindChar(Trim(xlSheet.Cells(iLineNo, 2)), "'", "%&quot
'This logic is used to handle that the SSN is only displayed on the first line for
'each employee's data.
If strCurrSSN = "" Then
strCurrSSN = strPrevSSN
strCurrName = strPrevName
End If
strDeductionCode = Nz(Trim(xlSheet.Cells(iLineNo, 3)))
If strDeductionCode = "" Then 'at end of file so leave loop
Exit Do
End If
sql = "select * from tbl_deduction_codes where " & _
"deduction_code = '" & strDeductionCode & "'"
Set rst = dbs.OpenRecordset(sql, dbOpenDynaset, dbReadOnly)
'Only insert records for deductions we want to capture (entered in tbl_deduction_codes)
If Not rst.EOF Then
dblDeductionAmt = Nz(xlSheet.Cells(iLineNo, 4), 0)
sqlInsert = "insert into tbl_emp_deductions(ssn,employee_name,deduction_code," & _
"deduction_amt," & _
"report_date,update_date,update_by) VALUES ('" & strCurrSSN & "','" & _
strCurrName & "','" & _
strDeductionCode & "'," & dblDeductionAmt & ",'" & report_date & "','" & _
today & "','" & USER_ID & "')"
DoCmd.RunSQL sqlInsert
rst.Close
End If
iLineNo = iLineNo + 1
strPrevSSN = strCurrSSN 'reset ssn for next record
strPrevName = strCurrName
Loop
'Now go back and put back the apost in the names
sqlApost = "select employee_name from tbl_emp_deductions"
Set rstApost = dbs.OpenRecordset(sqlApost, dbOpenDynaset)
Do While Not rstApost.EOF
rstApost.Edit
rstApost![employee_name] = FindChar(rstApost![employee_name], "%", "'&quot
rstApost.Update
rstApost.MoveNext
Loop
rstApost.Close
dbs.Close
' MsgBox "Lines in xls: " & iLineNo
End Function