如何使用VBA将Excel中的数据列插入Access数据库的字段中

ycl3bljg 于 11个月前发布在其他

关注(0)|答案(2)|浏览(104)

我试图在Excel中编写一个JavaScript代码，将Excel工作表的列插入Access数据库的字段中。我试图将列“NDC11”，“ProductNameLong”和“ProductName2”插入Access数据库的字段中，这些字段分别名为“NDC11”，“ProductNameLong”和“ProductName2”。这是我到目前为止的代码。

Sub DataPuller()

Dim refresh
Dim year As String
Dim intColIndex As Integer
Dim strPath As String
Dim strProv As String
Dim strConn As String
Dim Conn As New Connection
Dim rsQry As New Recordset
Dim strQry As String
Dim NDCs As Range
Dim ProductName2s As Range

refresh = MsgBox("Start New Query?", vbYesNo)

If refresh = vbYes Then
    year = Application.InputBox("Which year would you like CMS Utilization Data from:" & vbNewLine & "2019, 2020, 2021, or 2022?", "Input Year")
    If year = "2022" Then
        On Error GoTo Whoa
        strPath = "C:\Users\hgarrison\Documents\PRM DOCS\1Q2022 - 4Q2022 CMS Utilization Database.accdb"
        strProv = "Microsoft.ACE.OLEDB.12.0;"
        strConn = "Provider=" & strProv & "Data Source=" & strPath
        Conn.Open strConn
        strQry = "DELETE * from MB_without_financials"
        Conn.Execute strQry
        Sheets.Add.Name = "Data"
        
        xlFilepath = Application.ThisWorkbook.FullName
        strQry = "INSERT INTO MB_without_financials(NDC11, ProductNameLong, ProductName2) SELECT NDC11, ProductNameLong, ProductName2 FROM [Excel 12.0 xml;HDR=YES;DATABASE=" & xlFilepath & "].[Sheet1$]"
        Conn.Execute strQry

LetsContinue:
       Application.ScreenUpdating = True
       On Error Resume Next
       rs.Close
       Set rs = Nothing
       cn.Close
       Set cn = Nothing
       On Error GoTo 0
       Exit Sub
Whoa:
       MsgBox "Error Description :" & Err.Description & vbCrLf & _
             "Error at line     :" & Erl & vbCrLf & _
             "Error Number      :" & Err.Number
       Resume LetsContinue
    End If

End If

End Sub

字符串
我的问题是在"”SQL查询.我得到的错误说“没有一个或多个所需的参数值”.我如何才能插入这些列从Excel到这些特定的Access字段？任何帮助将不胜感激.

excel

来源：https://stackoverflow.com/questions/77404493/how-to-insert-columns-of-data-from-excel-into-fields-of-an-access-database-using

2条答案

按热度按时间

bq9c1y661#

尝试这个简短的测试程序来确认字段名称

Sub mydebug()

    Dim conn As Connection, rs As ADODB.Recordset
    Dim sql As String, i As Long, msg As String
    Set conn = New ADODB.Connection
    With conn
    
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=Database2.accdb"
        .Open
        
        sql = "SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=" & Application.ThisWorkbook.FullName & "].[Sheet1$]"
        Debug.Print sql
        
        Set rs = conn.Execute(sql)
        For i = 1 To rs.Fields.Count
            msg = msg & vbLf & i & "  [" & rs.Fields(i - 1).Name & "]"
        Next
        MsgBox "Field Names" & msg, vbInformation
        
    End With

End Sub

字符串

赞(0）回复(0）举报 11个月前

ujv3wf0j2#

我发现了我的问题.这是相当尴尬的，但有些人可能会面临它在未来.我的问题是，我编码的个人.XLSB和我打电话给下面的代码：

xlFilePath = Application.ThisWorkbook.FullName

字符串
而我应该使用的是：

xlFilePath = Application.ActiveWorkbook.FullName

型
感谢您发送编修。

赞(0）回复(0）举报 11个月前

我来回答

如何使用VBA将Excel中的数据列插入Access数据库的字段中

2条答案

相关问题

热门标签

最新问答