Private Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Private Sub Form_Load()



 
  Dim cn     As String
  Dim ls     As Long
  Dim res     As Long
    
  cn = String(1024, 0)      '或cn   =   vb.Space(100)
  ls = 1024
    
  res = GetComputerName(cn, ls)
    
  If res <> 0 Then
  ComputerName = Mid(cn, 1, InStr(cn, Chr(0)) - 1)
  Else
  ComputerName = ""
  End If
 
 

 ComputerName = ComputerName + "\WINCC"









'定義連接字符串并建立連接(目的:讀取所有歸檔變量名)
 connstr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=DBA;Initial Catalog=master;Data Source =" & ComputerName & ""
 conn.ConnectionString = connstr
 conn.CursorLocation = 3
 conn.Open connstr
 

'*********************從數(shù)據(jù)庫讀取數(shù)據(jù)庫名***************************************


 sql = "select * from sysdatabases where name like 'CC%R'"
 

 rs.CursorLocation = adUseClient
 Set rs = conn.Execute(sql)
 rs.MoveFirst
 

 database_name = (rs.Fields(0))



 rs.Close
 Set rs = Nothing
 
conn.Close
Set conn = Nothing


'*********************讀取變量號結(jié)束*********************************************





















Dim str As String

 str = "p"
'定義連接字符串并建立連接(目的:讀取所有歸檔變量名)
 connstr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=DBA;Initial Catalog=" & database_name & ";Data Source =" & ComputerName & ""
 conn.ConnectionString = connstr
 conn.CursorLocation = 3
 conn.Open connstr
 

'*********************從數(shù)據(jù)庫讀取變量號***************************************
'從數(shù)據(jù)庫讀取變量號并填充進(jìn)變量顯示清單中
'把變量的相關(guān)數(shù)據(jù)存儲進(jìn)數(shù)組中
'表PDE#TAGs中的變量全是歸檔的變量

 sql = "SELECT * FROM Archive"
 

 rs.CursorLocation = adUseClient
 Set rs = conn.Execute(sql)
 rs.MoveFirst
 
 i = 0
 
Do While Not rs.EOF And Not rs.BOF
 
 Form1.List1.AddItem (rs.Fields(1))
 Archive_Variable_Name(i) = (rs.Fields(1))

 rs.MoveNext
 
 i = i + 1
 
Loop


 rs.Close
 Set rs = Nothing
 
conn.Close
Set conn = Nothing


'*********************讀取變量號結(jié)束*********************************************


 



 '顯示打印參數(shù)設(shè)置界面
  Form1.Show
  
'*********************界面顯示完成***********************************************




End Sub



Private Sub Command1_Click()



'Form1.Hide
  
 
'****************獲取用戶選擇的變量*********************
i = 0
j = 0
Selected_Archive_Variable_Count = 0

For i = 0 To (Form1.List1.ListCount - 1)
  
    If Form1.List1.Selected(i) = True Then
   
      Selected_Archive_Variable_Count = Selected_Archive_Variable_Count + 1
      
      Selected_Archive_Variable_Name(j) = Archive_Variable_Name(i)
      
      
      
      
      'Mid(strin,start[,length])

      'Selected_Archive_Variable_Name(j) = Mid(Selected_Archive_Variable_Name(j), 12, 20)
      
      'RTrim (Selected_Archive_Variable_Name(j))

      
      
      j = j + 1
  
    Else
   
              
    End If

Next


'***************獲取用戶選擇的變量結(jié)束****************

'***************清空數(shù)據(jù)庫先**************************

 connstr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=DBA;Initial Catalog=MYARCHIVE;Data Source =ComputerName"
 conn.ConnectionString = connstr
 conn.CursorLocation = 3
 conn.Open connstr

sql = "DELETE FROM MYARCHIVE"


 rs.CursorLocation = adUseClient
 Set rs = conn.Execute(sql)


'**************清空完成******************************









'***************把選擇好的歸檔變量寫進(jìn)數(shù)據(jù)庫**********
Set conn = New ADODB.Connection
connstr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=DBA;Initial Catalog=MYARCHIVE;Data Source =ComputerName"
conn.Open connstr

Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Open "MYARCHIVE", conn, , , adCmdTable



i = 0
j = 0


 
          
For i = 0 To (Form1.List1.ListCount - 1)
    
            
     If Form1.List1.Selected(i) = True Then
     
    rs.AddNew
    rs!Name = Archive_Variable_Name(i)
    
    
      rs.Update
      booRecordAdded = True
      



   Else
   
    
      
   End If
   

   

Next


rs.Close
 Set rs = Nothing
 
conn.Close
Set conn = Nothing


'MsgeBox ("成功！")

 Unload Form1
End Sub























'*****************************定義公共變量**************************
'和數(shù)據(jù)庫有關(guān)的數(shù)據(jù)
Public conn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public connstr As String
Public sql As String

Public BNM(50) As String
Public Riqi(50) As String
Public shijian1 As Date
Public shijian2 As Date
Public shijian3 As String
Public shijian4 As String

Public database_name As String
'Public ComputerName As String

'和歸檔變量相關(guān)的數(shù)據(jù)
Public Archive_Variable_Name(300)
Public Archive_Variable_TimeFactor(300)

Public Selected_Archive_Variable_Name(100)
Public Selected_Archive_Variable_TimeFactor(100)
Public Selected_Archive_Variable_Count
Public Selected_Archive_Variable_Position(50)

'和用戶操作有關(guān)的數(shù)據(jù)
Public quanxuan_button As Integer
Public User_Select_Public_Time_Zone '用戶是否選擇公共時間軸

'和電子表格相關(guān)的數(shù)據(jù)
Public FirstVariablePostion_In_SheetBook

'******************************************************************

'*****************************定義共用函數(shù)*************************
'********檢查到數(shù)據(jù)庫的連接狀態(tài)************
Public Function GetConState(intState As Integer) As String

  Select Case intState
   Case 0
    GetConState = "adstateclosed"
   Case 1
    GetConState = "adstateopen"
  End Select
  
End Function
'*****************************************

'*****************************************