O
oucsester
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
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