ÓÃExcel+VBA+SQL Server½øÐÐÊý¾Ý´¦Àí
ÓÃExcel+VBA+SQL Server½øÐÐÊý¾Ý´¦Àí
ʹÓÃExcel+VBA+SQL Server½øÐÐÊý¾Ý´¦ÀíÊÇÒ»ÖÖ¼òµ¥ÓÐЧ·½·¨£¬ÕÆÎÕÒÔÏ»ù´¡ÖªÊ¶ÊµÏÖ¿ìËÙÈëÃÅ(ÕÆÎÕexcel/vba/sqlserver¸÷1%ÄÚÈÝ£¬Äã¾ÍÄܳÉΪÊý¾Ý´¦Àí¸ßÊÖµÄ:))£º
Ò»¡¢Excel»ù´¡ÖªÊ¶
Á˽⹤×÷²¾(Workbook)¡¢¹¤×÷±í(Worksheet)¡¢µ¥Ôª¸ñ(Cell)µÈµÄ»ù±¾¸ÅÄÊìϤһЩ»ù±¾²Ù×÷¡£
¶þ¡¢SQL Server»ù´¡ÖªÊ¶
²Î¼ûhttp://distance.njtu.edu.cn/course/8100062/kejian/index.htm
1¡¢Êý¾Ý¿âÓйظÅÄÊý¾Ý¿â¡¢±í¡¢¼Ç¼¡¢×Ö¶Î
A)Êý¾Ý¿â(Database)
B)±í(Table)¡¢¼Ç¼£¨ÐУ¬Row,Record£©¡¢×ֶΣ¨ÁУ¬Column,Field£©...
2¡¢³£¼ûÊý¾Ý²Ù×÷µÄSQLÃüÁselect, insert , update ,delete
Èý¡¢VBA»ù´¡ÖªÊ¶£º
1¡¢»ù±¾¸ÅÄî¡£
2¡¢»ù±¾¿ØÖƽṹ£º
·Ë³Ðò½á¹¹:³ÌÐò°´Ë³ÐòÖ´ÐÐ;
··ÖÖ§½á¹¹ÃüÁî:
if Ìõ¼þ then
<Èç¹ûÌõ¼þ³ÉÁ¢Ö´Ðб¾Óï¾ä¿é>
end if
»ò£º
if ... then
...
else
...
end if
»ò£º
if ... then
...
elseif ...
...
else
...
end if
µÈ¡£¡£
·Ñ»·½á¹¹ÃüÁ
for i=? to ??
...
next
»ò
do while ...
...
loop
3¡¢ÔÚVBAÖвÙ×ݶÔÏó£¬ÏÈÀí½â²Ù×ÝEXCEL¹¤×÷±íºÍÊý¾Ý¿â¶ÔÏó£º
½«ÖµÐ´ÈëEXCELµ¥Ôª¸ñ£¬È磺thisworkbook.worksheets("sheet1").cells(1,2)=1234444
´ÓEXCELµ¥Ôª¸ñÈ¡µÃÊýÖµ£¬È磺x=thisworkbook.worksheets("sheet1").cells(1,2)
Êý¾Ý¿â²Ù×÷£º
cn.open ...£¨½¨Á¢Êý¾ÝÁ¬½Ó¶ÔÏó£©
rs.open ... £¨½¨Á¢Êý¾Ý¼¯¶ÔÏó£©
x=rs("...") £¨¶ÁÈ¡ÊýÖµ£©
rs.close £¨¹Ø±Õrs£©
cn.close £¨¹Ø±Õcn£©
cn.execute £¨Ö´ÐÐsqlÓï¾ä£©
...
ËÄ¡¢Àý×Ó
sub test() '¶¨Òå¹ý³ÌÃû³Æ
Dim i As Integer, j As Integer, sht As Worksheet 'i,jΪÕûÊý±äÁ¿£»sht Ϊexcel¹¤×÷±í¶ÔÏó±äÁ¿£¬Ö¸Ïòijһ¹¤×÷±í
Dim cn As New ADODB.Connection '¶¨ÒåÊý¾ÝÁ´½Ó¶ÔÏó £¬±£´æÁ¬½ÓÊý¾Ý¿âÐÅÏ¢£»ÇëÏÈÌí¼ÓADOÒýÓÃ
Dim rs As New ADODB.Recordset '¶¨Òå¼Ç¼¼¯¶ÔÏ󣬱£´æÊý¾Ý±í
Dim strCn As String ,strSQL as String '×Ö·û´®±äÁ¿
strCn = "Provider=sqloledb;Server=·þÎñÆ÷Ãû³Æ»òI
Ïà¹ØÎĵµ£º
Êý¾Ý¿âËùÓÐÕß (DataBase Owner)
dbo ÊǾßÓÐÔÚÊý¾Ý¿âÖÐÖ´ÐÐËùÓлµÄ°µÊ¾ÐÔȨÏÞµÄÓû§¡£½«¹Ì¶¨·þÎñÆ÷½ÇÉ« sysadmin µÄÈκγÉÔ±¶¼Ó³É䵽ÿ¸öÊý¾Ý¿âÄÚ³ÆΪ dbo µÄÒ»¸öÌØÊâÓû§ÉÏ¡£ÁíÍ⣬Óɹ̶¨·þÎñÆ÷½ÇÉ« sysadmin µÄÈκγÉÔ±´´½¨µÄÈκζÔÏó¶¼×Ô¶¯ÊôÓÚ ......
×î½üÓÐÅóÓÑÓöµ½Ê¡ÊеÄÎÊÌ⣬ÏëÏë×Ô¼º½ñºóÒ²ÓпÉÄÜ»áÓöµ½£¬ËùÒÔ¾Í×Ô¼ºÔÚÍøÉÏдд£¬ËÑËÑ£¬Ö÷ÒªÊǶÔ×Ô¼º½ñºóÓвο¼
--´´½¨Êý¾Ý¿â
create database NationalAll
Go
--ʹÓÃNationalAllÊý¾Ý¿â
use NationalAll
Go
--´´½¨Ê¡¼¶±í
Create Table Province
(
ProID int primary key not null,
ProName nvarchar(50) n ......
--Excelµ¼Èësql´æ´¢¹ý³Ì£¨¼òµ¥£©
Create procedure import
@tablename varchar(100),
@filepath varchar(100)
as
EXEC( 'INSERT INTO ' + @tablename +
' SELECT * from
OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',''Data Source="' + @filepath + '";
......
²éѯËÙ¶ÈÂýµÄÔÒòºÜ¶à£¬³£¼ûÈçϼ¸ÖÖ£º
1¡¢Ã»ÓÐË÷Òý»òÕßûÓÐÓõ½Ë÷Òý(ÕâÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣬ÊdzÌÐòÉè¼ÆµÄȱÏÝ)
2¡¢I/OÍÌÍÂÁ¿Ð¡£¬ÐγÉÁËÆ¿¾±Ð§Ó¦¡£
3¡¢Ã»Óд´½¨¼ÆËãÁе¼Ö²éѯ²»ÓÅ»¯¡£
4¡¢ÄÚ´æ²»×ã
5¡¢ÍøÂçËÙ¶ÈÂý
6¡¢²éѯ³öµÄÊý¾ÝÁ¿¹ý´ó£¨¿ÉÒÔ²ÉÓöà´Î²éѯ£¬ÆäËûµÄ·½·¨½µµÍÊý¾ÝÁ¿£©
7¡¢Ëø»òÕßËÀËø(ÕâÒ²ÊDzéѯÂý×î³ ......
SQL²Ù×÷È«¼¯
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º& ......