Import Record count (1 Viewer)

[starter]

We have an app where users can import transactions into from excel.

When users import transactions, they get a window with the results:
"X records found
X records were imported"

The X records found is working properly. The X records were imported is always 0. Although transactions are being successfully imported.

This is the code:

Option Compare Database

Private Sub btnImport_Click()

On Error Resume Next

Dim strFilter As String
Dim strInputFileName As String
Dim bGoodToGo As Boolean
Dim ao As AccessObject
Dim iStartRecs As Integer
Dim iEndRecs As Integer
Dim iLoop As Integer
Dim iRecID As Integer

Dim MultiRS As New ADODB.Recordset

iStartRecs = DCount("*", "dbo_tCCTransactions")

DoCmd.SetWarnings False

    DoCmd.OpenQuery "qryClearLocalTransactions", acViewNormal
    
DoCmd.SetWarnings True

bGoodToGo = True

strFilter = ahtAddFilterItem(strFilter, "Excel File (*.xlsx)", "*.xlsx")
strInputFileName = ahtCommonFileOpenSave( _
                Filter:=strFilter, OpenFile:=True, _
                DialogTitle:="Please select an input file...", _
                Flags:=ahtOFN_HIDEREADONLY)
Me.txtFileName = strInputFileName

DoCmd.SetWarnings False

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tCCTransactions", strInputFileName, True, "A1:AT7500"
    DoCmd.OpenQuery "qryExpandLast4", acViewNormal
    
DoCmd.SetWarnings True
   
'clear all import errors
For Each ao In CurrentData.AllTables
    If InStr(ao.Name, "ImportErrors") > 0 Then
        DoCmd.SetWarnings False
           DoCmd.RunSQL "DROP TABLE " & ao.Name
        DoCmd.SetWarnings True
    End If
Next ao

'alphabetize all multipart transactions
If DCount("*", "qryMultiPartTransactions") > 0 Then
    
    MultiRS.Open "SELECT * FROM qryMultiPartTransactions ", CurrentProject.Connection, adOpenStatic, adLockReadOnly
    MultiRS.MoveFirst
    
    While Not MultiRS.EOF
    
        For iLoop = 1 To CInt(MultiRS.Fields(1)) Step 1
            
            If IsNull(DLookup("MAXOFAUTOID", "qryMultiPartTransactions", "[Reference Number] = '" & MultiRS.Fields(0) & "'")) Then
                DoCmd.SetWarnings False
                    DoCmd.RunSQL "Update tCCTransactions Set [Reference Number] = '" & MultiRS.Fields(0) & Chr(64 + iLoop) & "' WHERE [Reference Number] = '" & MultiRS.Fields(0) & "'", False
                DoCmd.SetWarnings True
            Else
                iRecID = DLookup("MAXOFAUTOID", "qryMultiPartTransactions", "[Reference Number] = '" & MultiRS.Fields(0) & "'")
                DoCmd.SetWarnings False
                    DoCmd.RunSQL "Update tCCTransactions Set [Reference Number] = '" & MultiRS.Fields(0) & Chr(64 + iLoop) & "' WHERE [AUTOID] = " & iRecID, False
                DoCmd.SetWarnings True
            End If
    
        Next iLoop
    
        MultiRS.MoveNext
    
    Wend

End If

'merge account numbers


'attempt to append import to MSSQL table
DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryClearOldTransactions", acViewNormal
    DoCmd.OpenQuery "qryAppendCCTransactions", acViewNormal
DoCmd.SetWarnings True

iEndRecs = DCount("*", "dbo_tCCTransactions")

MsgBox DCount("*", "tCCTransactions") & " records found " & vbCrLf & iEndRecs - iStartRecs & " records were imported"

End Sub

Private Sub Label2_DblClick(Cancel As Integer)

DoCmd.SetWarnings False

    DoCmd.OpenQuery "qryUnImport", acViewNormal

DoCmd.SetWarnings True

End Sub

I appreciate your help. I am a beginner in Access. Thank you.

 

[theDBguy]

Hi. Do you always import the same number of transactions each time? Since you're clearing the table before importing, I am wondering if the number of records you're importing is always the same.

 

[starter]

Hello. Thank you for the quick response. The number of transactions is always different.

 

[arnelgp]

remove "dbo_" from this:

iStartRecs = DCount("*", "dbo_tCCTransactions")
…
...
iEndRecs = DCount("*", "dbo_tCCTransactions")

 

[moke123]

I would also add

debug.print iEndRecs
 
debug.print  iStartRecs

to ensure you are getting the values you think you are

 

[starter]

That doesn't seem to do it.

I tested importing a file with 103 records.

'103 records found
-30 records were imported'

All 103 records were successfully imported.

 

[theDBguy]

That doesn't seem to do it.

I tested importing a file with 103 records.

'103 records found
-30 records were imported'

All 103 records were successfully imported.

Hi. Can you please post the SQL statement for this query: "qryAppendCCTransactions"? Thanks.

 

[starter]

Hi. Can you please post the SQL statement for this query: "qryAppendCCTransactions"? Thanks.

The query is:

INSERT INTO dbo_tCCTransactions ( Name, [Short Name], [Account Number], [Managing Account Number], [Managing Account Name], [Managing Account Name Line 2], [Social Security Number], [Optional 1], [Password], [Current Default Accounting Code], [Lost/Stolen Account], [Replacement Account], [Transaction Date], [Posting Date], [Cycle Close Date], [Transaction Amount], [Source Currency Amount], [Source Currency], [National Tax], [Regional Tax], [Posting Type], [Purchase ID], [Transaction Status], [Disputed Status], [Dispute Status Date], [Reference Number], [Merchant Category Code Group Code], [Merchant Category Code Group Description], [Merchant Category Code], [Merchant Category Code Description], [Merchant Name], [Merchant City], [Merchant State/Province], [Taxpayer ID Number (TIN)], [Merchant Order Number], [Memo to Account Name], [Memo to Account Number], [Posted to Account Name], [Posted to Account Number], [Client Name], [Report Date], [Report Name], [Date Type], [Start Date], [End Date], [Reviewed Status], [Allocation Detail], [Transaction Comments], [Fleet Detail], Payments, Fees, IMPORTTIME, IMPORTER, IMPORTFILE )
SELECT tCCTransactions.Name, tCCTransactions.[Short Name], tCCTransactions.[Account Number], tCCTransactions.[Managing Account Number], tCCTransactions.[Managing Account Name], tCCTransactions.[Managing Account Name Line 2], tCCTransactions.[Social Security Number], tCCTransactions.[Optional 1], tCCTransactions.Password, tCCTransactions.[Current Default Accounting Code], tCCTransactions.[Lost/Stolen Account], tCCTransactions.[Replacement Account], tCCTransactions.[Transaction Date], tCCTransactions.[Posting Date], tCCTransactions.[Cycle Close Date], tCCTransactions.[Transaction Amount], tCCTransactions.[Source Currency Amount], tCCTransactions.[Source Currency], tCCTransactions.[National Tax], tCCTransactions.[Regional Tax], tCCTransactions.[Posting Type], tCCTransactions.[Purchase ID], tCCTransactions.[Transaction Status], tCCTransactions.[Disputed Status], tCCTransactions.[Dispute Status Date], tCCTransactions.[Reference Number], tCCTransactions.[Merchant Category Code Group Code], tCCTransactions.[Merchant Category Code Group Description], tCCTransactions.[Merchant Category Code], tCCTransactions.[Merchant Category Code Description], tCCTransactions.[Merchant Name], tCCTransactions.[Merchant City], tCCTransactions.[Merchant State/Province], tCCTransactions.[Taxpayer ID Number (TIN)], tCCTransactions.[Merchant Order Number], tCCTransactions.[Memo to Account Name], tCCTransactions.[Memo to Account Number], tCCTransactions.[Posted to Account Name], tCCTransactions.[Posted to Account Number], tCCTransactions.[Client Name], tCCTransactions.[Report Date], tCCTransactions.[Report Name], tCCTransactions.[Date Type], tCCTransactions.[Start Date], tCCTransactions.[End Date], tCCTransactions.[Reviewed Status], tCCTransactions.[Allocation Detail], tCCTransactions.[Transaction Comments], tCCTransactions.[Fleet Detail], tCCTransactions.Payments, tCCTransactions.Fees, Now() AS IMPORTTIME, usernamewindows() AS IMPORTER, [forms].[frmTransactionImport].[txtFileName] AS IMPORTFILE
FROM tCCTransactions;

 

[pbaldy]

Post 8 was moderated, I'm posting to trigger email notifications. I'll also delete the duplicate moderated post.

 

[Gasman]

Does that mean there were 133 records from the previous import.?

If you are clearing out the old transactions why even do a count.?

That doesn't seem to do it.

I tested importing a file with 103 records.

'103 records found
-30 records were imported'

All 103 records were successfully imported.

 

[starter]

Does that mean there were 133 records from the previous import.?

If you are clearing out the old transactions why even do a count.?

Yes exactly. The previous import had 133 records.

I did another import to confirm. The import had 157 records.

The message was:
'157 records found
54 records were imported'

The old transactions are still there in the SQL table (dbo_tCCTransactions) and available for the users, they're not cleared. They are just cleared from the tCCTransactions table.

They are available in the SQL dbo_tCCTransactions table.

The imports stack the transactions per se. If I'm not clear please let me know.

The excel file that's being imported is credit card transactions downloaded from the bank. The excel file is imported into access, users allocate the transactions to the appropriate codes, then the transactions are exported into the accounting system. When the excel file is generated by the bank, it's possible that it contains transactions that were part of the previous import. That's the purpose of the record count and the count of how many records were successfully imported.

Also, I wasn't the one that wrote this code. I'm just trying to troubleshoot it.

Thank you.

 

[isladogs]

Post #11 was also moderated. Posting this to trigger email notifications

 

[The_Doc_Man]

iStartRecs = DCount("*", "dbo_tCCTransactions")
...
iEndRecs = DCount("*", "dbo_tCCTransactions")
...
MsgBox DCount("*", "tCCTransactions") & " records found " & vbCrLf & [COLOR="Red"]iEndRecs - iStartRecs[/COLOR] & " records were imported"

I don't know why you bother to do this. You clear the local transactions table before using it. Do a DCount of THAT table after the import and error filtering steps are complete and don't bother with the math. If that query really IS a simple append, you can go ahead and count what you are adding unless there is something else you aren't telling us.

Basically, it looks like your numbers are coming from two different places OR two different phases of an operation in a way that the two places/phases are nearly unrelated except by being in the same database. You are getting crazy results because you are not doing an "apples to apples" type of operation. Or if you prefer techie, a non-homogeneous data source set.

 

[arnelgp]

see post #4.

your code should be:

iStartRecs = DCount("*", "tCCTransactions")
…
...
iEndRecs = DCount("*", "tCCTransactions")

and not:

iStartRecs = DCount("*", "dbo_tCCTransactions")
…
...
iEndRecs = DCount("*", "dbo_tCCTransactions")

 

[starter]

iStartRecs = DCount("*", "dbo_tCCTransactions")
...
iEndRecs = DCount("*", "dbo_tCCTransactions")
...
MsgBox DCount("*", "tCCTransactions") & " records found " & vbCrLf & [COLOR="Red"]iEndRecs - iStartRecs[/COLOR] & " records were imported"

I don't know why you bother to do this. You clear the local transactions table before using it. Do a DCount of THAT table after the import and error filtering steps are complete and don't bother with the math. If that query really IS a simple append, you can go ahead and count what you are adding unless there is something else you aren't telling us.

Basically, it looks like your numbers are coming from two different places OR two different phases of an operation in a way that the two places/phases are nearly unrelated except by being in the same database. You are getting crazy results because you are not doing an "apples to apples" type of operation. Or if you prefer techie, a non-homogeneous data source set.

I'm not sure where the import and error filtering step is happening.

Importing the file appends all the records to the the local tCCTransactions table, even ones that are duplicates. I just tested by creating a file with 244 records. I added two records in there that are duplicates from a previous import for a total of 246 records. All 246 records were appended to the tCCTransactions table, but only the correct 244 were appended to dbo_tCCTransactions SQL table.

I didn't write this code, but I think the logic of the person who wrote it was is doing a count of the SQL table before and after the append, and calculating the difference to get a count of the records that were successfully imported. But that isn't working for some reason.

 

[starter]

see post #4.

your code should be:

iStartRecs = DCount("*", "tCCTransactions")
…
...
iEndRecs = DCount("*", "tCCTransactions")

and not:

iStartRecs = DCount("*", "dbo_tCCTransactions")
…
...
iEndRecs = DCount("*", "dbo_tCCTransactions")

Hello, please see post #6. It just gives the difference between the records in the previous import file and the current import file.

 

[arnelgp]

'attempt to append import to MSSQL table
[COLOR="Blue"]Dim lngRecordsAffected As Long[/COLOR]
DoCmd.SetWarnings False[COLOR="Black"][/COLOR]
    DoCmd.OpenQuery "qryClearOldTransactions", acViewNormal
    'DoCmd.OpenQuery "qryAppendCCTransactions", acViewNormal
[COLOR="Blue"]    With Currentdb.QueryDefs("qryAppendCCTransactions")
        .Execute
        lngRecordsAffected = .RecordsAffected
    End With
[/COLOR]DoCmd.SetWarnings True
MsgBox DCount("*", "tCCTransactions") & " records found " & vbCrLf & [COLOR="blue"]lngRecordsAffected[/COLOR] & " records were imported"

 

[starter]

'attempt to append import to MSSQL table
[COLOR="Blue"]Dim lngRecordsAffected As Long[/COLOR]
DoCmd.SetWarnings False[COLOR="Black"][/COLOR]
    DoCmd.OpenQuery "qryClearOldTransactions", acViewNormal
    'DoCmd.OpenQuery "qryAppendCCTransactions", acViewNormal
[COLOR="Blue"]    With Currentdb.QueryDefs("qryAppendCCTransactions")
        .Execute
        lngRecordsAffected = .RecordsAffected
    End With
[/COLOR]DoCmd.SetWarnings True
MsgBox DCount("*", "tCCTransactions") & " records found " & vbCrLf & [COLOR="blue"]lngRecordsAffected[/COLOR] & " records were imported"

Thanks. No records are appended to dbo_tCCTransactions when I try this.

 

[arnelgp]

maybe no record, because you already appended it before.
delete the record from mssql and try again.

btw, remove the "On error resume next", so you will see what error you have.

 

[arnelgp]

just tested it with my linked mssql(mssqlExpress) table and it shows the number of records updated.