vb与数据库的几种连接方法

1、、用 dao 控件连接数据库1.与 access2000数据库连接private sub command1_click() 也可直接在控件属性中设置以下各项但在控件属性中不能写入密码只有在数据数没有密码的情况下可以省略data1.refresh data1.connect = access 2000; data1.databasename = app. path + /chncmadb.mdb 数据库没有密码此句可省data1.connect = ;pwd=123456 data1.recordsource = 耕地资源管理单元属性数据表2004data1.recordsource = sel

2、ect * from 耕地资源管理单元属性数据表2004 data1.refresh move 后才能正确显示记录个数end sub 2.与没有密码的dbf 文件数据库连接private sub command2_click() data1.connect = dbase iii; data1.databasename = app. path data1.recordsource =dbf data1.recordsource = select * from dbf data1.refresh move 后才能正确显示记录个数end sub 3.与没有密码的excel 文件数据库连接priva

3、te sub command3_click() data1.connect = excel 8.0; data1.databasename = app.path & /excel.xls data1.recordsource = select * from excel.xls data1.refresh move 后才能正确显示记录个end sub 二、用 dao 代码连接数据库在使用 dao 对象前应选定visual basic菜单下的 工程 中的引用了菜单中的microsoft dao 3.6 object library 选项，或其它版本1.dao 代码与 access数据库连接

4、private sub command1_click() dim db as database dim rs as recordset 以共享、读写方式打开如果无密码最后一个参数可以不要setdb= opendatabase(app.path & /chncmadb.mdb, false, false, ;pwd=123456) 不需要 move 来更新记录个数set rs = db.openrecordset(耕地资源管理单元属性数据表2004) 需要 move 来更新记录个数set rs = db.openrecordset(select * from 耕地资源管理单元属性数据表2

5、004) if rs.recordcount 0 then rs.movelast rs.movefirst end if end sub 2.dao 代码与没有密码的dbf 文件数据库连接private sub command2_click() dim db as database dim rs as recordset 以共享、读写方式打开set db = opendatabase(app.path, false, false, dbase iii;) 不需要 move 来更新记录个数set rs = db.openrecordset(dbf) 需要 move 来更新记录个数set rs

6、= db.openrecordset(select * from dbf) if rs.recordcount 0 then rs.movelast rs.movefirst end if end sub 3. dao 代码与没有密码的excel 文件数据库连接private sub command3_click() dim db as database dim rs as recordset 以共享、读写方式打开如果无密码最后一个参数可以不要set db = opendatabase(app.path & /excel.xls, false, false, excel 8.0;) 不

7、需要 move 来更新记录个数 set rs = db.openrecordset(excel.xls) 表格中的工作目录sheet 需要 move 来更新记录个数set rs = db.openrecordset(select * from excel.xls) 表格中的工作目录sheet if rs.recordcount 0 then rs.movelast rs.movefirst end if end sub 三、用 ado 控件连接数据库也可直接在控件属性中设置以下各项1.ado 控件与 access2000数据库连接private sub command1_click() 连接有

8、密码的access 数据库adodc1.connectionstring = provider=microsoft.jet.oledb.4.0;data source= & app.path & /chncmadb1.mdb;jet oledb:database password=123456 连接没有密码的access 数据库adodc1.connectionstring = provider=microsoft.jet.oledb.4.0;data source= & app.path & /chncmadb.mdb;persist security inf

9、o=false adodc1.recordsource = 耕地资源管理单元属性数据表2004 adodc1.recordsource = select * from 耕地资源管理单元属性数据表2004 adodc1.refresh set datagrid1.datasource = adodc1 datagrid1.refresh end sub 2.ado 控件与 dbf 表连接private sub command2_click() adodc1.connectionstring = provider=msdasql.1;persist security info=false;data

10、 source=dbase files;dbq= & app.path & ;sourcetype=dbf; adodc1.connectionstring = provider=msdasql.1;persist security info=false;extended properties=dsn=visual foxpro tables;uid=;sourcedb=”& app.path &”;sourcetype=dbf;exclusive=no;backgroundfetch=yes;collate=machine;null=yes;deleted=y

11、es; adodc1.connectionstring = provider=msdasql.1;persist security info=false;extended properties=dsn=dbase files;dbq=”& app.path &”;defaultdir=”& app.path &”;driverid=533;maxbuffersize=2048;pagetimeout=5;能使表名长度不受限制adodc1.connectionstring = provider=msdasql.1;driver=microsoft visual f

