VBA basic operations-ms SQL server operations

• Read data from the ms SQL Server data table and save it to an Excel worksheet.

 Sub  Returnsqlrecord ()  '  Sht is the Excel worksheet object variable, pointing to a worksheet      Dim I As   Integer , Sht As  Worksheet  '  Define the data link object and save the information of the connected database      '  To use ADODB, you must add the reference "Microsoft ActiveX Data Objects library 2. x" in the tools> references menu"     Dim CN As   New  ADODB. Connection  '  Define record set objects and save data tables      Dim RS As   New  ADODB. recordset  Dim Strcn As   String , Strsql As   String          ' Define the database link string, Server = server name or IP address (enter "." locally), Database = Database Name, uid = User Login Name, Pwd = Password Strcn = "  Provider = sqloledatabase; server = NIKEY-980114BB0; database = pubs; uid = sa; Pwd = sa;  "          '  Define the SQL query command string Strsql = "  Select job_id, job_desc from dbo.jobs  "          '  Establish a connection with the database. If the connection is successful, the connection object CN is returned.  CN. Open strcn  ' Execute the SQL commands contained in strsql and save the results to the RS record set object.  Rs. Open strsql, CN I = 1      '  Point the sht to the sheet 1 worksheet of the current workbook.      Set Sht = thisworkbook. worksheets ( "  Sheet1  "  )  '  When the Data Pointer is not moved to the end of the record set, perform the following operations      Do   While   Not Rs. EOF  '  Save the value of the job_id field of the current record to column 1st of row I of the sheet1 worksheet. Sht. cells (I, 1 ) = RS ( "  Job_id  "  ) Sht. cells (I,  2 ) = RS ( "  Job_desc  "  )  '  Move pointer to the next record Rs. movenext I = I + 1      Loop          '  Disable record set  Rs. Close  '  Close the database link and release resources  CN. Close  End sub 

 

• Read the Excel worksheet data and insert it into the database (insert the A2: D6 record in the sheet 1 worksheet to the jobs data table in the database pubs)

 Sub  Returnsqlrecord ()  Dim IAs   Integer , Sht As  Worksheet  '  Define the data link object and save the information of the connected database      '  To use ADODB, you must add the reference "Microsoft ActiveX Data Objects library 2. x" in the tools> references menu"      Dim CN As   New  ADODB. Connection  Dim Strcn As  String , Strsql As   String          '  Define the database link string, Server = server name or IP address (enter "." locally), Database = Database Name, uid = User Login Name, Pwd = Password Strcn = "  Provider = sqloledb; server =.; database = pubs; uid = sa; Pwd = sa;  "          '  Clear Defined variables Strsql = ""          '  Establish a connection with the database. If the connection is successful, the connection object CN is returned.  CN. Open strcn Set Sht = thisworkbook. worksheets ( "  Sheet1  "  )  For I = 2   To   6          '  Construct an SQL command string. When performing the insert operation on the job_id column, set identity_insert of the table to open; otherwise, insertion will fail. Strsql = strsql & "  Set identity_insert dbo.jobs on; insert into dbo.jobs (job_id, job_desc, min_lvl, max_lvl) values ( "  _ & Sht. cells (I, 1 )& "  ,  " & CSTR (SHT. cells (I, 2 ))& "  ,  " & Sht. cells (I, 3 )& "  ,  " & Sht. cells (I, 4 )& "  );  "      Next      '  Run the SQL command string. If the SQL command is correct, five records will be added to the database. You can also run Rs. Open strsql and CN.  CN. Execute strsql  '  Close the database link and release resources  CN. Close  End sub 

 

	A	B	C	D
1	Job_id	Job_desc	Min_lvl	Max_lvl
2	30	'Test30'	20	100
3	31	'Test31'	20	100
4	32	'Test32'	20	100
5	33	'Test33'	20	100
6	34	'Test34'	20	100