We help IT Professionals succeed at work.

Counting records using VB6 in Access

I am trying to write a program in VB6 with some issues.  The part of the code attached symbolizes a check run on an Excel spreadsheet.  If it has "Store" in the first column, first Cell, it warns that the file has already been parsed, and would then proceed to the Else statement, where it parses the file (the parsing code is not included as it works fine).  My issue is if it finds it parsed, the message box appears warning the user as such.  Answering "Yes" to the message box would begin the import process (starting with the If IntResponse = 6 Then).  What I'm trying to get it to do it look at my database and run a query on a table to return a record count.  If that record count is 0, I want it to run the import.  If it finds any records, I want it to warn me it's already been imported.  That's the part I can't working.  I can get the import statement fine, but my check doesn't work.  I'm very new to VB6 so I'm sure I'm doing something stupid.  The code posted here will do everything EXCEPT query the database properly for the number of records.  I would appreciate any and all insight into this.
If xlSheet.Range("A1").Value = "Store" Then
            Dim intResponse As Integer
            intResponse = MsgBox("WARNING!!!  Import File has already been parsed. Click Yes to proceed with the import.", vbYesNo, "Warning")

            If intResponse = 6 Then

                ' BEGIN IMPORT LOGIC HERE!!!!
                Dim con As New ADODB.Connection
                Dim adRs As New ADODB.Recordset
                Dim TEST As Integer
                Dim strSQL As String
                Dim strSQL1 As String

                strSQL = "INSERT INTO IMPORT SELECT * FROM [Sheet1$] IN ""R:\FTX Import\import.xls"" ""Excel 8.0; HDR=YES;"""
                strSQL1 = "SELECT COUNT(*) As TEST FROM IMPORT;"

                con.Open( _
                   "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\westy\Documents\Visual Studio 2010\Projects\WindowsApplication1\WindowsApplication1\FTXTranslator.mdb;Jet OLEDB:Engine Type=4")
                adRs = con.Execute(strSQL1)

                ' Checks to see if records already exist in the destination table
                If TEST = 0 Then

                    con.Execute(strSQL)

                    con.Close()
                    con = Nothing

                    MsgBox("Complete", vbInformation, "Done!")
                Else : MsgBox("Already imported!", vbInformation, "Done")
                    con.Close()
                    con = Nothing
                End If

            Else
                Application.Exit()

            End If

            xlBook.Close()
            xlObj.Quit()

        Else.....

Open in new window

Comment
Watch Question

Most Valuable Expert 2012
Top Expert 2014

Commented:
Try

 adRs = con.OpenRecordset(strSQL1)

If adRs.RecordCount  > 0 Then
   'already exists
Else
  'insert it
End if

Author

Commented:
CodeCruiser...  Glad to see you again!!!  Regretfully this did not work.  I'm getting an error:

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

This is the code I used, per your fix:

If intResponse = 6 Then

                ' BEGIN IMPORT LOGIC HERE!!!!
                Dim con As New ADODB.Connection
                Dim adRs As New ADODB.Recordset
                Dim TEST As Integer
                Dim strSQL As String
                Dim strSQL1 As String

                strSQL = "INSERT INTO IMPORT SELECT * FROM [Sheet1$] IN ""R:\FTX Import\import.xls"" ""Excel 8.0; HDR=YES;"""
                strSQL1 = "SELECT COUNT(*) As TEST FROM IMPORT;"

                con.Open( _
                   "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\westy\Documents\Visual Studio 2010\Projects\WindowsApplication1\WindowsApplication1\FTXTranslator.mdb;Jet OLEDB:Engine Type=4")
                adRs = con.OpenRecordset(strSQL1)

                ' Checks to see if records already exist in the destination table
                If adRs.RecordCount > 0 Then

                    MsgBox("Already imported!", vbInformation, "Done")

                Else
                    con.Execute(strSQL)

                    con.Close()
                    con = Nothing

                    MsgBox("Complete", vbInformation, "Done!")

                End If

Most Valuable Expert 2012
Top Expert 2013

Commented:
<<                If TEST = 0 Then   >>

Assuming that TEST is referring to the field TEST  in your query (strSQL1), you need to use the recordset prefix.


If xlSheet.Range("A1").Value = "Store" Then
            Dim intResponse As Integer
            intResponse = MsgBox("WARNING!!!  Import File has already been parsed. Click Yes to proceed with the import.", vbYesNo, "Warning")

            If intResponse = 6 Then

                ' BEGIN IMPORT LOGIC HERE!!!!
                Dim con As New ADODB.Connection
                Dim adRs As New ADODB.Recordset
                Dim TEST As Integer
                Dim strSQL As String
                Dim strSQL1 As String

                strSQL = "INSERT INTO IMPORT SELECT * FROM [Sheet1$] IN ""R:\FTX Import\import.xls"" ""Excel 8.0; HDR=YES;"""
                strSQL1 = "SELECT COUNT(*) As TEST FROM IMPORT;"

                con.Open( _
                   "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\westy\Documents\Visual Studio 2010\Projects\WindowsApplication1\WindowsApplication1\FTXTranslator.mdb;Jet OLEDB:Engine Type=4")
                adRs = con.Execute(strSQL1)

                ' Checks to see if records already exist in the destination table
                If adRs!TEST = 0 Then  '<----*** You need to prefix your field with the recordset name

                    con.Execute(strSQL)

                    con.Close()
                    con = Nothing

                    MsgBox("Complete", vbInformation, "Done!")
                Else : MsgBox("Already imported!", vbInformation, "Done")
                    con.Close()
                    con = Nothing
                End If

            Else
                Application.Exit()

            End If

            xlBook.Close()
            xlObj.Quit()

        Else..... 

Open in new window

Most Valuable Expert 2012
Top Expert 2014

Commented:
Error could be due to choice of names so try

strSQL1 = "SELECT COUNT(*) As [TEST] FROM [IMPORT];"

Author

Commented:
mbizup...  thanks for the efforts, however that did not work either.  I received the following error:

Overload resolution failed because no accessible 'Fields' accepts this number of arguments.

Here is the code used:

' BEGIN IMPORT LOGIC HERE!!!!
                Dim con As New ADODB.Connection
                Dim adRs As New ADODB.Recordset
                Dim TEST As Integer
                Dim strSQL As String
                Dim strSQL1 As String

                strSQL = "INSERT INTO IMPORT SELECT * FROM [Sheet1$] IN ""R:\FTX Import\import.xls"" ""Excel 8.0; HDR=YES;"""
                strSQL1 = "SELECT COUNT(*) As TEST FROM IMPORT;"

                con.Open( _
                   "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\westy\Documents\Visual Studio 2010\Projects\WindowsApplication1\WindowsApplication1\FTXTranslator.mdb;Jet OLEDB:Engine Type=4")
                adRs = con.OpenRecordset(strSQL1)

                ' Checks to see if records already exist in the destination table
                If adRs!TEST = 0 Then

                    con.Execute(strSQL)

                    con.Close()
                    con = Nothing

                    MsgBox("Complete", vbInformation, "Done!")

                Else : MsgBox("Already imported!", vbInformation, "Done")
                    con.Close()
                    con = Nothing

                End If

Author

Commented:
CodeCruiser, I am receiving the same error...
Most Valuable Expert 2012
Top Expert 2014
Commented:

Author

Commented:
CodeCruiser...  I can only say you are AWESOME!!!  It seems this all had to do with my CursorLocation properties.  Basically I used this code:

If intResponse = 6 Then

                ' BEGIN IMPORT LOGIC HERE!!!!
                Dim con As New ADODB.Connection
                Dim adRs As New ADODB.Recordset
                Dim strSQL As String

                strSQL = "INSERT INTO IMPORT SELECT * FROM [Sheet1$] IN ""R:\FTX Import\import.xls"" ""Excel 8.0; HDR=YES;"""

                con.Open( _
                   "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\westy\Documents\Visual Studio 2010\Projects\WindowsApplication1\WindowsApplication1\FTXTranslator.mdb;Jet OLEDB:Engine Type=4")
                adRs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
                adRs.CursorType = ADODB.CursorTypeEnum.adOpenStatic
                adRs.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
                adRs.Open("select * from IMPORT", con)

                ' Checks to see if records already exist in the destination table
                If adRs.RecordCount > 0 Then

                    MsgBox("Already imported!", vbInformation, "Done")
                    con.Close()
                    con = Nothing

                Else
                    con.Execute(strSQL)

                    con.Close()
                    con = Nothing

                    MsgBox("Complete", vbInformation, "Done!")

                End If

            Else
                Application.Exit()

            End If

            xlBook.Close()
            xlObj.Quit()

        Else....

And it imports, if there are no records...  and warns me if there are!  It appears that was it!!!  Thanks again!!!!
Most Valuable Expert 2012
Top Expert 2014

Commented:
Glad to help :-)
Most Valuable Expert 2012
Top Expert 2013

Commented:
Try using rs.open instead of Con.Execute for your SELECT query:


If xlSheet.Range("A1").Value = "Store" Then
            Dim intResponse As Integer
            intResponse = MsgBox("WARNING!!!  Import File has already been parsed. Click Yes to proceed with the import.", vbYesNo, "Warning")

            If intResponse = 6 Then

                ' BEGIN IMPORT LOGIC HERE!!!!
                Dim con As New ADODB.Connection
                Dim adRs As New ADODB.Recordset
                Dim TEST As Integer
                Dim strSQL As String
                Dim strSQL1 As String

                strSQL = "INSERT INTO IMPORT SELECT * FROM [Sheet1$] IN ""R:\FTX Import\import.xls"" ""Excel 8.0; HDR=YES;"""
                strSQL1 = "SELECT COUNT(*) As TEST FROM IMPORT;"

                con.Open( _
                   "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\westy\Documents\Visual Studio 2010\Projects\WindowsApplication1\WindowsApplication1\FTXTranslator.mdb;Jet OLEDB:Engine Type=4")
                         adRS.Open strSQL1, con, adOpenKeySet, adLockOptimistic

                ' Checks to see if records already exist in the destination table
                If adRs("TEST") = 0 Then  '<----*** You need to prefix your field with the recordset name

                    con.Execute(strSQL)

                    con.Close()
                    con = Nothing

                    MsgBox("Complete", vbInformation, "Done!")
                Else : MsgBox("Already imported!", vbInformation, "Done")
                    con.Close()
                    con = Nothing
                End If

            Else
                Application.Exit()

            End If

            xlBook.Close()
            xlObj.Quit()

        Else.....  

Open in new window