Automation Error when Building String in Access
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Mike, that does not address the extraneous trailing "," after the counties have been concatenated.
AW
AW
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
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
ASKER
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.
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