sql中的表導入到access

[dongfang2003]

sql中存在兩個表t1,t2,這兩個表都有記錄,現在我想把sql中的兩個表用vb編程導入到access中去,我想在導入t1,t2表之前,用vb編程自動生成一個空白的access數據庫(db1.mdb),假設它位於c:\下,如果c盤根目錄存在這個數據庫,就覆蓋它,然後將sql的t1,t2表(表中有記錄)導入到剛創建的db1.mdb中,有哪位大俠給我答復,最好詳細點,carfield2003仁兄的貼子我看了,而且在机子上運行過,不知是何原因,不能運行,其中的opendatasource,openrowset函數机子似乎不認,能不能說得詳細點

[dongfang2003]

備份時出現[microsoft][ODBC SQL SERVER DRIVERR]逾時中止,是什么原因引起的

[liufeng7600]

这样。
以下全部代码实现。

首先用 dao 创建 access 数据库（抱歉，本人不会用ado建立access数据库）。

之后，手工再sql2000 中导出数据，在最后一步时 时间 选择 立即运行（默认），
保存 选择 保存 DTS 包 同时选上 visual basic 文件形式。

此时不但 到出了数据，也把导出的手工形式存成了代码形式。即生成的.bas 文件

如果用程序的话。只要把 .bas 中的代码（就是上个哥们最繁琐的地方）拷贝到你的commond的 click 事件中。

说的繁琐了。
一句话。
用sql200 的导入导出工具生成代码，将代码 拷贝到 你的commond的 click 事件中。就 O k
了。
我做的一个连锁软件6,7 个表就是这样的。

[dongfang2003]

sql server7.0有無此功能,liufeng7600大俠,如有怎么做

[道素]

如果是在本地创建数据库，可以参考下面代码：
创建数据库用ADOX
如：
'Dim oCat As ADOX.Catalog
'Dim oTable As ADOX.Table
'Dim oColumn As ADOX.Column
'Dim oKey As ADOX.Key

'Set oCat = New ADOX.Catalog
'oCat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\new35.mdb;" & _
"Jet OLEDB:Engine Type=4;"

' Create a new Table
'Set oTable = New ADOX.Table
'oTable.Name = "WebSite"
'oCat.Tables.Append oTable
下面是一个比较完整例子，你要创建你说需要的东西即可，然后用insert into等语句
插入要保存的记录


Private Sub cmdADOCreate_Click()
'As we are using a Graphical Style Checkbox to simulate a Command
button:
If ChangeCmdProperties(cmdADOCreate) Then
Exit Sub
End If


Dim catADO As ADOX.Catalog
Dim tblADO As ADOX.Table
Dim ix As ADOX.Index
Dim colNew As ADOX.Column
Set catADO = New ADOX.Catalog
Set tblADO = New ADOX.Table
Dim cnnNew As New ADODB.Connection
Dim rstNew As New ADODB.Recordset
Dim rstNew2 As New ADODB.Recordset
Dim fAccess97 As Boolean
Dim cTimer As clsTimer
Dim tl As Long

'check if already present
If Dir$(App.Path & "\TestDAOvsADO.mdb") <> "" Then
If MsgBox(App.Path & "\TestDAOvsADO.mdb" & " already exists.
Overwrite?", vbQuestion + vbYesNo) = vbNo Then
Exit Sub
Else
Kill (App.Path & "\TestDAOvsADO.mdb")
End If
End If

'find which version the user wants to make the database
If MsgBox("Do you want to create the database in Access 97 Format?" &
vbCrLf & "NOTE ADO using Jet version 3.51 will NOT allow setting up
Autonumber fields!", vbQuestion + vbYesNo) = vbYes Then
fAccess97 = True
End If

Set cTimer = New clsTimer
cTimer.Reset
' create the db
If fAccess97 Then
catADO.Create "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" &
App.Path & "\TestDAOvsADO.mdb"
Else
catADO.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
App.Path & "\TestDAOvsADO.mdb"
End If

tblADO.Name = "TestTable"
' Add new Table to the Tables collection of the database.
catADO.Tables.Append tblADO
With tblADO
' Create fields and append them to the
' Columns collection of the new Table object.
Set colNew = New ADOX.Column
With colNew
.Name = "ID"
.Type = adInteger
'refer to parent catalog to allow autonumber to be set up
Set .ParentCatalog = catADO
If Not fAccess97 Then
'not supported for Access 97 databases - use DAO...
'Make it Autonumber
.Properties("AutoIncrement") = True
End If
.SortOrder = adSortAscending
End With
.Columns.Append colNew

Set colNew = Nothing
Set colNew = New ADOX.Column
With colNew
.Name = "Surname"
.DefinedSize = 255
If fAccess97 Then
.Type = adVarChar
Else
.Type = adVarWChar
End If
.Attributes = adColNullable
.SortOrder = adSortAscending
End With
.Columns.Append colNew
Set colNew = Nothing
Set colNew = New ADOX.Column
With colNew
.Name = "FirstName"
.DefinedSize = 255
If fAccess97 Then
.Type = adVarChar
Else
.Type = adVarWChar
End If
.Attributes = adColNullable
.SortOrder = adSortAscending
End With
.Columns.Append colNew
Set colNew = New ADOX.Column
With colNew
.Name = "PurchaseID"
.Type = adInteger
.Attributes = adColNullable
.SortOrder = adSortAscending
End With
.Columns.Append colNew

Set ix = New ADOX.Index
With ix
.PrimaryKey = True
.Unique = True
.IndexNulls = adIndexNullsDisallow
.Name = "ID"
.Columns.Append "ID"
End With
.Indexes.Append ix

Set ix = New ADOX.Index
With ix
.PrimaryKey = False
.Unique = True
.IndexNulls = adIndexNullsDisallow
.Name = "Name"
.Columns.Append "Surname"
.Columns.Append "FirstName"
End With
.Indexes.Append ix
End With


If Not fAccess97 Then
'If you want a different interval than 1 for the autonumbers then
set the next line to desired value
tblADO.Columns("ID").Properties("Increment") = 1
End If
Set tblADO = Nothing

'now create a second table
Set tblADO = New ADOX.Table
With tblADO
.Name = "TestTable2"
' Create fields and append them to the
' Columns collection of the new Table object.
With .Columns
.Append "ID", adInteger
.Append "Cost", adCurrency
End With

Set colNew = New ADOX.Column
With colNew
.Name = "Item"
.DefinedSize = 255
If fAccess97 Then
.Type = adVarChar
Else
.Type = adVarWChar
End If
.Attributes = adColNullable
.SortOrder = adSortAscending
End With
.Columns.Append colNew
.Columns("Item").ParentCatalog = catADO
Set ix = New ADOX.Index
With ix
.PrimaryKey = True
.Unique = True
.IndexNulls = adIndexNullsDisallow
.Name = "ID"
.Columns.Append "ID"
End With
.Indexes.Append ix
End With

' Add the new Table to the Tables collection of the database.
catADO.Tables.Append tblADO
If Not fAccess97 Then
tblADO.Columns("Item").Properties("Jet OLEDB:Allow Zero Length") =
True
End If
catADO.ActiveConnection = Nothing
Set tblADO = Nothing
Set catADO = Nothing

'now add some data

cnnNew.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path &
"\TestDAOvsADO.mdb"
rstNew.Open "TestTable", cnnNew, adOpenKeyset, adLockOptimistic,
adCmdTableDirect
rstNew2.Open "TestTable2", cnnNew, adOpenKeyset, adLockOptimistic,
adCmdTableDirect

rstNew.AddNew
If fAccess97 Then 'no autonumber, so create your own number
rstNew!ID = 1
End If
rstNew!Surname = "Smith"
rstNew!FirstName = "John"
rstNew!PurchaseID = 1
rstNew.Update

rstNew.AddNew
If fAccess97 Then
rstNew!ID = 2
End If
rstNew!Surname = "Jones"
rstNew!FirstName = "Harry"
rstNew!PurchaseID = 2
rstNew.Update

rstNew.AddNew
If fAccess97 Then
rstNew!ID = 3
End If
rstNew!Surname = "Smith"
rstNew!FirstName = "Fred"
rstNew!PurchaseID = 3
rstNew.Update


rstNew2.AddNew
rstNew2!ID = 1
rstNew2!Item = "Apples"
rstNew2!Cost = 3.5
rstNew2.Update

rstNew2.AddNew
rstNew2!ID = 2
rstNew2!Item = "Oranges"
rstNew2!Cost = 4.4
rstNew2.Update

rstNew2.AddNew
rstNew2!ID = 3
rstNew2!Item = "Pears"
rstNew2!Cost = 2.1
rstNew2.Update

rstNew.Close
rstNew2.Close
Set catADO = Nothing
cnnNew.Close
Set cnnNew = Nothing
tl = cTimer.Interval
Me.Caption = "Time taken " & tl & " ms"
Set cTimer = Nothing
MsgBox "Database created"
End Sub

[dongfang2003]

太復雜,能不能有簡單一點的

[yijiansong]

UP