创建表: '引用Microsoft ADO Ext 2.5 for DDL and Security Dim cat As ADOX.Catalog Dim tbl As ADOX.Table Dim con As ADODB.Connection On Error GoTo 0 ' Create the new database. Set cat = New ADOX.Catalog cat.Create _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & DatabaseName & ";" ' Create a new table. Set tbl = New ADOX.Table tbl.Name = "TestTable" tbl.Columns.Append "FirstName", adVarWChar, 40 tbl.Columns.Append "LastName", adVarWChar, 40 tbl.Columns.Append "Birthdate", adDate tbl.Columns.Append "Weight", adInteger cat.Tables.Append tbl
'设置列可以为NULL tb1.columns("Weight").Attributes=AdColNullable '或者tb1.Columns("Weight").Properties("Jet OLEDB:Allow Zero Length") = True
' Connect to the database. Set con = cat.ActiveConnection ' Insert records. con.Execute "INSERT INTO TestTable VALUES ('Andy', 'Able', '1 Jan 1980', '150')" con.Execute "INSERT INTO TestTable VALUES ('Betty', 'Baker', #2/22/1990#, 70)" ' Close the database connection. con.Close Set con = Nothing Set tbl = Nothing Set cat = Nothing -----------------------------------------把execl转成access不用语句的话 你这样,打开Access--》向导中选“空的Accesss数据库”---》创建----》点工具拦新建--》选导入表---》文件类型选Micrsoft Execl文件---》直接点完成
前我們在【問題127】資料庫的匯出 ---- 使用 DAO 提過資料庫的匯出功能,之後,很多網友來信問到如何將 Excel 的資料表中的資料匯入 Access 資料庫,下面我已將程式碼做成模組,只要傳入必要之參數即可!此一模組共有四個參數: 1、sSheetName:要匯出資料的資料表名稱 (Sheet name),例如 Sheet1 2、sExcelPath:要匯出資料的 Excel 檔案路徑名稱 (Workbook path),例如 C:\book1.xls 3、sAccessTable:要匯入的 Access Table 名稱,例如 TestTable 4、sAccessDBPath:要匯入的 Access 檔案路徑名稱,例如 C:\Test.mdb在宣告區中加入以下宣告:Private Sub ExportExcelSheetToAccess(sSheetName As String, _ sExcelPath As String, sAccessTable As String, sAccessDBPath As String) Dim db As Database Dim rs As Recordset Set db = OpenDatabase(sExcelPath, True, False, "Excel 5.0") Call db.Execute("Select * into [;database=" & sAccessDBPath & "]." & _ sAccessTable & " FROM [" & sSheetName & "$]") MsgBox "Table exported successfully.", vbInformation, "Yams" End Sub 使用範例如下:將 C:\book1.xls 中的 Sheet1 匯入 C:\Test.mdb 成為 TestTableExportExcelSheetToAccess "Sheet1", "C:\book1.xls", "TestTable", "C:\Test.mdb"
创建数据库呢,可以用DAO.举例方法如下: If pcRecordset.EOF = False Then pcRecordset.MoveFirst On Error GoTo Line1 '利用DAO的数据库对象,生成库 Set db = DBEngine.CreateDatabase(dasename.Text, dbLangChineseSimplified) Set td = New TableDef
For i = 0 To szcount - 1
Dim fieldstart As Integer Dim fieldname As String '取字段名 fieldstart = InStr(fieldtemp(i), "[") fieldname = left(fieldtemp(i), fieldstart - 1)
Dim typestart As Integer Dim typeend As Integer Dim Typeno As Integer '取字段类型 typestart = InStrRev(fieldtemp(i), "[") typeend = InStrRev(fieldtemp(i), "]") Typeno = Mid(fieldtemp(i), typestart + 1, typeend - typestart - 1)
Dim lenstart As Integer Dim lenend As Integer Dim lenno As Integer '取字段长度 lenstart = InStrRev(fieldtemp(i), "{") lenend = InStrRev(fieldtemp(i), "}") lenno = Mid(fieldtemp(i), lenstart + 1, lenend - lenstart - 1) '生成新的表 If lenno = 255 Then lenno = 12 If Typeno = 130 Or Typeno = 202 Then td.Fields.Append td.CreateField(fieldname, dbText, lenno) If Typeno = 2 Then td.Fields.Append td.CreateField(fieldname, dbInteger) If Typeno = 3 Then td.Fields.Append td.CreateField(fieldname, dbInteger) If Typeno = 4 Then td.Fields.Append td.CreateField(fieldname, dbSingle) If Typeno = 6 Then td.Fields.Append td.CreateField(fieldname, dbInteger) Next '生成新的表 td.name = tablename.Text db.TableDefs.Append td Set db = Nothing
也可以用ADO生成表 是用SQL语句的.create table 表名 字段
Option ExplicitPrivate Sub cmdLoad_Click() Dim excel_app As Object Dim excel_sheet As Object Dim db As Database Dim new_value As String Dim row As Integer Screen.MousePointer = vbHourglass DoEvents ' Create the Excel application. Set excel_app = CreateObject("Excel.Application") ' Uncomment this line to make Excel visible. ' excel_app.Visible = True ' Open the Excel spreadsheet. excel_app.Workbooks.Open FileName:=txtExcelFile.Text ' Check for later versions. If Val(excel_app.Application.Version) >= 8 Then Set excel_sheet = excel_app.ActiveSheet Else Set excel_sheet = excel_app End If ' Open the Access database. Set db = OpenDatabase(txtAccessFile.Text) ' Get data from the Excel spreadsheet and insert ' it into the TestValues table. row = 1 Do ' Get the next value. new_value = Trim$(excel_sheet.Cells(row, 1)) ' See if it's blank. If Len(new_value) = 0 Then Exit Do ' Insert the value into the database. db.Execute "INSERT INTO TestValues VALUES (" & _ new_value & ")" row = row + 1 Loop ' Close the database. db.Close Set db = Nothing ' Comment the rest of the lines to keep ' Excel running so you can see it. ' Close the workbook without saving. excel_app.ActiveWorkbook.Close False ' Close Excel. excel_app.Quit Set excel_sheet = Nothing Set excel_app = Nothing Screen.MousePointer = vbDefault MsgBox "Copied " & Format$(row - 1) & " values." End Sub' Note that this project contains a reference to ' Microsoft DAO 3.51 Object Library. Private Sub Form_Load() Dim file_path As String file_path = App.Path If Right$(file_path, 1) <> "\" Then file_path = file_path & "\" txtExcelFile.Text = file_path & "XlsToMdb.xls" txtAccessFile.Text = file_path & "XlsToMdb.mdb" End Sub
'引用Microsoft ADO Ext 2.5 for DDL and Security
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim con As ADODB.Connection On Error GoTo 0 ' Create the new database.
Set cat = New ADOX.Catalog
cat.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DatabaseName & ";" ' Create a new table.
Set tbl = New ADOX.Table
tbl.Name = "TestTable"
tbl.Columns.Append "FirstName", adVarWChar, 40
tbl.Columns.Append "LastName", adVarWChar, 40
tbl.Columns.Append "Birthdate", adDate
tbl.Columns.Append "Weight", adInteger
cat.Tables.Append tbl
'设置列可以为NULL
tb1.columns("Weight").Attributes=AdColNullable
'或者tb1.Columns("Weight").Properties("Jet OLEDB:Allow Zero Length") = True
' Connect to the database.
Set con = cat.ActiveConnection ' Insert records.
con.Execute "INSERT INTO TestTable VALUES ('Andy', 'Able', '1 Jan 1980', '150')"
con.Execute "INSERT INTO TestTable VALUES ('Betty', 'Baker', #2/22/1990#, 70)" ' Close the database connection.
con.Close
Set con = Nothing
Set tbl = Nothing
Set cat = Nothing
-----------------------------------------把execl转成access不用语句的话
你这样,打开Access--》向导中选“空的Accesss数据库”---》创建----》点工具拦新建--》选导入表---》文件类型选Micrsoft Execl文件---》直接点完成
1、sSheetName:要匯出資料的資料表名稱 (Sheet name),例如 Sheet1
2、sExcelPath:要匯出資料的 Excel 檔案路徑名稱 (Workbook path),例如 C:\book1.xls
3、sAccessTable:要匯入的 Access Table 名稱,例如 TestTable
4、sAccessDBPath:要匯入的 Access 檔案路徑名稱,例如 C:\Test.mdb在宣告區中加入以下宣告:Private Sub ExportExcelSheetToAccess(sSheetName As String, _
sExcelPath As String, sAccessTable As String, sAccessDBPath As String)
Dim db As Database
Dim rs As Recordset
Set db = OpenDatabase(sExcelPath, True, False, "Excel 5.0")
Call db.Execute("Select * into [;database=" & sAccessDBPath & "]." & _
sAccessTable & " FROM [" & sSheetName & "$]")
MsgBox "Table exported successfully.", vbInformation, "Yams"
End Sub
使用範例如下:將 C:\book1.xls 中的 Sheet1 匯入 C:\Test.mdb 成為 TestTableExportExcelSheetToAccess "Sheet1", "C:\book1.xls", "TestTable", "C:\Test.mdb"
If pcRecordset.EOF = False Then
pcRecordset.MoveFirst
On Error GoTo Line1
'利用DAO的数据库对象,生成库
Set db = DBEngine.CreateDatabase(dasename.Text, dbLangChineseSimplified)
Set td = New TableDef
For i = 0 To szcount - 1
Dim fieldstart As Integer
Dim fieldname As String
'取字段名
fieldstart = InStr(fieldtemp(i), "[")
fieldname = left(fieldtemp(i), fieldstart - 1)
Dim typestart As Integer
Dim typeend As Integer
Dim Typeno As Integer
'取字段类型
typestart = InStrRev(fieldtemp(i), "[")
typeend = InStrRev(fieldtemp(i), "]")
Typeno = Mid(fieldtemp(i), typestart + 1, typeend - typestart - 1)
Dim lenstart As Integer
Dim lenend As Integer
Dim lenno As Integer
'取字段长度
lenstart = InStrRev(fieldtemp(i), "{")
lenend = InStrRev(fieldtemp(i), "}")
lenno = Mid(fieldtemp(i), lenstart + 1, lenend - lenstart - 1)
'生成新的表
If lenno = 255 Then lenno = 12
If Typeno = 130 Or Typeno = 202 Then td.Fields.Append td.CreateField(fieldname, dbText, lenno)
If Typeno = 2 Then td.Fields.Append td.CreateField(fieldname, dbInteger)
If Typeno = 3 Then td.Fields.Append td.CreateField(fieldname, dbInteger)
If Typeno = 4 Then td.Fields.Append td.CreateField(fieldname, dbSingle)
If Typeno = 6 Then td.Fields.Append td.CreateField(fieldname, dbInteger)
Next
'生成新的表
td.name = tablename.Text
db.TableDefs.Append td
Set db = Nothing
是用SQL语句的.create table 表名 字段
Dim excel_app As Object
Dim excel_sheet As Object
Dim db As Database
Dim new_value As String
Dim row As Integer Screen.MousePointer = vbHourglass
DoEvents ' Create the Excel application.
Set excel_app = CreateObject("Excel.Application") ' Uncomment this line to make Excel visible.
' excel_app.Visible = True ' Open the Excel spreadsheet.
excel_app.Workbooks.Open FileName:=txtExcelFile.Text ' Check for later versions.
If Val(excel_app.Application.Version) >= 8 Then
Set excel_sheet = excel_app.ActiveSheet
Else
Set excel_sheet = excel_app
End If ' Open the Access database.
Set db = OpenDatabase(txtAccessFile.Text) ' Get data from the Excel spreadsheet and insert
' it into the TestValues table.
row = 1
Do
' Get the next value.
new_value = Trim$(excel_sheet.Cells(row, 1)) ' See if it's blank.
If Len(new_value) = 0 Then Exit Do ' Insert the value into the database.
db.Execute "INSERT INTO TestValues VALUES (" & _
new_value & ")" row = row + 1
Loop ' Close the database.
db.Close
Set db = Nothing ' Comment the rest of the lines to keep
' Excel running so you can see it. ' Close the workbook without saving.
excel_app.ActiveWorkbook.Close False ' Close Excel.
excel_app.Quit
Set excel_sheet = Nothing
Set excel_app = Nothing Screen.MousePointer = vbDefault
MsgBox "Copied " & Format$(row - 1) & " values."
End Sub' Note that this project contains a reference to
' Microsoft DAO 3.51 Object Library.
Private Sub Form_Load()
Dim file_path As String file_path = App.Path
If Right$(file_path, 1) <> "\" Then file_path = file_path & "\"
txtExcelFile.Text = file_path & "XlsToMdb.xls"
txtAccessFile.Text = file_path & "XlsToMdb.mdb"
End Sub