Results 1 to 4 of 4
  1. #1
    Swade730 is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2016
    Posts
    4

    Post Using excel workbook to update access database on conditions Help needed

    Hi All,




    I'm looking to accomplish the following...


    1. I want to use an Excel work book that will allow me click a button> to select another excel file (With my stored data) to input into access> and Upload that selected files data to the appropriate Columns in Access.


    2. The Data I am working with is NBA Stats for NBA Players.
    -If the Player exist in the database - and the stats are the same --pretty much do nothing.
    -If the player does not exist in the database and is present on the new data set I am importing (via the Macro workbook), Insert the new row.
    -If the Data being does not include all the players existing in the database- then the players in the database move to in-active


    Basically the Data being imported will determine how the database is updated/behaves


    How Can I accomplish this using a macro?


    The reason for the work book is to have end users use it. They should be able to click a button - which will then prompt them to select the file from the folder...Whereby one of the three situations happens row for row...


    attached is sample data.


    Any help would be so greatly appreciated
    Code:
    DATA SET DATE PLAYER FULL NAME POSITION OWN TEAM OPP TEAM V MIN BL PTS
    2013-2014 Regular Season 10/29/2013 Jason Maxiell PF Orlando Indiana R 26.3 2 0
    2013-2014 Regular Season 10/29/2013 Maurice Harkless SF Orlando Indiana R 23.5 0 14
    2013-2014 Regular Season 10/29/2013 Nikola Vucevic C Orlando Indiana R 30.7 1 8
    2013-2014 Regular Season 10/29/2013 Arron Afflalo SG Orlando Indiana R 33.0 0 9
    2013-2014 Regular Season 10/29/2013 Jameer Nelson PG Orlando Indiana R 30.6 0 12
    2013-2014 Regular Season 10/29/2013 Victor Oladipo PG Orlando Indiana R 22.6 0 12
    2013-2014 Regular Season 10/29/2013 Andrew Nicholson PF Orlando Indiana R 19.3 0 18
    2013-2014 Regular Season 10/29/2013 E'Twaun Moore SG Orlando Indiana R 25.8 0 6
    2013-2014 Regular Season 10/29/2013 Kyle O'Quinn C Orlando Indiana R 14.4 3 6
    2013-2014 Regular Season 10/29/2013 Ronnie Price SG Orlando Indiana R 8.6 0 2
    2013-2014 Regular Season 10/29/2013 Solomon Jones PF Orlando Indiana R 5.4 0 0
    2013-2014 Regular Season 10/29/2013 Paul George SF Indiana Orlando H 36.1 3 24
    2013-2014 Regular Season 10/29/2013 David West PF Indiana Orlando H 30.7 5 13
    2013-2014 Regular Season 10/29/2013 Roy Hibbert C Indiana Orlando H 26.9 7 8
    2013-2014 Regular Season 10/29/2013 Lance Stephenson SG Indiana Orlando H 35.6 1 19
    2013-2014 Regular Season 10/29/2013 George Hill PG Indiana Orlando H 29.1 0 7
    2013-2014 Regular Season 10/29/2013 Orlando Johnson SG Indiana Orlando H 15.2 0 9
    2013-2014 Regular Season 10/29/2013 Luis Scola PF Indiana Orlando H 20.7 0 8
    2013-2014 Regular Season 10/29/2013 CJ Watson PG Indiana Orlando H 19.9 0 5
    2013-2014 Regular Season 10/29/2013 Solomon Hill SF Indiana Orlando H 8.1 0 0
    2013-2014 Regular Season 10/29/2013 Ian Mahinmi C Indiana Orlando H 16.7 2 4
    2013-2014 Regular Season 10/29/2013 Rasual Butler SG Indiana Orlando H 1.0 0 0
    2013-2014 Regular Season 10/29/2013 Luol Deng SF Chicago Miami R 29.5 0 4
    2013-2014 Regular Season 10/29/2013 Carlos Boozer PF Chicago Miami R 32.4 1 31
    2013-2014 Regular Season 10/29/2013 Joakim Noah C Chicago Miami R 20.2 2 2
    2013-2014 Regular Season 10/29/2013 Jimmy Butler SG Chicago Miami R 29.9 1 20
    2013-2014 Regular Season 10/29/2013 Derrick Rose PG Chicago Miami R 34.4 0 12
    2013-2014 Regular Season 10/29/2013 Mike Dunleavy SF Chicago Miami R 22.9 0 10
    2013-2014 Regular Season 10/29/2013 Taj Gibson PF Chicago Miami R 29.3 0 10
    2013-2014 Regular Season 10/29/2013 Kirk Hinrich PG Chicago Miami R 29.5 0 6
    2013-2014 Regular Season 10/29/2013 Nazr Mohammed C Chicago Miami R 5.3 0 0
    2013-2014 Regular Season 10/29/2013 Tony Snell SG Chicago Miami R 6.7 0 0
    2013-2014 Regular Season 10/29/2013 LeBron James PF Miami Chicago H 38.0 0 17
    2013-2014 Regular Season 10/29/2013 Udonis Haslem PF Miami Chicago H 21.5 0 6
    2013-2014 Regular Season 10/29/2013 Chris Bosh C Miami Chicago H 31.4 3 16
    2013-2014 Regular Season 10/29/2013 Dwyane Wade SG Miami Chicago H 36.1 1 13
    2013-2014 Regular Season 10/29/2013 Mario Chalmers PG Miami Chicago H 26.8 0 13
    2013-2014 Regular Season 10/29/2013 Ray Allen SG Miami Chicago H 26.3 1 11
    2013-2014 Regular Season 10/29/2013 Shane Battier SF Miami Chicago H 22.2 0 14
    2013-2014 Regular Season 10/29/2013 Norris Cole PG Miami Chicago H 21.2 0 11
    2013-2014 Regular Season 10/29/2013 Chris Andersen C Miami Chicago H 16.6 2 6
    2013-2014 Regular Season 10/29/2013 Jared Dudley SF LA Clippers LA Lakers R 22.7 0 5
    2013-2014 Regular Season 10/29/2013 Blake Griffin PF LA Clippers LA Lakers R 40.2 0 19
    2013-2014 Regular Season 10/29/2013 DeAndre Jordan C LA Clippers LA Lakers R 35.5 3 17
    2013-2014 Regular Season 10/29/2013 JJ Redick PG LA Clippers LA Lakers R 33.8 0 13
    2013-2014 Regular Season 10/29/2013 Chris Paul PG LA Clippers LA Lakers R 36.0 0 15
    2013-2014 Regular Season 10/29/2013 Matt Barnes SF LA Clippers LA Lakers R 28.5 0 8
    2013-2014 Regular Season 10/29/2013 Jamal Crawford SG LA Clippers LA Lakers R 25.9 0 15
    2013-2014 Regular Season 10/29/2013 Darren Collison PG LA Clippers LA Lakers R 12.5 0 9
    2013-2014 Regular Season 10/29/2013 Ryan Hollins PF LA Clippers LA Lakers R 4.0 1 0
    2013-2014 Regular Season 10/29/2013 Reggie Bullock SF LA Clippers LA Lakers R 1.1 0 2
    2013-2014 Regular Season 10/29/2013 Nick Young SG LA Lakers LA Clippers H 22.1 0 13
    2013-2014 Regular Season 10/29/2013 Shawne Williams PF LA Lakers LA Clippers H 12.8 2 3
    2013-2014 Regular Season 10/29/2013 Pau Gasol C LA Lakers LA Clippers H 24.5 0 15
    2013-2014 Regular Season 10/29/2013 Steve Blake PG LA Lakers LA Clippers H 22.4 0 6
    2013-2014 Regular Season 10/29/2013 Steve Nash PG LA Lakers LA Clippers H 20.7 0 3
    2013-2014 Regular Season 10/29/2013 Chris Kaman C LA Lakers LA Clippers H 19.7 2 10
    2013-2014 Regular Season 10/29/2013 Jodie Meeks SG LA Lakers LA Clippers H 25.6 0 13
    2013-2014 Regular Season 10/29/2013 Jordan Farmar PG LA Lakers LA Clippers H 27.3 0 16
    2013-2014 Regular Season 10/29/2013 Xavier Henry SF LA Lakers LA Clippers H 26.4 0 22
    2013-2014 Regular Season 10/29/2013 Wesley Johnson SF LA Lakers LA Clippers H 20.5 1 3
    2013-2014 Regular Season 10/29/2013 Jordan Hill PF LA Lakers LA Clippers H 18.0 1 12
    2013-2014 Regular Season 10/30/2013 Paul Pierce SF Brooklyn Cleveland R 29.9 0 17
    2013-2014 Regular Season 10/30/2013 Kevin Garnett C Brooklyn Cleveland R 26.3 1 8
    2013-2014 Regular Season 10/30/2013 Brook Lopez C Brooklyn Cleveland R 33.6 4 21
    2013-2014 Regular Season 10/30/2013 Joe Johnson SG Brooklyn Cleveland R 34.4 0 13
    2013-2014 Regular Season 10/30/2013 Deron Williams PG Brooklyn Cleveland R 21.7 1 7
    2013-2014 Regular Season 10/30/2013 Andray Blatche PF Brooklyn Cleveland R 20.3 1 0
    2013-2014 Regular Season 10/30/2013 Reggie Evans PF Brooklyn Cleveland R 15.8 1 4
    2013-2014 Regular Season 10/30/2013 Alan Anderson SF Brooklyn Cleveland R 23.4 0 9
    2013-2014 Regular Season 10/30/2013 Shaun Livingston PG Brooklyn Cleveland R 15.6 0 1
    2013-2014 Regular Season 10/30/2013 Jason Terry PG Brooklyn Cleveland R 19.1 0 14
    2013-2014 Regular Season 10/30/2013 Earl Clark PF Cleveland Brooklyn H 27.1 0 9
    2013-2014 Regular Season 10/30/2013 Tristan Thompson PF Cleveland Brooklyn H 34.9 0 18
    2013-2014 Regular Season 10/30/2013 Anderson Varejao C Cleveland Brooklyn H 34.6 1 11
    2013-2014 Regular Season 10/30/2013 Dion Waiters SG Cleveland Brooklyn H 29.4 0 11
    2013-2014 Regular Season 10/30/2013 Kyrie Irving PG Cleveland Brooklyn H 30.9 0 15
    2013-2014 Regular Season 10/30/2013 Jarrett Jack PG Cleveland Brooklyn H 22.5 0 12
    2013-2014 Regular Season 10/30/2013 Alonzo Gee SF Cleveland Brooklyn H 18.8 0 7
    2013-2014 Regular Season 10/30/2013 Anthony Bennett SF Cleveland Brooklyn H 15.0 0 2
    2013-2014 Regular Season 10/30/2013 Andrew Bynum C Cleveland Brooklyn H 7.6 2 3
    2013-2014 Regular Season 10/30/2013 CJ Miles PG Cleveland Brooklyn H 15.3 0 10
    2013-2014 Regular Season 10/30/2013 Tyler Zeller C Cleveland Brooklyn H 4.0 0 0
    2013-2014 Regular Season 10/30/2013 LeBron James PF Miami Philadelphia R 36.6 0 25
    2013-2014 Regular Season 10/30/2013 Udonis Haslem PF Miami Philadelphia R 11.5 0 2
    2013-2014 Regular Season 10/30/2013 Chris Bosh C Miami Philadelphia R 34.3 0 22
    2013-2014 Regular Season 10/30/2013 Roger Mason Jr. SF Miami Philadelphia R 14.6 0 3
    2013-2014 Regular Season 10/30/2013 Mario Chalmers PG Miami Philadelphia R 30.6 0 16
    2013-2014 Regular Season 10/30/2013 Shane Battier SF Miami Philadelphia R 23.7 0 2
    2013-2014 Regular Season 10/30/2013 Norris Cole PG Miami Philadelphia R 27.0 0 10
    2013-2014 Regular Season 10/30/2013 Ray Allen SG Miami Philadelphia R 27.9 0 19
    2013-2014 Regular Season 10/30/2013 Chris Andersen C Miami Philadelphia R 13.8 0 0
    2013-2014 Regular Season 10/30/2013 Rashard Lewis SF Miami Philadelphia R 20.1 0 11
    2013-2014 Regular Season 10/30/2013 Evan Turner SF Philadelphia Miami H 37.2 0 26
    2013-2014 Regular Season 10/30/2013 Thaddeus Young PF Philadelphia Miami H 34.3 0 10
    2013-2014 Regular Season 10/30/2013 Spencer Hawes C Philadelphia Miami H 29.4 0 24
    2013-2014 Regular Season 10/30/2013 James Anderson SG Philadelphia Miami H 35.4 0 8
    2013-2014 Regular Season 10/30/2013 Michael Carter-Williams PG Philadelphia Miami H 36.2 0 22
    2013-2014 Regular Season 10/30/2013 Tony Wroten SG Philadelphia Miami H 23.4 0 14
    2013-2014 Regular Season 10/30/2013 Lavoy Allen PF Philadelphia Miami H 17.4 0 4
    2013-2014 Regular Season 10/30/2013 Daniel Orton C Philadelphia Miami H 14.9 1 4
    2013-2014 Regular Season 10/30/2013 Darius Morris PG Philadelphia Miami H 11.8 0 2
    2013-2014 Regular Season 10/30/2013 Gerald Wallace SF Boston Toronto R 39.9 0 3
    2013-2014 Regular Season 10/30/2013 Brandon Bass PF Boston Toronto R 32.2 0 17
    2013-2014 Regular Season 10/30/2013 Vitor Faverani C Boston Toronto R 26.7 3 13
    2013-2014 Regular Season 10/30/2013 Jeff Green SF Boston Toronto R 37.1 1 25
    2013-2014 Regular Season 10/30/2013 Avery Bradley PG Boston Toronto R 31.3 1 8
    2013-2014 Playoffs 06/15/2014 LeBron James PF Miami San Antonio R 41.3 2 31
    2013-2014 Playoffs 06/15/2014 Rashard Lewis SF Miami San Antonio R 9.0 0 3
    2013-2014 Playoffs 06/15/2014 Chris Bosh C Miami San Antonio R 38.6 0 13
    2013-2014 Playoffs 06/15/2014 Dwyane Wade SG Miami San Antonio R 36.0 0 11
    2013-2014 Playoffs 06/15/2014 Ray Allen SG Miami San Antonio R 31.3 0 5
    2013-2014 Playoffs 06/15/2014 Shane Battier SF Miami San Antonio R 11.0 0 0
    2013-2014 Playoffs 06/15/2014 Chris Andersen C Miami San Antonio R 18.0 1 0
    2013-2014 Playoffs 06/15/2014 Norris Cole PG Miami San Antonio R 8.8 0 2
    2013-2014 Playoffs 06/15/2014 Udonis Haslem PF Miami San Antonio R 9.8 1 2
    2013-2014 Playoffs 06/15/2014 Michael Beasley SF Miami San Antonio R 17.0 0 9
    2013-2014 Playoffs 06/15/2014 Mario Chalmers PG Miami San Antonio R 14.9 0 8
    2013-2014 Playoffs 06/15/2014 James Jones SF Miami San Antonio R 2.2 0 0
    2013-2014 Playoffs 06/15/2014 Toney Douglas PG Miami San Antonio R 2.2 0 3
    2013-2014 Playoffs 06/15/2014 Kawhi Leonard SF San Antonio Miami H 34.0 1 22



    Rows to be updated Sample
    Code:
    DATA SET DATE PLAYER FULL NAME POSITION OWN TEAM OPP TEAM V MIN BL PTS
    2013-2014 Regular Season 10/29/2013 Jason Maxiell PF Orlando Indiana R 21.32 5 2
    2013-2014 Regular Season 10/29/2013 Maurice Harkless SF Orlando Indiana R 18.5 4 3
    2013-2014 Regular Season 10/29/2013 Nikola Vucevic C Orlando Indiana R 25.65 3 4
    2013-2014 Regular Season 10/29/2013 Arron Afflalo SG Orlando Indiana R 27.98 2 6
    Attached Files Attached Files

  2. #2
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    239
    If I understand, what you need is Import to Acces - take a look at this:

    https://msdn.microsoft.com/en-us/lib.../ff844793.aspx

    However, there are many other issues to solve when importing, for example - are fields named same, what kind of file to import (.xls,.xslx etc.), where does first column of data start etc.

    I'm doing same thing now, and I confronted a lot of issues in Access. But I'm doing It from Access, not Excel, maybe that is what you want too ? I have some code that might help you...

  3. #3
    Swade730 is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2016
    Posts
    4
    Quote Originally Posted by Lukael View Post
    If I understand, what you need is Import to Acces - take a look at this:

    https://msdn.microsoft.com/en-us/lib.../ff844793.aspx

    However, there are many other issues to solve when importing, for example - are fields named same, what kind of file to import (.xls,.xslx etc.), where does first column of data start etc.

    I'm doing same thing now, and I confronted a lot of issues in Access. But I'm doing It from Access, not Excel, maybe that is what you want too ? I have some code that might help you...
    Hey,

    I would love to see the code you are working on...I have some other methods that I am trying my self that I am willing to share...

    are you going to go the Append Query route?

  4. #4
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    239
    No problem, that is why forums are for...

    It's opposite of what you're looking for, since you start from Excel, but at least second part could help you a little bit.

    1. I created a form with Textbox, cmdButton for Import and cmdButton for browsing files on computer. This code applies for FileDialogPicker (a.k.a. browsing window):

    Code:
    Private Sub cmdBrowseFiles_Click()
    
    Dim Browse_Title As String
    Dim Filepath As String
    Dim ChoosenFile As Variant
    
    Browse_Title = "Title of browsing window"
    
    'Open browsing window in browsing files mode
    
    With Application.FileDialog(msoFileDialogFilePicker)
      With .Filters
        .Clear
        .Add "All Files", "*.*"     'Allow all files
      End With
    
    .AllowMultiSelect = False      'Choose only one file
      
    .InitialFileName = vbNullString
        .InitialView = msoFileDialogViewDetails 
        .Title = Browse_Title
    
    If .Show Then
      
    'Since AllowMultiselect false only one file will be selected
    For Each ChoosenFile In .SelectedItems
      
          'Enter choosen file name in Textbox
    FilePath = ChoosenFile
            Me![MyTextbox] = FilePath
        Next ChoosenFile
        
      End If
      
    End With
    
    End Sub
    2.When file name is inserted in Textbox I launch code for Import. Now here is where It starts tricky. There are several ways to accomplish Import, and some are difficult. In my case, I import from .xls file into pre-created table into Access using TransferSpreadsheet method, and then import this data into desired destination table using SQL strings. Here It is:

    Code:
    Sub FileImport()
    
    Dim FilePath As String
    Dim SQL As String
    
    Me.MyTextbox.SetFocus
    FilePath = MyTextbox.Text
    
    'First check if file exists - see function below this code for this - next code
    
    If FileExists(FilePath) Then
    
    'Import into pre-created table - be very carefull when creating table type of fields - they must be same or import in next step will not work !!
    ' This code also creates a table if It doesn't exist, but type of fields will be set to Text only - so again, be carefull with that
    
    DoCmd.TransferSpreadsheet acImport, , "NEWTABLE", FilePath, True
    
    Else
    MsgBox "File not found. Please check file name or It's location ! "
    
    End If
    
    ' This is SQL string, and this is probably where you'll spend a lot of time - Access doesn't work quite same as MySQL 
    ' Provided code here might help you a lot - It imports in field where you desire, from table you desire, and It eliminates empty rows & duplicates - however you'll need a lot of changes for your need
    
    SQL = " INSERT INTO DestinationTable (Field1, Field2)" & _
    " SELECT DISTINCT Field3,Field4" & _
    " FROM NEWTABLE" & _
    " WHERE NOT EXISTS(SELECT * FROM DestinationTable WHERE (NEWTABLE.Field3 = Destination.Field1 AND NEWTABLE.Field4 = DestinationTable.Field2 ))" & _
    " AND NOT (Field3 IS NULL AND Field4 IS NULL)"
    
    ' And then execute SQL
     DoCmd.RunSQL SQL
    
    End Sub
    3. This is function for checking files If they exists - It applies to code above :

    Code:
    Function FileExists(stestfile As String)
    
    
    Dim lsize As Long
    
    
    On Error Resume Next
    'preset length to -1 because files can be zero
    
    
    lsize = -1
    'get the length of the file
    lsize = FileLen(stestfile)
    If lsize > -1 Then
    FileExists = True
    Else
    FileExists = False
    End If
    
    End Function

    Ok, this is It from me. Another way that I was advised on this forum is to create VBA code for saving Excel file as .xls, then link this file (I reckon with TransferSpreadsheet method too, but instead "acImport" use the "acLink") to Access and run Import macro. However, I have difficulties on saving Excel file as .xls, so If you have code for that, you're more than welcome to share It with me

    I also haven't really tested what happens with import If your Excel file Isn't "well prepared" - I tested my code and It works with no problem If columns with data have names as you write them in SQL string, and If they are set in columns A,B,C and so on - one after another. Maybe code doesn't work when columns are scrambled (with empty columns between them). So I'm allready thinking to write some code where I would first check the name of columns, and then import data from that columns. Here you can help me too

    I hope I helped you a little, I know It's a struggle !! Best luck !!

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

Similar Threads

  1. Taking a large Excel workbook and creating a database
    By desertratz in forum Database Design
    Replies: 3
    Last Post: 01-16-2016, 03:11 PM
  2. Using VBA originally written for Excel Workbook in Access
    By AccessRookee in forum Programming
    Replies: 4
    Last Post: 12-10-2014, 12:54 PM
  3. Opening Excel Workbook From Access with VBA
    By kestefon in forum Access
    Replies: 4
    Last Post: 01-31-2014, 06:51 PM
  4. Replies: 4
    Last Post: 12-17-2012, 01:21 PM
  5. Send an excel workbook from access
    By haazzaa in forum Access
    Replies: 1
    Last Post: 07-26-2012, 05:40 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