We help IT Professionals succeed at work.

Automation Error when Building String in Access

217 Views
Last Modified: 2012-05-05
In Microsoft Access 2003, that has been upsized to a sql server, I am trying to make a form that runs the following command: For a certain office, get a listing of the offices it serves and put them into a long string that it sticks into a database cell.

I.E. For Office:001 the Counties it serves is:  "smith, carson, jimmy, rock"

I have this bit of code to do that:

With me building the SQL Insert command dynamically. However after I add about 5 counties I get an Automation Error, it's like I'm hitting a limit to the length of the string variable.  Ideas?

    Dim temp As String
    Dim sqlInsert As String
    Dim cnn As ADODB.Connection
    Set cnn = CurrentProject.Connection
   
   
    Dim rst As ADODB.Recordset
   
    Set rst = New ADODB.Recordset
    With rst
        Set .ActiveConnection = CurrentProject.Connection
        .CursorType = adOpenKeyset
        .Open "CIL", Options:=adCmdTableDirect
       
        If .RecordCount > 0 Then
            .MoveLast
            Do Until .BOF
               
               
               Set rst2 = New ADODB.Recordset
               With rst2
                   
                    Set .ActiveConnection = CurrentProject.Connection
                    rst2.Open "SELECT CIL.ILRUCode, CILCounties.ilru, CILCounties.fips, Counties.NAME FROM CIL INNER JOIN CILCounties ON CIL.ILRUCode = CILCounties.ilru INNER JOIN Counties ON CILCounties.fips = Counties.FIPS WHERE (CIL.ILRUCode = N'" + rst.Fields("ILRUCODE") + "')"
                    sqlInsert = "Insert INTO CCTest(ILRU, Counties (VALUES ('" + rst.Fields("ILRUCODE") + "',"
                   
                   
                    If .RecordCount > 0 Then
                   
                        .MoveLast
                        sqlInsert = sqlInsert + "'" + rst2.Fields("NAME") + ", "
                       
                        Do Until .BOF
                        .MovePrevious
                        sqlInsert = sqlInsert + rst2.Fields("NAME") + ", "
                       
                        Loop
                        sqlInsert = sqlInsert + "')"
                        cnn.Execute sqlInsert
                   
                    End If
                End With
               
                .MovePrevious
            Loop
        End If
    End With

Comment
Watch Question

Database and Application Developer
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
you will need to remove the finla "," before you then append the ")"

                        Do Until .BOF
                        .MovePrevious
                        sqlInsert = sqlInsert + rst2.Fields("NAME") + ", "
                       
                        Loop
                        '  remove the final ","
                        sqlInsert = Left(sqlInsert, Len(sqlInsert) -1)

                        sqlInsert = sqlInsert + "')"
                        cnn.Execute sqlInsert

AW
Mike, that does not address the extraneous trailing "," after the counties have been concatenated.

AW
Mike EghtebasDatabase and Application Developer

Commented:
RuralInstitute,

re:> that does not address the extraneous trailing "," after the counties have been concatenated.

From Arthur_Wood.

In addition to my solution, if you had to apply the recommendation from Arthur_Wood, then the point could be split. I am looking at it now.

Did you use his good solution?

Regards,

Mike

Author

Commented:
I mainly applied eghtebas's solution to point me in the right direction. The formatting on his was also a little off, but it got me going int the proper area (i.e. a syntax problem) I already awarded points, sorry.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.