Results 1 to 5 of 5
  1. #1
    Lucky245 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    May 2019
    Posts
    3

    Using APPEND Query using vba code

    I have linked multiple spreadsheets each relating to a day (exported daily from a lab system) to my database e.g CD_01, CD_02 etc to CD_31.



    I need to append their contents to a master table depending on the date (where I will remove any duplicates). Rather than using 31 potential Append Queries I would like to run code which will append the relevant data into the Master Table. I have created a Master table with all possible column headers but not each of the individual spreadsheets will contain every header except the key fields.

    A key problem is that unlike the Master table the linked tables will most likely change format eg the number of columns and some column headers from month to month dependant on the lab outputs.

    I can get the database to work out what linked table is needed according to date opened but need to append this data to the Master Table.

    Any help appreciated even if its only a pointer to where to look. I know someone here will come back with a one liner but I seem to have a mental block on this.

    Thanks
    Last edited by Lucky245; 05-16-2019 at 11:53 AM. Reason: Make problem clearer.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    If your spreadsheets are identical, what I have done in the past is to have an 'import' excel file and linked to that. Then when ready to import a file, just copy the one you want to import to replace the 'import' file.

    Alternatively you can do this with vba and a sql statement. The basic select statement remains the same, just change the file name. code might be something like

    Code:
    dim fName as string
    dim xlRange as string
    dim connStr as string
    dim sqlStr as string
    
    
    xlRange="Sheet1$A1:Z300"
    connStr="[Excel 12.0;Hdr=Yes;IMEX=1;ACCDB=Yes]"
    
    fName="C:\import\" & inputbox("Enter file name") & ".xlsx"
    
    if dir(fName)<>"" then 'file found
        sqlstr="INSERT INTO myTable (fld1, fld2) SELECT fld1, fld2 FROM " & xlRange & " IN '" & fname & "'" & connStr
        currentdb.execute sqlStr
    end if

  3. #3
    Lucky245 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    May 2019
    Posts
    3
    Unfortunately the linked sheets are not the same as the master table. They can vary in number of columns and field names (column headers). The master tables holds all of the possible headers. The linked sheets can also change format from month to month eg 10 columns one month and then 15 a month later with some different headers.

    The headers names change (apart from key columns) depending on the antibiotic being tested against.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Probably you can't but if you could eliminate Excel all together you'd save yourself a whole lot of pain. Is there any reason that you can't control the sheet layout and formatting from one period to the next? Why not use db for input and forget the sheets?

    The best you will be able to do IMHO is to ascertain (per sheet) how many header rows there are and input that in a prompt. Then if you can prevent sheet data from going outside of a horizontal range, you could use a dynaminc (vertically) range to automatically grab the data and write it to the table via automation. You'd need some way to match the spreadsheet column to the correct table field. If you cannot control the sheet column name or position (i.e. they must be static) then I'd say you're beat.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you can modify the code I provided to a select query so you can parse through the column names to determine which columns you want to import, and construct another query to do the import

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

Similar Threads

  1. Replies: 19
    Last Post: 10-03-2017, 09:10 AM
  2. Replies: 11
    Last Post: 08-23-2016, 04:52 PM
  3. Access can't append all the records in the append query
    By fluffyvampirekitten in forum Access
    Replies: 2
    Last Post: 08-27-2015, 01:53 AM
  4. How to turn off prompt when running append query from code
    By GraeagleBill in forum Programming
    Replies: 5
    Last Post: 07-22-2013, 03:35 PM
  5. Replies: 1
    Last Post: 10-06-2011, 08:37 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