|
各位大侠:
参考坛子上的帖子,自己弄了一个程序,把excel数据导入到access数据库里:
运行过程中错误提示如图所示:
跟踪SQL变量的值,如下:
"select * from CSSR where date=#2011-07-07# and BSC='Bsc70_Zhmurovo' and sector_name='Zhm_Test-BTS_0' and lac='13803' and CI='5' and time=00:00"
这个SQL语句好像也没有什么语法错误,也没有缺少"operator".
请大家帮帮看看!
程序如下:
Public Sub data_input(str_filetype As String)
Dim myData As String, myTable As String
Dim wb As Workbook
Dim ws As Worksheet
Dim i As Long, j As Long
' Dim myCmd As ADODB.Command
Dim cnn As Object 'ADODB.Connection
Dim rs As Object 'ADODB.Recordset
Set wb = ThisWorkbook
Set ws = ActiveWorkbook.Sheets("sheet1")
myData = wb.Path & "\" & "bwc.mdb"
myTable = str_filetype
'Set cnn = New object 'ADODB.
Set cnn = CreateObject("adodb.connection")
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & myData
' With cnn
' .Provider = "microsoft.jet.oledb.4.0"
' .Open myData
' End With
N = Range("a65535").End(xlUp).Row
For i = 11 To N
If myTable = "Rx_qual" Then
SQL = "select * from " & myTable _
& " where date=#" & Format(ws.Cells(i, 6).Value, "yyyy-mm-dd") & "#" _
& " and BSC='" & ws.Cells(i, 1).Value & "'" _
& " and sector_name='" & ws.Cells(i, 2).Value & "'" _
& " and lac='" & ws.Cells(i, 3).Value & "'" _
& " and CI='" & ws.Cells(i, 4).Value & "'" _
& " and time=" & Format(ws.Cells(i, 7).Value, "hh:mm") _
& " and trx='" & ws.Cells(i, 5).Value & "'"
Else
SQL = "select * from " & myTable _
& " where date=#" & Format(ws.Cells(i, 5).Value, "yyyy-mm-dd") & "#" _
& " and BSC='" & ws.Cells(i, 1).Value & "'" _
& " and sector_name='" & ws.Cells(i, 2).Value & "'" _
& " and lac='" & ws.Cells(i, 3).Value & "'" _
& " and CI='" & ws.Cells(i, 4).Value & "'" _
& " and time=" & Format(ws.Cells(i, 6).Value, "hh:mm") _
End If
Set rs = CreateObject("ADODB.Recordset")
rs.Open SQL, cnn, 1, 3
If rs.RecordCount = 0 Then
rs.AddNew
For j = 2 To rs.Fields.Count
rs.Fields(j - 1) = ws.Cells(i, j).Value
Next j
rs.Update
Else
For j = 2 To rs.Fields.Count
rs.Fields(j - 1) = ws.Cells(i, j).Value
Next j
rs.Update
End If
Next i
MsgBox "ハセンア」エ賚・マ」。", vbInformation + vbOKOnly
ws.Range("A2:O" & N).ClearContents
rs.Close
cnn.Close
Set wb = Nothing
Set ws = Nothing
Set rs = Nothing
Set myCmd = Nothing
Set cnn = Nothing
End Sub |
|