Results 1 to 12 of 12
  1. #1
    hyperionfall is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2010
    Location
    mississippi
    Posts
    18

    update from excel


    I have a table that i wish to export to execl make some changes to the values and then import it back in updateing the original table with the new information. i tried import export but that will only apend not update. i am guessing this can only be done through SQL or vb which i am novice at best.
    just so you understand why i want to do this. my systems are offshore so i cannot link tables or use a front end back end set up. each system is basicaly unique except that they will all have the same parts list to build their inventory from. i may need to change the design of the program from time to time or add parts to the lis and need them to export their unique local data held in tables and install the new access and then import their data back in. BTW 1 table has lookup fields the rest are just place holders. they have very limited internet so usually the updates are sent on thumbdrive or cd.

    thx

  2. #2
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    First of all I must say I found your thread very interesting. Going through it i assume that You have the following requirements:

    1) Export data from a table int Excel
    2) Import the Changed Data into access
    3) Update the Edited Information.

    This is What I have done:

    I have one table Called table1 with the Following Fields:

    ID,S_Name,Class,RollNumber,Grade.

    I have a a Form which appears as startup and has three buttons:

    Export To Excel: As the name Suggest the Click of this Button will transfer the Data from Table1 onto a xls in the C:\. C:\Test.xls
    If a File does exist the same is deleted and a new file is created with the data from table1.

    Code used:

    Private Sub Command0_Click()
    On Error GoTo Err_Command0_Click

    'Declarations
    Dim strExcelFile As String
    Dim strWorksheet As String
    Dim strTable As String
    Dim objDB As Database


    strExcelFile = "C:\Test.xls" 'This is the path where you want to create the xls
    strWorksheet = "WorkSheet1" 'Name of the WorkSheet you can Change according to your preference
    strTable = "Table1"

    Set objDB = CurrentDb 'odjDB Set to current database
    If Dir(strExcelFile) <> "" Then Kill strExcelFile 'Deletes an existing file with the same name if present to save fresh export
    'Sql executed to write Data on to the xls

    objDB.Execute _
    "SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
    "].[" & strWorksheet & "] FROM " & "[" & strTable & "]"
    objDB.Close
    Set objDB = Nothing 'Memory Reclaimed



    Exit_Command0_Click:
    Exit Sub

    Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click

    End Sub

    Import SpreadSheet: On Clicking this button the modified information in the Test.xls is imported into the database and as table ImportTB. Before Importing the code attached to this button checks whether a Table ImportTB exist if it does it is deleted to import the new edited data in the Test.xls.

    Code Used:

    Private Sub Command1_Click()
    On Error GoTo Err_Command1_Click
    'This Code deletes the Table ImportTB if Present. Prevents Error if No Table is There
    On Error Resume Next
    CurrentDb.TableDefs.Delete "ImportTB"
    'Spread Sheet is imported into the Database as a Table Named ImportTB
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "ImportTB", "C:\Test.xls", True

    Exit_Command1_Click:
    Exit Sub

    Err_Command1_Click:
    MsgBox Err.Description
    Resume Exit_Command1_Click

    End Sub


    Update Table: This button Updates the data in Table 1 with the edited data in Table ImportTB.

    Code Used:
    Private Sub Command2_Click()
    On Error GoTo Err_Command2_Click
    'The Data is updated using an Update query
    Dim strSQL As String
    strSQL = "UPDATE ImportTB, Table1 SET Table1.S_Name = [ImportTB].[S_Name], Table1.Class = [ImportTB].[Class], Table1.RollNumber = [ImportTB].[RollNumber], Table1.Grade = [ImportTB].[Grade] WHERE Table1.ID=[ImportTB].[ID];"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    Exit_Command2_Click:
    Exit Sub

    Err_Command2_Click:
    MsgBox Err.Description
    Resume Exit_Command2_Click

    End Sub


    How to use the sample mdb:
    1) In the startup Form Click Export Into Excel Button.
    2) Open The Test.xls in the C drive
    3) Make Changes to the data in the Test.xls.
    4) Save the File.
    5) Click on Import The Modified SpreadSheet Button to import data from Test.xls and Create the Table ImportTB
    6) Click Update Table 1 with Information in xls Button to Update the Changes.
    7) Compare the Result.




    if this solves your problem mark the thread solved.

  3. #3
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Note:
    1) Its is not need to create the Test.xls file in the C:\ physically by you. The Code in the Export to Excel takes care of it.
    2) I have assumed that edit will be made to data in the fields and and not addition and deletion of Fields will be done.

  4. #4
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    is you problem solved if yes mark the thread solved.

  5. #5
    Datagopherdan is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Dec 2008
    Posts
    220
    Hi Maximus,

    Very useful code. I would just recommend making one change to it. Replace the below.

    Code:
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    with

    Code:
    CurrentDb.Execute strSQL
    When you turn system warnings off, you run the risk that if the code does not run all the way through, system warnings will remain off, which makes it easy for users to accidentally perform actions like deleting records with no warning prompts.

  6. #6
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    The execute method is a DAO method of the database object and it interacts
    directly with the Jet engine. Anything that starts with "DoCmd" is part of
    the Access interface.

    Generally, the Execute method is better. No "are you sure" prompts and you
    can trap for errors. On the other hand RunSQL can invoke the expression
    service so it can resolve things like "Forms!NameOfForm!NameOfControl" where
    the Execute method cannot. That limitation of Execute is easily worked
    around though by taking the reference out of the SQL quoted string so VBA
    can evaluate it...

    MySQL = "SELECT * FROM MyTable WHERE SomeField = '" &
    Forms!NameOfForm!NameOfControl & "'"

    adding

    CurrentDb.Execute strSQL,dbFailOnError switch lets you know if it worked or not

    You can use it with transactions, to roll back after a problem

    Some say the you get better speed with Execute but I have not conducted the test.

    As RunSql is a part of Access interface easy to use and for beginners very good. sure CurrentDB.Execute is better most us will say that for sure.

    More over you need Objects References (DAO object library) for Execute and not for Docmd.RunSQL

    The one advantage that Docmd.RunSQL has is the progress bar at the bottom of the screen Ha!Ha!

    Now the choice is yours my friend.

  7. #7
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    thanks Dan for the tip,

  8. #8
    Datagopherdan is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Dec 2008
    Posts
    220
    Anytime Maximus. I would disagree with you in regards to turning system warnings off. I used to write code that way when I was first starting out and I had many a experienced developers telling me to never do it that way where you are turning system warnings off.

    Also, you can provide a status for the user with the CurrentDb.Execute method. You would do something like this.

    Code:
    Dim IntRetVal As Integer
    'Set the status
    IntRetVal = SysCmd(acSysCmdSetStatus, "Running Query Now")
    CurrentDb.Execute strSQL
    'Clear the status
    IntRetVal = SysCmd(acSysCmdSetStatus, " ")

  9. #9
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Sure sir

    all taken in and absorbed.

    Novice like me has yet to learn a lot Ha!Ha!

  10. #10
    kami.raz is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Apr 2010
    Posts
    1
    hi

    im trying to post

    this is a test , sorry

  11. #11
    Datagopherdan is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Dec 2008
    Posts
    220
    You know alot. Learning is a never-ending process. No matter how long you've been doing something, theres always something new to learn.

  12. #12
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Sure Sir I am a willing learner. I try my level best. I always look forward to constructive suggestions. Thank you for the compliment.

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

Similar Threads

  1. VBA to open excel, import on close of excel
    By bdaniel in forum Programming
    Replies: 0
    Last Post: 03-20-2010, 02:45 PM
  2. Code to Update Excel from Access
    By portmancp in forum Programming
    Replies: 2
    Last Post: 03-10-2010, 03:06 AM
  3. Update Query- selective update?
    By stephenaa5 in forum Queries
    Replies: 1
    Last Post: 10-29-2009, 11:15 AM
  4. Replies: 1
    Last Post: 08-31-2009, 10:24 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