0

I'm reading the data from a workbook into a table in access. the trouble I'm having is that I have numeric data in Text formatted cells which have a leading zero and when the INSERT into statement reads it in it cuts the leading zero off. The data being passed to it has the leading zero and the table column its going into is in Text format.

Is there a workaround to have the SQL pass the numeric string as a string?

the various msgboxes you see were for debugging purposes.

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
Stavros Jon
  • 1,695
  • 2
  • 7
  • 17
ChadT
  • 92
  • 10
  • 2
    Is the column in Access defined as text or number? If the access column is a number column, then of course it will strip out the leading zeros. So one thing to check is to ensure that the Access column is a text column as opposed to a number column. This should thus keep the leading zeros. – Albert D. Kallal Jan 25 '19 at 18:55
  • column is short text. I just tried using .Text and got the same results. is there a quirk about how the SQL insert statement is passing the value? – ChadT Jan 25 '19 at 19:06
  • Sorry - wrong thread - deleted the comments. Was responding to another thread. In your case, if the access column HAS to be a text data type, or it will remove the leading zeros. (sorry about the bigint wild goose chase). – Albert D. Kallal Jan 25 '19 at 19:24
  • Access is seeing the column as a number column. The values pulled from Excel will thus not have the leading zeros. If access sees that column (cell) as text, then this would/should work. So the real question then becomes is how to force access to see/format that cell as a text column as opposed to a number column. – Albert D. Kallal Jan 25 '19 at 19:25

1 Answers1

0

I Found the answer to my question in this question Should I quote numbers in SQL?

I wasn't passing the IDs which are codes as strings I was passing them as numbers so in the Insert Into statement where I put the 'xlSheet.Cells(i, 1).Value' I needed to put single quotes into the SQL statement around that value so it was passed as a string

the Statement should look like this

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