Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

Adding information to Access DB

Hi - I've writting the following code to add information into an MS Access db but I'm having problems.

Problem:    
    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
    End If
   
    sqlSurvey = "Select * "
    sqlSurvey = sqlSurvey & "From " & strTableName & " " & strQuotes

    Set rsResults = dbSurvey.OpenRecordset(sqlSurvey)
   
        With rsResults
            .AddNew
           
            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)
            Next
            .Update
            .MoveLast
        End With

    dbSurvey.Close
   
End Function
'------------------------------------------------------------------------------------------------------------------
0
eciabattari
Asked:
eciabattari
  • 3
  • 3
1 Solution
 
RogueSolutionsCommented:
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?
0
 
eciabattariAuthor Commented:
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.
0
 
RogueSolutionsCommented:
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?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
eciabattariAuthor Commented:
Figured it out....

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

If strQT(t) <> "" Then
         .Fields(strRowT) = strQT(t)
End If
                   
0
 
RogueSolutionsCommented:
OK.  You Access fields won't allow empty strings.  Surprised you didn't get an error for trying to set them as such
0
 
eciabattariAuthor Commented:
Thanks for the help
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now