Dim adoConn As ADODB.Connection
Dim oRs As ADODB.Recordset
Sub MS_SQL_static()
Dim sql As String
Dim oRs As New ADODB.Recordset
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim sum As Double
Dim value1 As Integer
Dim value2 As Integer
Dim value3 As Double
Dim value4 As Double
value1 = Range("i")
value2 = Range("j")
value3 = (value1 / 1 * 100) + value2
value4 = Int(value3 / 1 * 100) '8ÀÚ¸®
'MsgBox value
'MsgBox SQL
'±Ý¿ù°í°´¼ö
* sql = "select count(distinct code) from test where years= " & value1 & " and *months= " & value2 & ""
*
*** If DB_Connection = True Then
*** oRs.Open sql, adoConn, adOpenDynamic, adLockReadOnly
*
* If oRs.RecordCount > 0 Then
* Cells(8, 4) = oRs(0)
*
* Else
* MsgBox "DB¿¡ µ¥ÀÌÅÍ°¡ Á¸ÀçÇÏÁö ¾Ê½À´Ï´Ù": Exit Sub
* End If
*
* oRs.Close
*
* Else
* MsgBox "DBÀÇ Á¢¼ÓÀÌ ¿øÈ°ÇÏÁö ¾Ê½À´Ï´Ù": Exit Sub
* End If
End Sub
Public Function DB_Connection() As Boolean
On Error GoTo dbErr
Set adoConn = New ADODB.Connection
'MS-SQL Á¢¼ÓÀ϶§
With adoConn
.CursorLocation = adUseClient
.ConnectionString = "Provider=SQLOLEDB.1;Password=xxxx;Persist Security Info=True;User ID=xxxx;Initial Catalog=rent;Data Source=xxx.xxx.xxx.xxx"
.CommandTimeout = 5
.Open
End With
DB_Connection = True
Exit Function
dbErr:
DB_Connection = False
End Function
¾î¼´Ù ÀÌ ÀÏÀ» ½ÃÀÛÇÏ°Ô µÇ¾ú´ÂÁö ¸ð¸£°ÚÁö¸¸.. ÀÌ ÇÁ·Î±×·¥ÀÇ ¸ñÀûÀº ¿¢¼¿¸ÅÅ©·Î ÆÄÀÏ·Î ¾îµð¼µç ÇöÀç °í°´ÀÇ Á¤Å ¹× µ¿Å¸¦ »ìÆ캼 ¼ö ÀÖ´Â º¸°í¼ ÀÛ¼º¿¡ ÀÖ½À´Ï´Ù.
À§ÀÇ ¼Ò½º´Â ³âµµ¿Í ´ÞÀ» ¹Þ¾Æ¿Í¼ ±×¿¡ ÇØ´çÇÏ´Â Åë°è°ªµéÀ» ¿¢¼¿¿¡ »Ñ·ÁÁÖ´Â ÇüÅÂÀÔ´Ï´Ù.
º¸Åë ÁúÀǹ® Çϳª¿¡ ÇϳªÀÇ ¼¿¿¡ Ç¥ÇöµÇ´Â ¹æ½ÄÀÔ´Ï´Ù. À̺¥Æ® Çѹø¿¡ 70°³ Á¤µµÀÇ Äõ¸®°¡ ´øÁ®Áö´Â »óȲÀÔ´Ï´Ù. ±×·¯´Ï±î *Ç¥½Ã°¡ µÇ¾î ÀÖ´Â ºÎºÐÀÌ 70°³ Á¤µµ µÈ´Ù°í »ý°¢ÇÏ½Ã¸é µÉ°Í°°½À´Ï´Ù.
ÃÊÂ¥ÀÎ Á¦°¡ ºÁµµ ÇѼ¿À» Ç¥½ÃÇÒ¶§¸¶´Ù ***°¡ ºÙÀº ºÎºÐÀ» °è¼Ó ¼öÇàÇÏ´Ï ºñÈ¿À²ÀûÀÌ°í ¼Óµµµµ »ó´çÈ÷ ´Ê¾îÁö´Â °Í °°½À´Ï´Ù.
Áö±Ý ¿¢¼¿¿¡ »Ñ·ÁÁö´Â ¹æ½ÄÀÌ sql Äõ¸®¹®À» ÀúÀåÇؼ °¡Áö°í ÀÖ´Ù°¡ ±×¿¡ ÇØ´çÇÏ´Â °ªÀÌ ÀÖ´Ù¸é(oRs(0)) ¼öÇàÇÏ´Â ¹æ½ÄÀ¸·Î ¾Ë°í Àִµ¥ ÀÌ ºÎºÐ¶§¹®¿¡ ¿©·¯°³ÀÇ Äõ¸®¹®À» Çѹø¿¡ ó¸®ÇÏÁö¸¦ ¸øÇÏ°Ú½À´Ï´Ù.
±×¸®°í ÇöÀç ¿¢¼¿¿¡¼ 'ÀÚ·á »ý¼ºÇϱâ' ¹öÆ°À» »ý¼ºÇÏ¿© À̺¥Æ®¸¦ ¹ß»ý½ÃÅ°¸é 4°³ÀÇ Å×ÀÌºí¿¡¼ ÇÊ¿äÇÑ °ªµé°ú ÆÄ»ýº¯¼ö¸¸ ¸¸µé¾î Àӽà Å×À̺í·Î ¸¸µé¾î ³õ°í °Å±â¼ ÀÚ·áµéÀ» ºÒ·¯¿À´Â ¹æ½ÄÀ» »ý°¢Çϴµ¥ ¸Å Äõ¸®¹®¸¶´Ù inner join À» ½ÃÅ°´Â °Íº¸´Ù ºü¸¦·±Áö¿ä? ÇöÀç ÀÌ¿ëÇÏ´Â ÀÚ·á°¡ ¹é¸¸°ÇÀÌ ³Ñ´Â ÀÚ·á¶ó ÀÌ ºÎºÐ¿¡ ´ëÇØ °í¹ÎÀÌ Á» µÇ³×¿ä.
¾Æ ±×¸®°í ºñº£¿¡¼ ÁÖ¼®À» ÇÑÁÙ¾¿¸¸ ´Þ¼ö ÀÖ³ª¿ä? Çѹø¿¡ ¿©·¯ ÁÙÀ» ÁÖ¼®À¸·Î ó¸®ÇÏ´Â ¹æ¹ý°ú ¹®ÀÚ¿ sql=" ³»¿ë" À» ÁÙ¶§ ²À '³»¿ë'ÀÌ ³Ê¹« ±æ¾î¼ ´ÙÀ½ ÁٷΠǥ±âÇÏ°í ½ÍÀºµ¥ ±× ¹æ¹ýµµ ¾Ë·ÁÁÖ½Ã¸é °í¸¿°Ú½À´Ï´Ù.
Á¦°¡ ÇÁ·Î±×·¥¿¡ ´ëÇÑ ÀÌÇصµ°¡ ¶³¾îÁö°í ÇÏ´Ùº¸´Ï Äڵ嵵 ¾û¸ÁÀÌ°í Áú¹®µµ µÎ¼¾øÀÌ ÇÑ°Í °°¾Æ Á˼ÛÇÏÁö¸¸ µµ¿ÍÁֽøé ÀºÇý ÀØÁö ¾Ê°Ú½À´Ï´Ù.^^
ºÎ»ê ±Ù±³ºÐÀÌ¸é ¼ÒÁÖ ÇÑÀÜ »çµå¸².(±èÇØ°ÅÁÖÀÚ)
S/W | 1884¸íÀÌ Àоú¾î¿ä. 3.138.35.193