EXCEL VBA SQL 將資料 匯出 到dBASEIII .dbf檔案
PS. : Properties='dBASE III;HDR=NO;IMEX=2' 兩邊一定要加上 單引號 , 否則連接失敗
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sqls As String
''20210410
'On Error Resume Next
On Error GoTo ErrorHandler
If cnn.State = adStateOpen Then cnn.Close
If rst.State = 1 Then rst.Close
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='dBASE III;HDR=NO;IMEX=2';Data Source=" & "D:\AICUSTOMS-應收費用ACT帳單檔"
If cnn.State = adStateOpen Then
sqls = ""
sqls = "select MAWB, HAWB from[ACCINOPM$] where MAWB = '" & MAWB_NO & "' AND HAWB = '" & HAWB_NO & "'"
sqls = sqls + " AND INVOICENO = '" & M_INVOICENO & "' AND DBISSUE = 'Y'"
rst.Open sqls, cnn, 1, 1
If Not rst Is Nothing Then
''新增
sqls = ""
sqls = "insert into [ACCINOPM$] (MAWB, HAWB, ACCNO,APPDATE, ACCDATE, DPT, INVOICENO)"
sqls = sqls + " values ('" & MAWB_NO & "','" & HAWB_NO & "','" & M_ACCNO & "'"
sqls = sqls + ",'" & M_APPDATE & "','" & M_DATE & "','" & M_DPT & "','" & M_INVOICENO & "')"
cnn.Execute sqls
End If
Else
MsgBox "Connection was not created!", vbCritical, "Connection error"
End If
If cnn.State = adStateOpen Then cnn.Close
Set cnn = Nothing
If rst.State = 1 Then rst.Close
Set rst = Nothing