ตัวอย่างโค้ดเก็บข้อมูลด้วย VBA ลงใน Database (MS SQL Server)

Public Conn As ADODB.Connection
Public Sub RecVal2(TableName As String)

Dim Statement2 As String, inputString As String
Dim tempDate2 As Date
Dim rs As ADODB.Recordset
Dim myOPC2 As New OpcHelper, Ir1 As String, Ir2 As String

Dim i As Integer, j As Integer, para As String




Set rs = New ADODB.Recordset



    Set rs = New ADODB.Recordset
    Statement2 = "INSERT INTO [" & TableName & "] Values ('" & para1 & "', '" & para2 & "')"
    
    With rs
    
        .CursorLocation = adUseClient
        .ActiveConnection = Conn
        .LockType = adLockBatchOptimistic
        .Open Statement2
        
    End With


Set rs = Nothing
    

End Sub

Public Sub StartConn()

Set Conn = New ADODB.Connection
Conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=SPCGLog;Data Source=(local)\sqlexpress"
'Conn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=" & user & ";Password=" & pw & ";Initial Catalog=SPCGLog;Data Source=" & serverName

Conn.Open

End Sub
    
ErrLabel:

End Sub

Public Sub CloseConn()

Conn.Close
Set Conn = Nothing

End Sub


 

ให้เรียกใช้ startConn เพื่อเริ่มการติดต่อ จากนั้นค่อยเรียก RecVal2 เพื่อเก็บข้อมูล สุดท้ายเรียก closeConn เพื่อปิดการติดต่อ

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s