Adding information to Access DB

Posted on 2004-11-08
Last Modified: 2010-05-02
Hi - I've writting the following code to add information into an MS Access db but I'm having problems.

    The intMaxRows number is based on categories and the changes with each category.  Sometimes is will only be 8 and others as many as 90.
    Therefore, I cannot put in a set number of ".Fields("GR1a") = strQA(t)", etc...
    It will add a new record but not the field results.
    The code that I've written will debug.print the correct results.  However, the values are not added to the DB.

Any suggestions?

Function Import2Access()
On Error Resume Next

    Dim rsResults As Recordset
    Dim sqlSurvey As String
    Dim strRowA As String, strRowB As String, strRowC As String, strRowT As String
    Dim strQuotes As String
    Dim v As Integer
    strQuotes = """"
    Debug.Print "TableName: " & strTableName
    If (Not OpenDataBase(strTableName)) Then
        MsgBox "Database could not be Openend !"
    End If
    sqlSurvey = "Select * "
    sqlSurvey = sqlSurvey & "From " & strTableName & " " & strQuotes

    Set rsResults = dbSurvey.OpenRecordset(sqlSurvey)
        With rsResults
            For t= 1 To intMaxRows                                   '<---------------------------------------------- HERE'S WHERE THE PROBLEM STARTS
                strRowA = strFormNameA & t & "a"    
                strRowB = strFormNameA & t & "b"    
                strRowC = strFormNameA & t & "c"          
                strRowT = strFormNameA & t& "t"                                                                            
                .Fields(strRowA) = strQA(t)
                .Fields(strRowB) = strQB(t)
                .Fields(strRowC) = strQC(t
                .Fields(strRowT) = strQT(t)
                Debug.Print v & " " & strQA(v) & " " & strQB(v) & " " & strQC(v) & " " & strQT(v)
        End With

End Function
Question by:eciabattari
    LVL 5

    Expert Comment

    First thing I can see is that your .AddNew and .Update are outside the loop so the fields just keep getting updated with the db being told to store them.

    May be more to it than that though?

    Author Comment

    I placed the .AddNew and .Update outside the loop due the fact that 1 record may have 8 fields, 11 fields or 60 fields worth of data.  It really depends on the category

    DB Configuration
    Table "tblGeneral"
    field 1: ID, autonumber, primary key
    field 2: GR1a
    field 3: GR2a
    field 18: GR16a

    Table tblContracting
    field 1: ID, autonumber, primary key
    field 2: CT1a
    field 3: CT2a
    field 25: GR24a

    I only wanted to add 1 record for each category & survey response.
    LVL 5

    Expert Comment

    Right OK, think I see what you have better now.

    One further thing, your debug line uses v when the loop was t ?  Maybe that's just a typo though?

    Author Comment

    Figured it out....

    Changed  .Fields(strRowT) = strQT(t) to

    If strQT(t) <> "" Then
             .Fields(strRowT) = strQT(t)
    End If
    LVL 5

    Accepted Solution

    OK.  You Access fields won't allow empty strings.  Surprised you didn't get an error for trying to set them as such

    Author Comment

    Thanks for the help

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now