Results 1 to 3 of 3
  1. #1
    deekadelic is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    23

    Need to Edit Saved Import

    I'm setting up a daily import operation from Excel, and the source files I'm using have close to 90 columns. If a single column has a datatype problem or any other issue, the entire import fails. When this happens, I have to start from scratch and rebuild the entire Saved Import from scratch. I'm figuring there must be a way to edit my Saved Imports. Am I wrong?



    I'm using Access 2016.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    it usu happens because access cant determine what data type the column is, so it guesses, and sometimes wrong.
    if you have a column that has both numbers and strings, then I run a macro before import to set everything to string by putting a single quite at the front of the value.
    Once your data types are set, then save the file to a generic filename, like: ImportFile.xlsx
    attach the file as a table
    to import just run an append query.

    i sometimes run 'validation' queries to see if there is bad data before the import.

    excel macro to convert data to string....

    Code:
    Sub Cvt2Txt()
    While ActiveCell.Value <> ""
       vTxt = ActiveCell.Value
       If Left(vTxt, 1) <> "'" Then ActiveCell.Value = "'" & vTxt
       ActiveCell.Offset(1, 0).Select   'next row
    Wend
    End Sub

  3. #3
    deekadelic is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    23
    I could do that, yes, but I guess I should have been more specific about what kinds of various scenarios would lead me to need to edit the Saved Import. For instance, I might have to add or delete a column from the import specifications; so either way I need to get into the design of the Saved Import to tweak it.

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

Similar Threads

  1. My saved import/export are gone
    By egyhamada in forum Import/Export Data
    Replies: 4
    Last Post: 02-06-2016, 08:47 AM
  2. Refreshing a Saved Import
    By accessmatt in forum Access
    Replies: 1
    Last Post: 09-22-2014, 04:00 PM
  3. Import spec saved
    By crowegreg in forum Import/Export Data
    Replies: 6
    Last Post: 02-17-2014, 04:11 PM
  4. Opening Saved data in form in Edit mode
    By l3111 in forum Forms
    Replies: 7
    Last Post: 06-06-2011, 11:37 AM
  5. Saved Import in Query
    By Kimbertha in forum Queries
    Replies: 1
    Last Post: 10-21-2010, 07:21 AM

Tags for this Thread

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