Results 1 to 10 of 10
  1. #1
    Join Date
    May 2006
    Posts
    17

    Unanswered: ADO Select * not showing all records

    I have written an excel application that loops through an access 2003 database of contacts.
    Code:
    DoEvents
        
        Dim conn As ADODB.Connection
        Dim rs As ADODB.Recordset
         
        'Open the ADO Connection
        Set conn = New ADODB.Connection
             
        strDatabase = wsSetup.Cells(1, 2)
        
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= '" & strDatabase & "'"
        conn.Open
           
        
        ' Select the data.
        Set rs = conn.Execute( _
            "SELECT * FROM tblCustomer", , _
            adCmdText)
    
    Row = 1
        Do While Not rs.EOF
            For col = 0 To rs.Fields.Count - 1
                wsData.Cells(Row, col + 1) = _
                    rs.Fields(col).Value
            Next col
    
            Row = Row + 1
            rs.MoveNext
        Loop
    
    
        ' Close the database.
        rs.Close
        Set rs = Nothing
        conn.Close
        Set conn = Nothing
    This brings in all the contacts into a hidden temporary sheet. Then Vba code selects distinct companies and displays a list of contacts for each company. If the contact isn't in the list a button can be pressed to allow the addition of a new contact. Using this code:
    Code:
    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
    
    strDatabase = wsSetup.Cells(1, 2)
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & strDatabase & "'"
    conn.Open
    
    Dim strContactName, strCompany, strPhone, strFax, strEmail, strAddress1, _
    strCity, strState, strZIP As String
    
    strContactName = frmNewContact.txtContactName
    strCompany = frmNewContact.txtCompany
    strPhone = frmNewContact.txtPhone
    strFax = frmNewContact.txtFax
    strEmail = frmNewContact.txtEmail
    strAddress1 = frmNewContact.txtAddress1
    strCity = frmNewContact.txtCity
    strState = UCase(frmNewContact.txtState)
    strZIP = frmNewContact.txtZIP
               
    Dim strSQL As String
    strSQL = "INSERT INTO tblCustomer(ContactName, Company, WorkNO, FaxNO, Email, Address, City, State, Zip)VALUES('" & strContactName & "', '" & strCompany & "', '" & strPhone & "', '" & strFax & "', '" & strEmail & "', '" & strAddress1 & "', '" & strCity & "', '" & strState & "', '" & strZIP & "')"
    
    conn.Execute strSQL
                     
    conn.Close
    Set conn = Nothing
    Randomly the database will stop selecting all records from the database. Even though the records do exist and can be updated via Access. I have tried repairing the database with Access with no luck. I have also tried selecting the data with a another stripped down version of my application from the same database and that works as far as I can see. So, is there something that is not clearing or.....

    Any ideas?

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    What do you mean it will "stop selecting all records"?
    If you have 50 total, is it pulling 0 or 35?

    If 0, I'd suspect your connection is timing out.

    If 35, I'd be very scared.
    Inspiration Through Fermentation

  3. #3
    Join Date
    May 2006
    Posts
    17

    I mean that it is pulling like 35...terrified I am.

    It seems that it will not pull new records that are added. It will work fine for a while and then one day it just stops working right. The only way that I can fix it is to revert back to an archived version of a working database file. Also sometimes it actually switches and will only list new records that were added and pretends that the original records are not there.

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    hmmmm....
    These "new" records. Are they a few minutes old, or could they be a few days old?

    Are you sure there aren't multiple copies of your database floating around?
    Inspiration Through Fermentation

  5. #5
    Join Date
    May 2006
    Posts
    17
    They could be a few minutes, few days or a few weeks old It doesn't matter as soon as "something" happens....frustration. Also yes I am positive there is only one database being used.

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    So, if someone changes the database info stored here:
    wsSetup.Cells(1, 2)

    It would just error out, instead of looking at a different location, right?
    What specific values are stored in those 2 cells?
    Inspiration Through Fermentation

  7. #7
    Join Date
    May 2006
    Posts
    17
    It would just error out. This is the directory in which the database can be found. It is changeable so that our outside salesman can use the spreadsheet application on their laptops. Otherwise it is linked to a mapped out drive on our server.

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Sorry rizzo, I'm out of ideas.

    From experience, I know that:
    Salemen + Computers=Bad

    When they change it from the field, which DB are they using? That gives me the impression there's more than one DB,
    and they aren't synched.
    Inspiration Through Fermentation

  9. #9
    Join Date
    May 2006
    Posts
    17
    Thanks for the help anyway. All of the contacts are being added correctly to the database. When the app runs with the correct database being accessed the records all don't come into the sheet. Maybe somebody else has a solution????
    Last edited by rizzo89; 01-22-07 at 18:26.

  10. #10
    Join Date
    May 2006
    Posts
    17

    Update

    Figured it out....just a bad cell reference in my VBA code. Thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •