Results 1 to 4 of 4
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    update access file from Excel (2000)

    I have this macro this import 2 table from an .mdb file, my problem is if i modify a record on one of 2 table of excel is possible to update the .mdb file? Have you a suggestion?
    Tks. for all

    <font face="Script MT Bold">

    Sub EstrazioneDati()

    ' --- Esatto nome del Database completo del percorso
    ' --- nel caso il Database specificato non esista nel percorso, la macro genera un'errore
    ' --- verificare quindi l'esistenza del Database

    Dim NomeDB As String

    NomeDB = "srv04f9494rmtbaspTEMPDBDELIVERY.MDB"

    Dim StringaDiConnessione
    StringaDiConnessione = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & NomeDB & ";'"

    Dim OggettoConnessione As Object, OggettoRecordsetUn, oggettoRecordsetDue As Object
    Set OggettoConnessione = CreateObject("ADODB.Connection")
    OggettoConnessione.Open StringaDiConnessione
    Set OggettoRecordsetUn = CreateObject("ADODB.Recordset")
    Set oggettoRecordsetDue = CreateObject("ADODB.Recordset")

    ' --- in questa istruzione SQL

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: update access file from Excel (2000)

    Hi Sal,
    What you suggest is possible - it would involve using Automation with Excel reaching into Access and using the Access object model. In your situation, it would require some fairly complex code in Excel to detect that a cell in the worksheet had been modified, and then writing the change back to Access. A different approach would be to write out the entire table when the Excel user closed the workbook, but that seems to be pretty drastic, and you will encounter locking problems if two users are working at the same time. Another option might be to actually store the two tables in Excel, and attach the worksheets as tables in Access, but that effectively makes the database a single-user database, since two people cannot edit an Excel workbook at the same time.

    Is there a reason not to use Access exclusively? (Most users not having Access installed would be a good one.) I wish I had a better suggestion - perhaps one of the other loungers will have an idea.
    Wendell

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update access file from Excel (2000)

    ok! for help tks.
    I have read your suggestion and the solution ... "to write out the entire table when the Excel user closed the workbook..." is a good idea. For example insert a button on a sheet and save the data present in the sheet to save all.
    If you haev a little time for me write an example macro in this post
    Tks.
    Salvatore

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: update access file from Excel (2000)

    Sorry for the delay in responding - it has been a very busy week. After thinking about what you are trying to do, it seems to me you could do it using the Access ODBC driver and simply connecting Excel to the database. In that case you could simply open up a query using either MS Query, or opening a query within the database - you probably want to avoid using parameter queries - and as long as the recordset is updateable, you should be able to simply edit in the appropriate cell, and have the change made automatically. I've not had time to test this, so you should do some experimenting first.

    I also should note that I would not recommend rewriting the entire table (as I suggested in my previous post), as that would not be a good thing if two or more people used the database. Sorry about not being clear on that point.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •