runtime error: syntax error or access violation
 

hi,
I'm getting the error for the following piece of code...plz help

[VBA]

Sub itconfandscratch()

Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset



Server_Name = "sturecord"
Database_Name = "Scratch" ' Enter your database name here
SQLStr = "SELECT stuname FROM dbo.sturec" ' Enter your SQL here

Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database="
& Database_Name & ""


rs.Open SQLStr, Cn, adOpenStatic
' Dump to spreadsheet

Dim str2 As String


str2 = "{"
Dim i As Integer
i = 0
While (i < rs.RecordCount And i < 10)

str2 = str2 + "'"
str2 = str2 + rs(0)
str2 = str2 + "'"
str2 = str2 + ","
rs.Move (1)

i = i + 1

Wend

str2 = str2 + "}"

SQLStr = "SELECT [stud name],class,subject FROM dbo.stuconfig where
[stud name] in " + str2

Dim Cn1 As ADODB.Connection

Dim rs1 As ADODB.Recordset
Set rs1 = New ADODB.Recordset
Server_Name = "cbvdhg-v"
Database_Name = "exceptions"


Set Cn1 = New ADODB.Connection
Cn1.Open "Driver={SQL Server};Server=" & Server_Name & ";Database="
& Database_Name & ""

rs1.Open SQLStr, Cn1, adOpenStatic

With Worksheets("sheet4").Range("a2:z1000") ' Enter your sheet name
and range here
ClearContents
CopyFromRecordset rs

End With
' Tidy up
rs1.Close
Set rs = Nothing
Cn1.Close
Set Cn = Nothing
rs.Close
Cn.Close

End Sub

[VBA]

The above is the entire code for getting a list of students from one
database, storing it in a recordset and using this list to get the
details of all these students from another database.
Please suggest the solution for the error.

Thanks,
Oucsester:confused:


--
oucsester
------------------------------------------------------------------------
oucsester's Profile: http://www.excelforum.com/member.php...o&userid=34082
View this thread: http://www.excelforum.com/showthread...hreadid=538434