Private Sub Command1_Click()
Dim i As Long
Dim db As Database
Set db = OpenDatabase("c:\aa.mdb")
Dim t As DAO.TableDef
Set t = db.CreateTableDef("##")
For i = 1 To 5
t.Fields.Append t.CreateField("a" & CStr(i), dbLong)
Next i
db.TableDefs.Append t
End Sub
Dim i As Long
Dim db As Database
Set db = OpenDatabase("c:\aa.mdb")
Dim t As DAO.TableDef
Set t = db.CreateTableDef("##")
For i = 1 To 5
t.Fields.Append t.CreateField("a" & CStr(i), dbLong)
Next i
db.TableDefs.Append t
End Sub
例如:
A(0,0),A(0,1)......A(0,j)
A(1,0).............A(1,j)
........................
........................
A(i,0).............A(i,j)
我要建一个零时表,它有 j 个字段,第一个字段放的是A(0,0),A(1,0)...A(i,0),第二个字段放的是A(0,1),A(1,1)...A(i,1),....第j+1个字段放的是A(0,j),A(1,j)...A(i,j),如何做?
请给出代码!!
Dim i As Long
dim j as long
Dim db As Database
Dim temptable As DAO.TableDef
set mydatabase=OpenDatabase("c:\aa.mdb")
'create #temptable
Set temptable = db.CreateTableDef("##")
for j=0 to value'你的j+1的大小
temptable.Fields.Append temptable.CreateField("a" & CStr(i), dbLong)
Next j
db.TableDefs.Append temptable'创建一个存储过程把i的值写入.
首先在SQL Server中建立数据库,建立DSN
本代码只用了ADO的引用,没有任何控件
注意语句中的空格绝对不能少!Private Sub Command1_Click()
Dim MyDB As ADODB.Connection '声明数据库连接
Dim MyArray(3, 5) As String '你的二维数组
Dim MyFieldName() As String '声明新建表的每个字段名数组
Dim CreatTableSQLStatement As String '声明新建表需要的SQL语句
Dim InsertSQLStatement1 As String '声明添加记录用SQL语句(临时1)
Dim InsertSQLStatement2 As String '声明添加记录用SQL语句(临时2)
Dim InsertSQLStatement As String '声明添加记录用SQL语句(最终)
Dim n, m, p, i, j As Integer
'通过二维数组第二维的上标得到字段名
For n = 0 To UBound(MyArray, 2) ',2表示需要得到第二维的上标
ReDim Preserve MyFieldName(n) As String
MyFieldName(n) = "fieldname_" & n
Next
'连接数据库
Set MyDB = New ADODB.Connection
MyDB.Open "PROVIDER=MSDASQL;dsn=OP;uid=sa;pwd=pass;" '通过得到的字段名,得到建立表的SQL语句,字段类型为长度40的可变字符串
'注意括号内的一定要有空格!!详细请参照SQL方面的书籍
CreatTableSQLStatement = "CREATE TABLE mytable ("
For m = 0 To UBound(MyFieldName)
If m <> UBound(MyFieldName) Then
CreatTableSQLStatement = CreatTableSQLStatement & MyFieldName(m) & " VARCHAR(40),"
Else
CreatTableSQLStatement = CreatTableSQLStatement & MyFieldName(m) & " VARCHAR(40))"
End If
Next
'执行语句,建立表,表名为mytable
MyDB.Execute CreatTableSQLStatement
'设定或计算你的二维数组的值,这里简单地付值
MyArray(0, 0) = "is00"
MyArray(0, 1) = "is01"
MyArray(0, 2) = "is02"
MyArray(0, 3) = "is03"
MyArray(0, 4) = "is04"
MyArray(0, 5) = "is05"
MyArray(1, 0) = "is10"
MyArray(1, 1) = "is11"
MyArray(1, 2) = "is12"
MyArray(1, 3) = "is13"
MyArray(1, 4) = "is14"
MyArray(1, 5) = "is15"
MyArray(2, 0) = "is20"
MyArray(2, 1) = "is21"
MyArray(2, 2) = "is22"
MyArray(2, 3) = "is23"
MyArray(2, 4) = "is24"
MyArray(2, 5) = "is25"
MyArray(3, 0) = "is30"
MyArray(3, 1) = "is31"
MyArray(3, 2) = "is32"
MyArray(3, 3) = "is33"
MyArray(3, 4) = "is34"
MyArray(3, 5) = "is35"
'得到添加记录用SQL语句,我的算法可能太复杂了
InsertSQLStatement1 = "INSERT mytable ("
For p = 0 To UBound(MyFieldName)
If p <> UBound(MyFieldName) Then
InsertSQLStatement1 = InsertSQLStatement1 & MyFieldName(p) & ","
Else
InsertSQLStatement1 = InsertSQLStatement1 & MyFieldName(p) & ") "
End If
Next
'循环二维数组第一维的上标次
For i = 0 To UBound(MyArray, 1) ',1表示需要得到第一维的上标
InsertSQLStatement2 = "VALUES ("
'循环二维数组第二维的上标次
For j = 0 To UBound(MyArray, 2) ',2表示需要得到第二维的上标
If j <> UBound(MyArray, 2) Then
InsertSQLStatement2 = InsertSQLStatement2 & "'" & MyArray(i, j) & "',"
Else
InsertSQLStatement2 = InsertSQLStatement2 & "'" & MyArray(i, j) & "')"
End If
Next
'得到最终添加记录用的SQL语句
InsertSQLStatement = InsertSQLStatement1 & InsertSQLStatement2
'向表mytable中添加记录
MyDB.Execute InsertSQLStatement
Next
End Sub
以下是SQL Server Query Analyzer的查询结果
select * from mytablefieldname_0 fieldname_1 fieldname_2 fieldname_3 fieldname_4 fieldname_5
-------------------------------------------------------------------------------------------
is00 is01 is02 is03 is04 is05
is10 is11 is12 is13 is14 is15
is20 is21 is22 is23 is24 is25
is30 is31 is32 is33 is34 is35(4 row(s) affected)是你希望的结果吗?
首先在SQL Server中建立数据库,建立DSN
本代码只用了ADO的引用,没有任何控件
注意语句中的空格绝对不能少!Private Sub Command1_Click()
Dim MyDB As ADODB.Connection '声明数据库连接
Dim MyArray(3, 5) As String '你的二维数组
Dim MyFieldName() As String '声明新建表的每个字段名数组
Dim CreatTableSQLStatement As String '声明新建表需要的SQL语句
Dim InsertSQLStatement1 As String '声明添加记录用SQL语句(临时1)
Dim InsertSQLStatement2 As String '声明添加记录用SQL语句(临时2)
Dim InsertSQLStatement As String '声明添加记录用SQL语句(最终)
Dim n, m, p, i, j As Integer
'通过二维数组第二维的上标得到字段名
For n = 0 To UBound(MyArray, 2) ',2表示需要得到第二维的上标
ReDim Preserve MyFieldName(n) As String
MyFieldName(n) = "fieldname_" & n
Next
'连接数据库
Set MyDB = New ADODB.Connection
MyDB.Open "PROVIDER=MSDASQL;dsn=OP;uid=sa;pwd=pass;" '通过得到的字段名,得到建立表的SQL语句,字段类型为长度40的可变字符串
'注意括号内的一定要有空格!!详细请参照SQL方面的书籍
CreatTableSQLStatement = "CREATE TABLE mytable ("
For m = 0 To UBound(MyFieldName)
If m <> UBound(MyFieldName) Then
CreatTableSQLStatement = CreatTableSQLStatement & MyFieldName(m) & " VARCHAR(40),"
Else
CreatTableSQLStatement = CreatTableSQLStatement & MyFieldName(m) & " VARCHAR(40))"
End If
Next
'执行语句,建立表,表名为mytable
MyDB.Execute CreatTableSQLStatement
'设定或计算你的二维数组的值,这里简单地付值
MyArray(0, 0) = "is00"
MyArray(0, 1) = "is01"
MyArray(0, 2) = "is02"
MyArray(0, 3) = "is03"
MyArray(0, 4) = "is04"
MyArray(0, 5) = "is05"
MyArray(1, 0) = "is10"
MyArray(1, 1) = "is11"
MyArray(1, 2) = "is12"
MyArray(1, 3) = "is13"
MyArray(1, 4) = "is14"
MyArray(1, 5) = "is15"
MyArray(2, 0) = "is20"
MyArray(2, 1) = "is21"
MyArray(2, 2) = "is22"
MyArray(2, 3) = "is23"
MyArray(2, 4) = "is24"
MyArray(2, 5) = "is25"
MyArray(3, 0) = "is30"
MyArray(3, 1) = "is31"
MyArray(3, 2) = "is32"
MyArray(3, 3) = "is33"
MyArray(3, 4) = "is34"
MyArray(3, 5) = "is35"
'得到添加记录用SQL语句,我的算法可能太复杂了
InsertSQLStatement1 = "INSERT mytable ("
For p = 0 To UBound(MyFieldName)
If p <> UBound(MyFieldName) Then
InsertSQLStatement1 = InsertSQLStatement1 & MyFieldName(p) & ","
Else
InsertSQLStatement1 = InsertSQLStatement1 & MyFieldName(p) & ") "
End If
Next
'循环二维数组第一维的上标次
For i = 0 To UBound(MyArray, 1) ',1表示需要得到第一维的上标
InsertSQLStatement2 = "VALUES ("
'循环二维数组第二维的上标次
For j = 0 To UBound(MyArray, 2) ',2表示需要得到第二维的上标
If j <> UBound(MyArray, 2) Then
InsertSQLStatement2 = InsertSQLStatement2 & "'" & MyArray(i, j) & "',"
Else
InsertSQLStatement2 = InsertSQLStatement2 & "'" & MyArray(i, j) & "')"
End If
Next
'得到最终添加记录用的SQL语句
InsertSQLStatement = InsertSQLStatement1 & InsertSQLStatement2
'向表mytable中添加记录
MyDB.Execute InsertSQLStatement
Next
End Sub
以下是SQL Server Query Analyzer的查询结果
select * from mytablefieldname_0 fieldname_1 fieldname_2 fieldname_3 fieldname_4 fieldname_5
-------------------------------------------------------------------------------------------
is00 is01 is02 is03 is04 is05
is10 is11 is12 is13 is14 is15
is20 is21 is22 is23 is24 is25
is30 is31 is32 is33 is34 is35(4 row(s) affected)是你希望的结果吗?
把开始的“注意语句中的空格绝对不能少!”改为“注意SQL语句中的空格绝对不能少!”
set rs=createobject("adodb.recordset")
rs.fields.append "username",200,20
rs.fields.append "password",200,20
'你可以自己定义所有的字段
......
rs.open
rs.addnew '添加纪录
rs("username")="aaa"
rs("password")="bbb"
......
rs.update你可以将这个无连接的recordset保存到一个临时文件,在必要的时候再打开
rs.save "D:\tmp.rst",0打开一个rst文件:
set rs=createobject("adodb.recordset")
rs.open "D:\tmp.rst"
...
dim rs as recordset
set db=engin.createdatabase("c:\aa.mdb")
现在我创建了数据集,采用了“hydnoahark(诺亚方舟)”的方法,在用Crystal Report 8 实现打印时,m_Report.Database.SetDataSource rs,居然出错:“下标越界”我查过记录集rs绝对没问题,是什么原因呢?是不是要在生成完rs后,执行rs.Close,然后在把rs作为数据源时执行rs.open 之类的语句呢?还是什么其它原因?
一个Form(form1)上有一个commandbox(Command1)和两个TextBox(text1,text2):
Private Sub Command1_Click()
Set rs = CreateObject("adodb.recordset")
rs.fields.append "username", 200, 20
rs.fields.append "password", 200, 20
rs.open
rs.addnew 'Ìí¼Ó¼Í¼
rs("username") = "aaa"
rs("password") = "bbb"
rs.Update
Set Text1.DataSource = rs
Text1.DataField = "username"
Set Text2.DataSource = rs
Text2.DataField = "password"
rs.Close
Set rs = NothingEnd Sub测试没有问题