12、oxpro driver;sourcedb= & app.path & ;sourcetype=dbf;locale identifier=2052 adodc1.recordsource = dbf1 adodc1.recordsource = select * from dbf1 adodc1.refresh set datagrid1.datasource = adodc1 datagrid1.refresh end sub 3.ado 控件与 excel 表连接private sub command3_click() 下面一句测试未能通过adodc1.connectio

13、nstring = data provider=msdasql.1;driver=microsoft excel driver *.xls);dbq= & app.path & /excel.xls adodc1.connectionstring=provider=msdasql.1;persist security info=false;extended properties=dsn=excel files;dbq= & app.path & /excel.xls;defaultdir=”&app.path &”;driverid=790;ma

14、xbuffersize=2048;pagetimeout=5;adodc1.connectionstring = provider=microsoft.jet.oledb.4.0;persist security info=false;data source= & app.path & /excel.xls;extended properties=excel 8.0;hdr=yes adodc1.recordsource = excel.xls adodc1.recordsource = select * from excel.xls adodc1.refresh set da

15、tagrid1.datasource = adodc1 datagrid1.refresh end sub 4.ado 控件与 oracle 数据库连接private sub command4_click() adodc1.connectionstring = provider=msdaora.1;password=chncmadb;user id=chncmadb;data source=towebserver;persist security info=true adodc1.connectionstring=provider=oraoledb.oracle.1;password=chnc

16、madb;persist security info=true;user id=chncmadb;data source=towebserver adodc1.recordsource = t320481tr012004 表名不能加方括号adodc1.recordsource = select * from t320481tr012004 adodc1.refresh set datagrid1.datasource = adodc1 datagrid1.refresh end sub 5.ado 控件与 sqlserver数据库连接未测试private sub command5_click(

17、) adodc1.connectionstring = provider=sqloledb.1;password=111;persist security info=true;user id=111;initial catalog=111;data source=111 adodc1.recordsource = t320481tr012004 adodc1.recordsource = select * from t320481tr012004 adodc1.refresh set datagrid1.datasource = adodc1 datagrid1.refresh end sub

18、 四、用 ado 代码连接数据库在使用 ado 对象前应选定visual basic菜单下的 工程 中的引用了菜单中的microsoft.activex data object 2.5 library选项，或其它版本1.ado 代码与 access2000数据库连接private sub command1_click() dim adocnn as adodb.connection dim adors as adodb.recordset set adocnn = new adodb.connection set adors = new adodb.recordset adocnn.curso

19、rlocation = aduseclient .open 后面的字符串可以参考ado 控件连接 .connectionstring后面的的字符串adocnn.open provider=microsoft.jet.oledb.4.0;data source= & app.path & /chncmadb1.mdb;jet oledb:database password=123456 adors.open select * from 耕地资源管理单元属性数据表2004, adocnn, adopendynamic, adlockpessimistic, adcmdtext se

20、t datagrid1.datasource = adors set adors = nothing set adocnn = nothing end sub 2.ado 代码与 dbf 表连接private sub command2_click() dim adocnn as adodb.connection dim adors as adodb.recordset set adocnn = new adodb.connection set adors = new adodb.recordset adocnn.cursorlocation = aduseclient .open 后面的字符串

21、可以参考ado 控件连接 .connectionstring后面的的字符串adocnn.open provider=msdasql.1;driver=microsoft visual foxpro driver;sourcedb= & app.path & ;sourcetype=dbf;locale identifier=2052 adors.open select * from dbf1, adocnn, adopendynamic, adlockpessimistic, adcmdtext set datagrid1.datasource = adors set ador

22、s = nothing set adocnn = nothing end sub 3.ado 代码与 excel 表连接private sub command3_click() dim adocnn as adodb.connection dim adors as adodb.recordset set adocnn = new adodb.connection set adors = new adodb.recordset adocnn.cursorlocation = aduseclient .open 后面的字符串可以参考ado 控件连接 .connectionstring后面的的字符串

23、adocnn.openprovider=microsoft.jet.oledb.4.0;persist security info=false;data source= & app.path & /excel.xls;extended properties=excel 8.0;hdr=yes adors.open select * from excel.xls, adocnn, adopendynamic, adlockpessimistic, adcmdtext set datagrid1.datasource = adors set adors = nothing set

24、adocnn = nothing end sub 4.ado 代码与 oracle 数据库连接private sub command4_click() dim adocnn as adodb.connection dim adors as adodb.recordset set adocnn = new adodb.connection set adors = new adodb.recordset adocnn.cursorlocation = aduseclient .open 后面的字符串可以参考ado 控件连接 .connectionstring后面的的字符串adocnn.open provider=oraoledb.oracle.1;password=chncmadb;p