2、你试试这样做,引用ado Dim Con As ADODB.Connection Dim rs As ADODB.Recordset Set Con = New ADODB.Connection strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\计划管理系统.mdb;Persist Security Info=False" Con.Open strCon Set rs = New ADODB.Recordset '添加记录 strsql="select * from table" rs.Open strsql, Con, adOpenKeyset, adLockOptimistic rs.addnew rs!字段1=text1.text rs!字段2=text2.text ........ rs.update rs.close set rs=nothing '修改记录 strsql="select * from table where id=1" rs.Open strsql, Con, adOpenKeyset, adLockOptimistic rs!字段1=text1.text rs!字段2=text2.text ........ rs.update rs.close set rs=nothing
'删除记录 strsql="delete from table where id=1" rs.Open strsql, Con, adOpenKeyset, adLockOptimistic
Access 系统表 MsysObjects 包含了数据库对象列表 使用下列 SQL 语句来获取你想要的得到窗体: SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)=-32768 ORDER BY MSysObjects.Name; 得到表: SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name; 得到报表: SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)= -32764 ORDER BY MSysObjects.Name; 得到模块: SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)= -32761 ORDER BY MSysObjects.Name; 得到宏: SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)= -32766 ORDER BY MSysObjects.Name; 使用下列 SQL 语句来获得SQL的用户表:Select [Name] From sysobjects Where xType='U' And [Name]<>'dtproperties'
可以使用ADO来操作数据库:'查询用户表是否存在(Access数据库) 菜单“工程”-->"引用"-->"Microsoft ActiveX Data Objects 2.X Library"Private Sub Command1_Click() Dim cn As New ADODB.Connection , rs As New ADODB.Recordset cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp.mdb;Persist Security Info=False" rs.Open "SELECT [Name] FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (Left$([Name],4) <> "Msys") AND [Type]=1 ", cn, adOpenDynamic, adLockReadOnly If Not rs.EOF Then rs.MoveFirst Do While rs.EOF If rs(0) = "你的表名" Then MsgBox "用户表已经存在!" Exit Do End If Loop Else MsgBox "用户表不存在!" End If rs.Close cn.Close Set rs=Nothing Set cn=Nothing End Sub========================================================================= '查询用户表是否存在(sql数据库)菜单“工程”-->"引用"-->"Microsoft ActiveX Data Objects 2.X Library"Private Sub Command1_Click() Dim cn As New ADODB.Connection , rs As New ADODB.Recordset cn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=登陆用户名;Password=登录密码;Initial Catalog=数据库名;Data Source=服务器别名/IP" rs.Open "Select [Name] From sysobjects Where xType='U' And [Name]<>'dtproperties' ", cn, adOpenDynamic, adLockReadOnly If Not rs.EOF Then rs.MoveFirst Do While rs.EOF If rs(0) = "你的表名" Then MsgBox "用户表已经存在!" Exit Do End If Loop Else MsgBox "用户表不存在!" End If rs.Close cn.Close Set rs=Nothing Set cn=Nothing End Sub
插入、修改记录可以像一楼所说的那样做,也可以用Insert into 语句,Update 语句来做: 插入记录: cn.Execute("insert into tableName values('" & txt1.Text & "','" & txt2.Text & "'")修改记录: cn.Execute("update tableName Set field1='" & txt1.Text & "',filed2='" & txt2.Text & "'")
上面给的查询语句有错误,把其中的双引号改为单引号:rs.Open "SELECT [Name] FROM MsysObjects WHERE (Left([Name],1)<>'~') AND (Left$([Name],4) <> 'Msys') AND [Type]=1 ", cn, adOpenDynamic, adLockReadOnly
dim cn as adodb.connection dim rs as adodb.recordset dim FindTableName as string '连接部分就不写了,请参考ADO210.chm中的示例 '...... '获得表结构 set rs=cn.OpenSchema(adSchemaTables) While not rs.EOF if rs("TABLE_NAME")=FindTableName then '找到该表了...... end if rs.movenext wend '创建表,插入修改等等 dim strSql as string strSql="create table......" cn.excute (strSql)
Dim Con As ADODB.Connection
Dim rs As ADODB.Recordset
Set Con = New ADODB.Connection
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\计划管理系统.mdb;Persist Security Info=False"
Con.Open strCon
Set rs = New ADODB.Recordset
'添加记录
strsql="select * from table"
rs.Open strsql, Con, adOpenKeyset, adLockOptimistic
rs.addnew
rs!字段1=text1.text
rs!字段2=text2.text
........
rs.update
rs.close
set rs=nothing
'修改记录
strsql="select * from table where id=1"
rs.Open strsql, Con, adOpenKeyset, adLockOptimistic
rs!字段1=text1.text
rs!字段2=text2.text
........
rs.update
rs.close
set rs=nothing
'删除记录
strsql="delete from table where id=1"
rs.Open strsql, Con, adOpenKeyset, adLockOptimistic
使用下列 SQL 语句来获取你想要的得到窗体:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)=-32768 ORDER BY MSysObjects.Name; 得到表:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name; 得到报表:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)= -32764 ORDER BY MSysObjects.Name; 得到模块:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)= -32761 ORDER BY MSysObjects.Name; 得到宏:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)= -32766 ORDER BY MSysObjects.Name;
使用下列 SQL 语句来获得SQL的用户表:Select [Name] From sysobjects Where xType='U' And [Name]<>'dtproperties'
菜单“工程”-->"引用"-->"Microsoft ActiveX Data Objects 2.X Library"Private Sub Command1_Click()
Dim cn As New ADODB.Connection , rs As New ADODB.Recordset
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp.mdb;Persist Security Info=False"
rs.Open "SELECT [Name] FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (Left$([Name],4) <> "Msys") AND [Type]=1 ", cn, adOpenDynamic, adLockReadOnly
If Not rs.EOF Then
rs.MoveFirst
Do While rs.EOF
If rs(0) = "你的表名" Then
MsgBox "用户表已经存在!"
Exit Do
End If
Loop
Else
MsgBox "用户表不存在!"
End If
rs.Close
cn.Close
Set rs=Nothing
Set cn=Nothing
End Sub=========================================================================
'查询用户表是否存在(sql数据库)菜单“工程”-->"引用"-->"Microsoft ActiveX Data Objects 2.X Library"Private Sub Command1_Click()
Dim cn As New ADODB.Connection , rs As New ADODB.Recordset
cn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=登陆用户名;Password=登录密码;Initial Catalog=数据库名;Data Source=服务器别名/IP"
rs.Open "Select [Name] From sysobjects Where xType='U' And [Name]<>'dtproperties'
", cn, adOpenDynamic, adLockReadOnly
If Not rs.EOF Then
rs.MoveFirst
Do While rs.EOF
If rs(0) = "你的表名" Then
MsgBox "用户表已经存在!"
Exit Do
End If
Loop
Else
MsgBox "用户表不存在!"
End If
rs.Close
cn.Close
Set rs=Nothing
Set cn=Nothing
End Sub
插入记录:
cn.Execute("insert into tableName values('" & txt1.Text & "','" & txt2.Text & "'")修改记录:
cn.Execute("update tableName Set field1='" & txt1.Text & "',filed2='" & txt2.Text & "'")
dim rs as adodb.recordset
dim FindTableName as string
'连接部分就不写了,请参考ADO210.chm中的示例
'......
'获得表结构
set rs=cn.OpenSchema(adSchemaTables)
While not rs.EOF
if rs("TABLE_NAME")=FindTableName then
'找到该表了......
end if
rs.movenext
wend
'创建表,插入修改等等
dim strSql as string
strSql="create table......"
cn.excute (strSql)