VBA - נסיון להתחבר לשרת SQLSERVER
היי,
כתבתי את הקוד הבא בנסיון להתחבר לשרת הSQLSERVER ולהציג את הרשומות בגליון האקסל. אבל אני מקבל הודעת שגיאה (אצרף תמונה)
Sub DataFromSqlServer()
Dim oConn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim mssql As String
Dim row As Integer
Dim Col As Integer
Dim ws As ThisWorkbook
Set ws = ThisWorkbook
Application.ScreenUpdating = False
Set oConn = New ADODB.Connection
Set rs = New ADODB.Recordset
mssql = "select b.Company," & _
"b.TamplateName," & _
"b.[TamplateNo]," & _
"count(b.TamplateNo) As CounterUse" & _
"from TemplateJobInfo as a" & _
"inner Join" & _
"TemplateJobInfoNames as b" & _
"on" & _
"a.[TemplateFileName] = b.[TamplateNo]" & _
"where a.[CreateTime] > '2017-01-01'" & _
"group by b.Company, b.TamplateName, b.[TamplateNo]" & _
"order by CounterUse desc"
oConn.ConnectionString = "driver={SQL Server};" & _
"server=PRINTBOS\SQLEXPRESS;uid=***!;pwd=***;authenticateduser = TRUE;database=PrintBos"
oConn.ConnectionTimeout = 30
oConn.Open
rs.Open mssql, oConn
If rs.EOF Then
MsgBox "No matching records found."
rs.Close
oConn.Close
Exit Sub
End If
row = 2
Col = 1
For Each fld In rs.Fields
Sheet2.Cells(row, Col).Value = fld.Name
Col = Col + 1
Next
rs.MoveFirst
row = row + 1
Do While Not rs.EOF
Col = 1
For Each fld In rs.Fields
Sheet2.Cells(row, Col).Value = fld
Col = Col + 1
Next
row = row + 1
rs.MoveNext
Loop
rs.Close
oConn.Close
End Sub
היי,
כתבתי את הקוד הבא בנסיון להתחבר לשרת הSQLSERVER ולהציג את הרשומות בגליון האקסל. אבל אני מקבל הודעת שגיאה (אצרף תמונה)
Sub DataFromSqlServer()
Dim oConn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim mssql As String
Dim row As Integer
Dim Col As Integer
Dim ws As ThisWorkbook
Set ws = ThisWorkbook
Application.ScreenUpdating = False
Set oConn = New ADODB.Connection
Set rs = New ADODB.Recordset
mssql = "select b.Company," & _
"b.TamplateName," & _
"b.[TamplateNo]," & _
"count(b.TamplateNo) As CounterUse" & _
"from TemplateJobInfo as a" & _
"inner Join" & _
"TemplateJobInfoNames as b" & _
"on" & _
"a.[TemplateFileName] = b.[TamplateNo]" & _
"where a.[CreateTime] > '2017-01-01'" & _
"group by b.Company, b.TamplateName, b.[TamplateNo]" & _
"order by CounterUse desc"
oConn.ConnectionString = "driver={SQL Server};" & _
"server=PRINTBOS\SQLEXPRESS;uid=***!;pwd=***;authenticateduser = TRUE;database=PrintBos"
oConn.ConnectionTimeout = 30
oConn.Open
rs.Open mssql, oConn
If rs.EOF Then
MsgBox "No matching records found."
rs.Close
oConn.Close
Exit Sub
End If
row = 2
Col = 1
For Each fld In rs.Fields
Sheet2.Cells(row, Col).Value = fld.Name
Col = Col + 1
Next
rs.MoveFirst
row = row + 1
Do While Not rs.EOF
Col = 1
For Each fld In rs.Fields
Sheet2.Cells(row, Col).Value = fld
Col = Col + 1
Next
row = row + 1
rs.MoveNext
Loop
rs.Close
oConn.Close
End Sub