INSERT INTO query not doing its job 1

[davyre]

Hi,
I have an append query to insert some data into a table from ms excel. Heres the code:

Dim partNum As String
Dim desc As String
Dim Material As String
Dim cost As Long
Dim vendorPartNum As String
Dim VendorID As Long
Dim ctr As Long

Dim xlLine As Integer
Dim allBlank As Integer

Dim insQry As String

Dim strFilter As String
Dim StrInputFileName As String
Dim xlApp As Excel.Application
Dim wkb As Excel.workbook
Dim wks As Excel.Worksheet

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xlsx)", "*.xlsx")
strFilter = ahtAddFilterItem(strFilter, "Excel 97-2003 Files (*.xls)", "*.xls")
strFilter = ahtAddFilterItem(strFilter, "all Files (*.*)", "*.*")

StrInputFileName = ahtCommonFileOpenSave( _
                            filter:=strFilter, _
                            OpenFile:=True, _
                            DialogTitle:="Choose an image file...", _
                            Flags:=ahtOFN_HIDEREADONLY)

If Len(StrInputFileName) > 0 Then
    ' Do nothing as a file was chosen MsgBox strInputFileName, vbOKOnly
Else
    'No file chosen, or user canceled
    Exit Sub
End If
    
Set xlApp = CreateObject("Excel.application")
Set wkb = GetObject(StrInputFileName)
Set wks = wkb.Worksheets("Sheet1")


allBlank = 0
xlLine = 2
ctr = 0

Do While allBlank <= 3
    partNum = wks.Cells(xlLine, "A")
    If partNum = "" Or IsNull(partNum) Then
        allBlank = allBlank + 1
        xlLine = xlLine + 1
    Else
        allBlank = 0
        desc = Replace(wks.Cells(xlLine, "F"), "'", "''")
        Material = wks.Cells(xlLine, "I")
        cost = wks.Cells(xlLine, "K")
        vendorPartNum = wks.Cells(xlLine, "D")
        VendorID = 43
        
        insQry = "INSERT INTO TblParts([PartNumber],[PartDescription],[Material],[PartCost],[VendorPart],[VendorID]) " _
                & "VALUES('" & partNum & "','" & desc & "','" & Material & "'," & cost & ",'" & vendorPartNum & "'," & VendorID & ")"
        
        DoCmd.SetWarnings False
        DoCmd.RunSQL insQry
        DoCmd.SetWarnings True
        
        Debug.Print insQry
        
        ctr = ctr + 1
        xlLine = xlLine + 1
    End If
    
Loop

MsgBox (ctr)

The issue is that it wont append the whole data. THe total data it needs to append is 174, but instead it only does 25. And there are some autonumber that jumps (i.e after 1011 then 1040)
I have checked using debug.print and it seems perfectly fine (I can see 174 records in Immediate Window), so my suspect is the docmd.runsql insQry...but what happen?anyone can help?
Thanks

 

[dhookom]

Did you attempt to run the insQry strings that didn't seem to insert records? Does tblParts have any constraints?

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

yes, I run the insQry but it seems did not insert all records..
what do you mean by constraints? It has relationship with tblPartStock and tblPartOrder but that's it..
I have checked the datatypes for each field and it should be okay. Otherwise there will be no records being inserted.

 

[davyre]

update: I tried to delete all the tblparts, tblpartstock, and tblpartorder. Now it seems working (it got 174 records on tblparts). Probably there are something that prevents access to insert the record freely.

 

[davyre]

update 2 :

Okay, now I understand why. The 149 of 174 records that are not appended to the table are duplicates to the existing records. But the duplicates are only on PartNumber field (not the PK). The reason I entering these duplicates because I have different price and vendorID (thus these are not much duplicates except the PartNumber).
So for example, the existing records are like this:

PartID  | PartNumber | ... | PartCost | VendorID |
1       | [highlight #FCE94F]A00001[/highlight]     |     | 5.99     | 15       |
2       | [highlight #8AE234]A00010[/highlight]     |     | 3.99     | 15       |

and the ones that I want to insert are

PartNumber | ... | PartCost | VendorID |
[highlight #FCE94F]A00001[/highlight]     | ... | 4.99     | 22       |
[highlight #8AE234]A00010[/highlight]     | ... | 3.49     | 22       |

so how can I insert data like those?

 

[PHV]

Be sure that no unique key is created on PartNumber.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Davyre "what do you mean by constraints?"
Check constraints to see "In a UNIQUE constraint, no two rows in the table can have the same value for the columns."

Duane
Hook'D on Access
MS Access MVP