Results 1 to 4 of 4
  1. #1
    NickWren is offline Advanced Beginner
    Windows XP Access 2013
    Join Date
    Dec 2015
    Location
    Connecticut
    Posts
    70

    Import Data from Excel when columns dont match

    So I have data that comes off our machines, there isnt much to the data that you can relate back to the access database.
    Here are just a basic format of what I am trying to do, this actually didnt work that bad, however it did not put the data with the samples I wanted to, is there a better way of importing data, or can I make some kind of script that will tell the data to look for the matching WrenID (on the database) to the WrenID/SampleID (on the excel spreadsheet).


    AccessForum.zip





    I also need my database to be put into like a "window mode" not actually opening up access but opening it as an "application" (window mode) or an executable file.

    I found this on a couple of forms, but clearly I dont want to pay for this, whats my bests approach? I know its all vba scripts but is there an easier way of doing it? would it need to be a executable file.
    http://www.peterssoftware.com/ss.htm

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I get some of my users doing this ....excel columns MUST remain the same EVERYTIME.
    I tell them this over and over. Why would you change the name of the fieldname week to week??!!!

    BUT if you have a workbook with static names that don't change, then attach the xl book as a table,
    build an append query and match the names to the fields you need.
    This way you can just overwrite a generic workbook everytime, and run the query.

  3. #3
    NickWren is offline Advanced Beginner
    Windows XP Access 2013
    Join Date
    Dec 2015
    Location
    Connecticut
    Posts
    70
    I use C code (my background) to analysis our data so the data comes out with the same columns over and over.
    Are you telling me to link a table to the database, then append that table to the tblSamples using a query.
    tblSample's has a bit of sample information, but is missing the data which is what I am trying to import into the sample information.

    We dont want a table actually linked to the database, because then it seems that we have the data in two spots.

    Also the SampleName from excel is a calculated field made from access.
    We take information, put it into access then save, then have other information added later on after we do the testing. (one is signing in the sample the other is putting the data with the sample, however the data is with the basic sample info)

  4. #4
    NickWren is offline Advanced Beginner
    Windows XP Access 2013
    Join Date
    Dec 2015
    Location
    Connecticut
    Posts
    70
    I was wondering if someone can take another look at this post, my problem hasnt been solved yet and I feel like its easy to do. I dont really want to use a linked excel spreadsheet, I would rather have a button that will import a file that we choose. But the thing is the one field on the excel sheet being brought in is a calculated field on the database table, so there is no way of referencing them from two different tables.

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

Similar Threads

  1. Import Excel Worksheets and Skip Certain Columns
    By vcs1161 in forum Import/Export Data
    Replies: 5
    Last Post: 07-20-2015, 04:36 PM
  2. Import from excel when data is in different columns - beginner
    By faodavid in forum Import/Export Data
    Replies: 5
    Last Post: 08-03-2014, 12:04 PM
  3. Import access query data into excel file, using excel vba
    By exceljockey in forum Import/Export Data
    Replies: 3
    Last Post: 11-15-2013, 02:36 PM
  4. Where two things DONT match
    By stephenaa5 in forum Queries
    Replies: 3
    Last Post: 11-09-2012, 03:51 PM
  5. Replies: 0
    Last Post: 04-29-2009, 04:27 PM

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