Results 1 to 6 of 6
  1. #1
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202

    Importing date from excel vba problems

    Good afternoon, I am attempting to import a excel datasheet into excel. The 3075-Syntex error (missing operator) in query expression. The error is showing that it is in the first box of the first row when converting to Access.
    Parameters:
    tblImportProduct has only one field of DocumentID.
    I have attmepted to change the field source data type short text, long text and number
    The field column in excel example is: 1625700_10726_20210114182539052
    Dim strColumnAcleaned As String


    Dim strSqlDml As String
    Dim intLine As Long

    The code is long, but begins with allowing the end user to chose the spreadsheet, goes directly to the workbook and begins to import: That all works! its when it begins to convert that I get the error code. Eventually I want to add 10 additional columns and was having problems with the date doing the same thing. I eliminated all the columns and am just trying to start by getting this first column to work.
    This is where I think the error is located:
    Do
    strColumnAcleaned = Replace(xlWs.Cells(intLine, 1).Value2, "'", """") 'replace single quotes in column A value if additional rows then can replace commas by dots as decimal separator in specified columns
    strSqlDml = "INSERT INTO tblImportProduct(DocumentID) VALUES('" & strColumnAcleaned & ")" 'creates a SQL insert statement using the previous obtained cleaned variables and the value for column A. The insert statement must have the sequence present in the destination table and is obtained by concatenating values per each line presented in the Excel file while iterating
    Debug.Print intLine & " - " & strSqlDml
    CurrentDb.Execute strSqlDml, dbFailOnError 'executes the insert statement against the database, the dbFailOnError is an optional value that will make the Execute process return an error if the SQL was not properly executed
    intLine = intLine + 1
    Loop Until IsEmpty(xlWs.Cells(intLine, 1)) 'stopping criteria, when values in column A stop the loop will stop,
    'please note in cells collection the first index is the row and the second one the column so we are making row changing.
    'Once the loop stops the steps after close the open workbook, quit excel and clean the memory references to the created objects

    Any help would be greatly appreciated

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It's difficult to read like that, but is that the actual code? Did you examine the debug result? You don't close out the single quote around the value.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @mlrucc, I just ran the code from your previous post and it imported 10,000 rows with zero errors.
    The spreadsheet you posted "Sample-Spreadsheet-10000-rows.xls" does not have any date fields in it.
    Do you have a different dB and a different spreadsheet?

    Did you look at the Debug.Print lines in the immediate window? Are the text fields and the date fields properly delimited in the SQL?
    Text fields are delimited with single quotes
    Date fields are delimited with hash (#) characters.

    Maybe you would post a couple of lines from the immediate window?
    Are there any blank cells in the spreadsheet for the columns you want to import into Access? If there are, you will need to handle them.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'll get out of the way.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202
    I actually figured out input it as an integer number and then ran an update query and changed it to change date. Thanks for the help I appreciate it

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You are entering dates as long integers???? Well.... OK, but it would be easier to just use dates (IMHO).


    Good luck with your project...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 02-24-2020, 10:22 PM
  2. Replies: 4
    Last Post: 03-16-2018, 12:37 AM
  3. Replies: 15
    Last Post: 09-22-2017, 02:23 PM
  4. Replies: 10
    Last Post: 12-28-2012, 02:06 PM
  5. Importing problems from Excel
    By ChrisNWV in forum Import/Export Data
    Replies: 3
    Last Post: 07-24-2012, 11:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums