时间戳

首页 > 解决方案 > VBA/Access INSERT into 将数字字符串作为数字传递的语句

sql - VBA/Access INSERT into 将数字字符串作为数字传递的语句

问题描述

我正在将工作簿中的数据读入访问表中。我遇到的麻烦是我在文本格式的单元格中有数字数据，这些数据有一个前导零，当 INSERT into 语句读取它时，它会切断前导零。传递给它的数据具有前导零，并且它进入的表列是文本格式。

是否有解决方法让 SQL 将数字字符串作为字符串传递？

您看到的各种 msgbox 用于调试目的。

Dim StringVar As Variant
Dim strLn As String
    'Asks user for Filepath
    StringVar = InputBox("Please enter file path", "Import", "")
    'Ends Function if no input or cancel is detected
    MsgBox (StringVar)
    If StringVar = "" Or StringVar = False Then
        MsgBox ("No input, Please try again")
        Exit Sub
    End If
    'Scrubs outer quotes if present
    StringVar = Replace(StringVar, Chr(34), "", 1, 2)
    MsgBox ("File Path Checked")
Dim FSO As Object
    Set FSO = CreateObject("Scripting.Filesystemobject")
    'Checks that our file exists, exits if not
    If (Not FSO.FileExists(StringVar)) Then
        MsgBox ("File does not exist, try again")
        Exit Sub
    End If
Dim xlApp As Object 'Excel.Application
Dim xlWrk As Object 'Excel.Workbook
Dim xlSheet As Object 'Excel.Worksheet
Dim i As Long
    MsgBox ("working on an excel sheet")
    Set xlApp = VBA.CreateObject("Excel.Application")

    'toggle visibility for debugging
    xlApp.Visible = False

    Set xlWrk = xlApp.Workbooks.Open(StringVar) 'opens the excel file for processing
    Set xlSheet = xlWrk.Sheets("Sheet1") 'modify to your perticular sheet
    MsgBox ("About to change the format")
    xlSheet.Columns("A").NumberFormat = "@"

    MsgBox ("Format changed")
    'walks through the excel sheet to the end and inserts the lines below the headerline into the database
    For i = 2 To xlSheet.UsedRange.Rows.Count
        DoCmd.RunSQL "Insert Into Split_List(Criteria) values(" & xlSheet.Cells(i, 1).Value & ")"
        If (MsgBox(i, vbOKCancel) = vbCancel) Then Exit Sub
    Next i
    MsgBox ("Brought in " & i & " lines")
    'Quits the file and closes the object
    xlWrk.Save
    xlWrk.Close
    xlApp.Quit

    Set xlSheet = Nothing
    Set xlWrk = Nothing
    Set xlApp = Nothing

标签： sqlexcelvbams-access

解决方案

我在这个问题中找到了我的问题的答案，我 应该在 SQL 中引用数字吗？

我没有将作为代码的 ID 作为字符串传递，而是将它们作为数字传递，因此在 Insert Into 语句中我将“xlSheet.Cells(i, 1).Value”放入 SQL 语句中围绕该值，因此它作为字符串传递

声明应如下所示

DoCmd.RunSQL "Insert Into Split_List(Criteria) values('" & xlSheet.Cells(i, 1).Value & "')"
                                                      ^                                 ^

推荐阅读