Excel 通过 SQL 服务器访问文件数据传输

2024-9-1 • tag-icon

我在通过 SQL Server 将数据从 MS Excel 传输到 MS Access 时遇到问题

这是我的程序

 Public Sub DoTrans()
DoEvents
Sheets("Temp").Select
  Set cn = CreateObject("ADODB.Connection")
  'dbPath = Application.ActiveWorkbook.Path & "\Database2021.mdb"
  dbPath = "\\datasource\Temp\Database2021.mdb"
 dbWb = Application.ActiveWorkbook.FullName
 dbWb = Application.ActiveWorkbook.FullName
  dbWs = Application.ActiveSheet.Name
  scn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath
  dsh = "[" & Application.ActiveSheet.Name & "$]"
  cn.Open scn

  ssql = "INSERT INTO Table1 ([DataEnteredOn],[Date],[DATA],[EstimatedHours],[ActualHours],[Eff%],[Remarks],[ESR#] ) "
  'ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh

  ssql = ssql & "SELECT [DataEnteredOn],[Date],[DATA],[EstimatedHours],[ActualHours],[Eff%],[Remarks],[ESR#]  FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh
  cn.Execute ssql
  cn.Close
  
  Sheets("Temp").Select
    
   MsgBox "Your datas saved. If you want to check, Please click ""Complete Database"" button"

Call CLEARDATA

End Sub

输入是0.117

但产出0.110

我需要修复数据转换。

参照下面的截图，我尝试调整小数位。但是没有用

答案1

比例和小数位必须相等，那么我们的输入和输出才相同。

答案1

相关内容