a********t
发帖数: 1810 | 1 两张sheets,一个名子是wageclean,一个是shift.我在shift上create一个VBA,错误是:
run-time error'-2147217904(80040e10)'
Automation Error
code:
Sub Shift()
Dim mySQL As String, i As Long
Dim mycnn As ADODB.Connection
Dim rs As New ADODB.Recordset
Application.ScreenUpdating = False
myPath = ThisWorkbook.Path
'*******************connection to ODBC
Range("A1:F65536").ClearContents
Set mycnn = CreateObject("adodb.connection")
mycnn.Open ("provider=microsoft.jet.oledb.4.0;extended properties=excel
8.0;data source=" & ThisWorkbook.FullName)
'***********************SQL query
mySQL = "SELECT [wageclean$].providerID,[wageclean$].employerid, [wageclean$
].payrate, [wage$].Totalhours, [wage$].totalpay FROM [wageclean$] WHERE (
select count(*) from [wageclean$] as f where f.employerid = [wageclean$].
employerid and f.providerID=[wageclean$].providerID and f.payrate < 10)"
'***************************ExectuteSQL
rs.Open mySQL, mycnn, adOpenKeyset, adLockOptimistic
'****************************loop and copy title
For i = 0 To rs.Fields.Count - 1
Cells(1, i + 1) = rs.Fields(i).Name
Next
'************************paste on sheet
Dim xxx As Integer
xxx = Range("shift!a65536").End(xlUp).Row + 1
Range("shift!a" & xxx).CopyFromRecordset rs
'**************************SQL process over
rs.Close
Set rs = Nothing
mycnn.Close
Set mycnn = Nothing
'***************************************************************
Application.ScreenUpdating = True
End Sub
VBA里的SQL不能做嵌套循环? ACCESS 里能出来 | r**********d
发帖数: 510 | 2 where clause is obviously wrong. but I am not sure what you are doing there
. |
|
