编程论坛 » VB6论坛 » excel連接access中的ado語句！

xunmi_love 发表于 2008-1-9 19:11

excel連接access中的ado語句！

[color=darkorange]Private Sub CommandButton1_Click()[/color]

[font=新細明體][color=#000000][font=Times New Roman]
Dim [color=red]DB1 As Database    ((使用都自定義形態尚未定義！))[/color][/font][/color][/font]
[font=新細明體][color=#000000][font=Times New Roman]
Dim QRY1, myQRY As QueryDef[/font][/color][/font]
[font=新細明體][color=#000000][font=Times New Roman]
Dim QuerySting As String[/font][/color][/font]
[font=新細明體][color=#000000][font=Times New Roman]
Dim RS1 As Recordset[/font][/color][/font]
[font=新細明體][color=#000000][font=Times New Roman]
Dim isExist As Boolean[/font][/color][/font]
[font=Times New Roman][color=#000000]    [/color][/font]
[font=新細明體][font=Times New Roman][color=#000000]'
On Error GoTo ErrorHandler[/color][/font][/font]
[font=Times New Roman][color=#000000]    [/color][/font]
[color=#000000][font=新細明體][font=Times New Roman]
myPrjName = InputBox("[/font][/font][font=新細明體]請輸入工程名稱：[/font][font=新細明體][font=Times New Roman]", "[/font][/font][font=新細明體]條件[/font][font=新細明體][font=Times New Roman]", "[/font][/font][font=新細明體]桂芳園五期[/font][font=Times New Roman][font=新細明體]")[/font][/font][/color]
[color=#000000][font=新細明體][font=Times New Roman]
myPlanName = InputBox("[/font][/font][font=新細明體]請輸入計畫單號：[/font][font=新細明體][font=Times New Roman]", "[/font][/font][font=新細明體]條件[/font][font=Times New Roman][font=新細明體]", "101102")[/font][/font][/color]
[color=#000000][font=Times New Roman]
[/font][/color]
[font=新細明體][color=#000000][font=Times New Roman]
If myPrjName = "" Then[/font][/color][/font]
[font=新細明體][color=#000000][font=Times New Roman]
If myPlanName = "" Then[/font][/color][/font]
[color=#000000][font=新細明體][font=Times New Roman]
MsgBox "[/font][/font][font=新細明體]未選擇任何條件！[/font][font=新細明體][font=Times New Roman]", vbOKOnly, "[/font][/font][font=新細明體]查詢退出[/font][font=Times New Roman][font=新細明體]"[/font][/font][/color]
[color=#000000][font=Times New Roman][font=新細明體]
Exit Sub[/font][/font][/color]
[color=#000000][font=Times New Roman][font=新細明體]
Else[/font][/font][/color]
[color=#000000][font=新細明體][font=Times New Roman]
QueryString = "SELECT * FROM XC_Plan WHERE
[/font][/font][font=新細明體]計畫單號[/font][font=Times New Roman][font=新細明體] =" _[/font][/font][/color]
[color=#000000][font=新細明體][font=Times New Roman]
& Chr(34) & myPlanName & Chr(34)
'chr(34) [/font][/font][font=新細明體]即為[/font][font=新細明體][font=Times New Roman] " [/font][/font][font=新細明體]號[/font][/color]
[font=新細明體][color=#000000][font=Times New Roman]
End If[/font][/color][/font]
[font=新細明體][color=#000000][font=Times New Roman]
Else[/font][/color][/font]
[font=新細明體][color=#000000][font=Times New Roman]
If myPlanName = "" Then[/font][/color][/font]
[color=#000000][font=新細明體][font=Times New Roman]
QueryString = "SELECT * FROM XC_Plan WHERE [/font][/font][font=新細明體]工程名稱[/font][font=Times New Roman][font=新細明體]=" _[/font][/font][/color]
[color=#000000][font=Times New Roman][font=新細明體]
& Chr(34) & myPrjName & Chr(34)[/font][/font][/color]
[color=#000000][font=Times New Roman][font=新細明體]
Else[/font][/font][/color]
[color=#000000][font=新細明體][font=Times New Roman]
QueryString = "SELECT * FROM XC_Plan WHERE [/font][/font][font=新細明體]工程名稱[/font][font=Times New Roman][font=新細明體]=" _[/font][/font][/color]
[color=#000000][font=Times New Roman][font=新細明體]
& Chr(34) & myPrjName & Chr(34) & " AND" _[/font][/font][/color]
[color=#000000][font=新細明體][font=Times New Roman]
& " [/font][/font][font=新細明體]計畫單號[/font][font=Times New Roman][font=新細明體] = " & Chr(34) & myPlanName & Chr(34)[/font][/font][/color]
[font=新細明體][color=#000000][font=Times New Roman]
End If[/font][/color][/font]
[font=新細明體][color=#000000][font=Times New Roman]
End If[/font][/color][/font]
[font=Times New Roman][color=#000000]        [/color][/font]
[font=Times New Roman][color=#000000]    [/color][/font]
[font=Times New Roman][color=#000000]    [/color][/font]
[font=新細明體][color=#000000][font=Times New Roman]
Set DB1 = OpenDatabase(ThisWorkbook.Path & "\UserDB" & "\PLAN.MDB")[/font][/color][/font]
[font=Times New Roman][color=#000000]    [/color][/font]
[color=#000000][font=新細明體][font=Times New Roman]
'[/font][/font][font=新細明體]下面檢查[/font][font=新細明體][font=Times New Roman]  [/font][/font][font=新細明體]查詢[/font][font=新細明體][font=Times New Roman]temp [/font][/font][font=新細明體]是否存在[/font][/color]
[font=新細明體][color=#000000][font=Times New Roman]
For Each myQRY In DB1.QueryDefs[/font][/color][/font]
[color=#000000][font=新細明體][font=Times New Roman]
If myQRY.Name = "[/font][/font][font=新細明體]查詢[/font][font=Times New Roman][font=新細明體]temp" Then[/font][/font][/color]
[color=#000000][font=Times New Roman][font=新細明體]
isExist = True[/font][/font][/color]
[color=#000000][font=Times New Roman][font=新細明體]
End If[/font][/font][/color]
[color=#000000][font=Times New Roman][font=新細明體]
Next[/font][/font][/color]
[color=#000000][font=新細明體][font=Times New Roman]
If Not isExist Then
'[/font][/font][font=新細明體]不存在則建立之[/font][/color]
[color=#000000][font=新細明體][font=Times New Roman]
Set QRY1 = DB1.CreateQueryDef(Name:="[/font][/font][font=新細明體]查詢[/font][font=Times New Roman][font=新細明體]temp", sqltext:=QueryString)[/font][/font][/color]
[color=#000000][font=新細明體][font=Times New Roman]
Else
'[/font][/font][font=新細明體]存在，則設置查詢條件[/font][/color]
[color=#000000][font=新細明體][font=Times New Roman]
Set QRY1 = DB1.QueryDefs("[/font][/font][font=新細明體]查詢[/font][font=Times New Roman][font=新細明體]temp")[/font][/font][/color]
[font=新細明體][color=#000000][font=Times New Roman]
QRY1.Sql = QueryString[/font][/color][/font]
[font=新細明體][color=#000000][font=Times New Roman]
End If[/font][/color][/font]
[font=Times New Roman][color=#000000]    [/color][/font]
[font=新細明體][color=#000000][font=Times New Roman]
Set RS1 = QRY1.OpenRecordset(dbOpenDynaset)[/font][/color][/font]
[font=Times New Roman][color=#000000]    [/color][/font]
[font=Times New Roman][color=#000000][/color][/font]
[color=#000000][font=Times New Roman][font=新細明體]
With Worksheets("plan")[/font][/font][/color]
[color=#000000][font=Times New Roman]
[/font][/color]
[color=#000000][font=新細明體][font=Times New Roman]
'[/font][/font][font=新細明體]下麵[/font][font=新細明體][font=Times New Roman]For--Next[/font][/font][font=新細明體]複製欄位字到第一行[/font][/color]
[font=新細明體][color=#000000][font=Times New Roman]
For iCols = 0 To RS1.Fields.Count - 1[/font][/color][/font]
[font=新細明體][color=#000000][font=Times New Roman]
.Cells(1, iCols + 1).Value = RS1.Fields(iCols).Name[/font][/color][/font]
[font=新細明體][color=#000000][font=Times New Roman]
Next[/font][/color][/font]
[font=新細明體][color=#000000][font=Times New Roman]
.Range("2:10000").ClearContents[/font][/color][/font]
[color=#000000][font=新細明體][font=Times New Roman]

.Range("A2").CopyFromRecordset RS1
'[/font][/font][font=新細明體]將記錄集[/font][font=新細明體][font=Times New Roman] RS1 [/font][/font][font=新細明體]的記錄全部複製到工作表[/font][/color]
[font=新細明體][color=#000000][font=Times New Roman]
.Select[/font][/color][/font]
[font=新細明體][color=#000000][font=Times New Roman]
End With[/font][/color][/font]
[font=Times New Roman][color=#000000]    [/color][/font]
[font=Times New Roman][color=#000000]    [/color][/font]
[font=Times New Roman][color=#000000]   [/color][/font]
[font=新細明體][color=#000000][font=Times New Roman]
DB1.Close[/font][/color][/font]
[font=Times New Roman][color=#000000][/color][/font]
[font=新細明體][font=Times New Roman][color=#000000]End Sub[/color][/font][/font]


請幫忙改一下

注：這個是我從別人那裡抄來的！放在原文檔裡面另一個sheet裡面去執行，就OK，新建一個XLS就出現上面的錯誤。
我把能想到的方法都試過了！頭都大了！
就是沒有找到這是怎麼回事！。。。。。。

[[italic] 本帖最后由 xunmi_love 于 2008-1-10 11:13 编辑 [/italic]]

zhouzhoulove 发表于 2008-6-30 15:44

引用关于数据库的东东就可以了

查看完整版本: excel連接access中的ado語句！