第1个回答 2008-12-20
给你几段代码参考下，自己写出来应该没什么问题,程序自己写才有意思
一段是创建数据库和数据表的代码，令一段是读取数据库access记录到Excel表的，和你的要求正好相反，都是用ADO实现,自己改吧
Public Sub TEST1()
Dim myDb As DAO.Database
Dim myTbl As DAO.TableDef
Dim myData As String
Dim myTable As String
myData = ThisWorkbook.Path & "\学生成绩管理.mdb"
myTable = "期末成绩"
On Error Resume Next
Kill myData
On Error GoTo 0
Set myDb = CreateDatabase(myData, dbLangChineseSimplified)
Set myTbl = myDb.CreateTableDef(myTable)
With myTbl
.Fields.Append .CreateField("学号", dbText, 10)
.Fields.Append .CreateField("姓名", dbText, 6)
.Fields.Append .CreateField("性别", dbText, 1)
.Fields.Append .CreateField("班级", dbText, 10)
.Fields.Append .CreateField("数学", dbSingle)
.Fields.Append .CreateField("语文", dbSingle)
.Fields.Append .CreateField("物理", dbSingle)
.Fields.Append .CreateField("化学", dbSingle)
.Fields.Append .CreateField("英语", dbSingle)
.Fields.Append .CreateField("总分", dbSingle)
End With
myDb.TableDefs.Append myTbl
myDb.Close
Set myDb = Nothing
Set myTbl = Nothing
MsgBox "创建数据库成功！" & vbCrLf _
& "数据库文件名为：" & myData & vbCrLf _
& "数据表名称为：" & myTable & vbCrLf _
& "保存位置：" & ThisWorkbook.Path, _
vbOKOnly + vbInformation, "创建数据库"
End Sub
Public Sub TEST2()
Dim myData As String, myTable As String, SQL As String
Dim myDb As DAO.Database
Dim myRs As DAO.Recordset
Dim i As Integer
ActiveSheet.Cells.Clear
myData = ThisWorkbook.Path & "\职工管理.mdb"
myTable = "职工基本信息"
Set myDb = OpenDatabase(myData)
SQL = "select * from " & myTable & " order by 职工编号"
Set myRs = myDb.OpenRecordset(SQL)
MsgBox "数据库中的记录数为：" & myRs.RecordCount
If myRs.RecordCount > 0 Then
For i = 1 To myRs.Fields.Count
Cells(1, i) = myRs.Fields(i - 1).Name
Next i
With Range(Cells(1, 1), Cells(1, myRs.Fields.Count))
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
Range("A2").CopyFromRecordset myRs
ActiveSheet.Cells.Font.Size = 10
ActiveSheet.Columns.AutoFit
End If
myRs.Close
myDb.Close
Set myRs = Nothing
Set myDb = Nothing
End Sub