嗨,我对VBA不太熟悉,希望有人能帮我完成最后一部分代码。
我正在尝试将电子表格中的单元格添加到SQL表中,但是在运行SQL语句时遇到了问题。以下是我目前的代码。
Private Sub ConnectDB()
Dim oConn As Object
Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "DRIVER={SQL Server};" & _
"SERVER=SERVER02;" & _
"DATABASE=platform;" & _
"USER=5y5t3mus3r;" & _
"PASSWORD=*******;" & _
"Option=3;"
If oConn.State = adStateOpen Then
MsgBox "Welcome to Database!"
Else
MsgBox "Sorry No Database Access."
End If
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim Company As String
Dim Address As String
Dim Address1 As String
Dim Address2 As String
Dim County As String
Dim Contact As String
Dim Phone As String
Dim Fax As String
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Sheets("wsheet").Activate
Set rs = New ADODB.Recordset
rs.Source = Sql
With wsheet
MyFile = "C:\Users\Ryan.ICS\Documents\Documents\InsertStatement.txt"
fnum = FreeFile()
Open MyFile For Output As fnum
myRow = 2
myCol = 4
For myRow = 2 To InputBox(Prompt:="What is the last row of data?", Title:="Data Row", Default:=1)
myCol = 4
Company = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
Address = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
Address1 = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
Address2 = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
Address3 = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 2
Phone = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
Fax = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
strSQL = "INSERT INTO [sandbox].[5y5t3mus3r].[ryan] (Organisation, Address1, Address2, TownCity, County, Telephone, Fax) VALUES('" & Company & "', '" & Address & "', '" & Address1 & "', '" & Address2 & "', '" & Address3 & "', " & Phone & ", " & Fax & ");"
Print #fnum, strSQL
DoCmd.RunSQL strSQL ***Here is where I am haveing an error it will not run the SQL command.****
oConn.Execute strSQL **** here is another tag I tried in a number of different ways but i still couldnt get the SQL statement to run
Next
End With
' Find out how many rows were affected by the Insert.
Set rs = oConn.Execute("Select @@rowcount")
' Display the first field in the recordset.
MsgBox rs(0) & " rows inserted"
oConn.Close
Set rs = Nothing
Set oConn = Nothing
Close #fnum
End Sub
Function esc(txt As String)
esc = Trim(Replace(txt, "'", "\'"))
End Function
当我尝试运行SQL语句时,出现了错误,我需要创建一个对象或方法来解决这个问题吗?
非常感谢您的帮助!
DoCmd
的吗?这是 Access 对象,不是 Excel 对象。 - Olivier Jacot-Descombesdebug.print strSQL
进行调试输出。然后将它输出的字符串复制到立即窗口中。接着将查询语句粘贴到您喜欢的SQL工具中并运行它。我认为最好的方法是先获得一个可用的INSERT INTO,然后修改您的VBA代码以匹配它。希望这能帮到您。 - jon;
应该会导致错误,但不应该是424 对象所需
错误。你的连接语法和查询执行语法在我看来都很好。 - Pynner