Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MACRO + ACCESS (2000 sr 1)

    My teacher see wath your student have make... and declare your comment on the last work;-)
    My actually prob is:

    I have maked a macro (db_access) to import a data to excel and put the data in a MDB, i have maked only one macro (is only a test) to export tha data from sheet L0785_TOTALE in a table of MDB table TOTALE, but is possible in one macro similar that to import simoultaneus the other remain sheet in the MBD or a make another 2 macro and start one to one?

    http:/www.gssitaly.com/l0785_def.zip
    http:/www.gssitaly.com/prova.zip

    one dubt: but this macro update the record when export excel in to access, (if i have maked a modify in the sheet) or i imsert amny line to verivy or compare the new modify with the old and control if one of record in excel? sorry but this is the first work with access

    as usual...Tks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MACRO + ACCESS (2000 sr 1)

    I would create separate macros for each worksheet. Of course, you can then create a macro that calls each of the three in turn.

    The macro you have now will append all records to the existing table. You will probably want to delete existing records before appending the records from Excel:

    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=E:MACROL0785-AUTPROVA.MDB;"
    ' delete existing records
    cn.Execute "DELETE * FROM TOTALE"

    ' open a recordset
    Set rs = New ADODB.Recordset

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

    Re: MACRO + ACCESS (2000 sr 1)

    ok!
    But my dubt/question is:

    i hope you remember the work with userform indirizzario (L0785_DEF).
    Well if i modifuy a record is possible to refresh this also in the MDB?
    For example if i modify a record in the sheet L0785_TOTALE from the userform is possible to update automaticly the related record in the MDB table TOTALE?
    I hope to make that require a index unique in this case is in the column M of the all sheet...
    In effect i have thing, to distribuite to final users a complete wbook only with the option to view and search a record and choose one User Master of the wbook to make all funcion, upgrade, import a txt file acc...
    Is a good idea?

    The txtbox in the macro to be updating are only 18, 38,34,39...

    If mi idea is good please send me an example to permit the update in the sheet and in the table.
    Tks as usual...

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MACRO + ACCESS (2000 sr 1)

    The best way to keep the worksheet and database table synchronized would be to link the database table to the worksheet. That way, you wouldn't have to run a macro to update the database table; the link would take care of that automatically.
    Unfortunately, Access cannot link to a dynamically defined range in Excel. You would have to move the table up from row 6 to row 1 in order to be able to create the link. In other words, the information in rows 1 - 5 would have to be moved to another location.

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

    Re: MACRO + ACCESS (2000 sr 1)

    uhm...
    But not is posible to link only the refred cells of excel in to the table?
    in other word if the value of the cells of the sheet is modifyed up date the cell of the table MDB?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MACRO + ACCESS (2000 sr 1)

    You can link an Excel table in Access in two ways:

    1. Link to a worksheet; in this case, the table must start in cell A1.
    2. Link to a named range; in this case, the table can be anywhere on a worksheet.

    In both cases, Access will automatically adjust the linked table if records are added, modified or deleted, but in case 2, records added below the named range will be ignored. You could include an empty row in the named range, but that is probably not desirable (it would add an empty record to the linked table in Access.)

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

    Re: MACRO + ACCESS (2000 sr 1)

    :-( Hans tks you for suggestion but for me is Arabic language...
    For example to make a link from the cell T7 to the mdb table...?
    Or the best is a liitle example on mine project... i have linked wbook and mdb on this post...

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MACRO + ACCESS (2000 sr 1)

    Sorry, you cannot link a single cell in Access, only an entire table.

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

    Re: MACRO + ACCESS (2000 sr 1)

    ok, but an example...
    Also a little file excel and a little mdb...
    Tks

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MACRO + ACCESS (2000 sr 1)

    Attaching an example is not very useful since

    a) It won't work when you download it, for Access hard codes the path to a linked file.
    [img]/forums/images/smilies/cool.gif[/img] You still won't know how to do it yourself.

    You can create a simple Excel spreadsheet yourself, with a table starting in A1.
    Close the workbook, then open the database.
    Select File | Get External Data | Link Tables...
    Select Microsoft Excel (*.xls) in the dropdown list at the bottom.
    Then locate your spreadsheet, select it and click Link.
    The wizard will guide you through the linking process.

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

    Re: MACRO + ACCESS (2000 sr 1)

    hI, Hans,...This macro solve my problem to update the Access from Excel...
    but i have many difficult to arrage tath when o click on the DB L0785_CDI_50 from the userform...
    In effect the new macro find and insert a new value, if the value of the related txtbox are changed, in the table TOTALE of the mdb PROVA.
    Is posisible to arrange the macro also when i click on the button L0785_CDI_50 fro the userform and inseret the relative value if the value of the related txtbox are changed in the table CDI_50 of the mdb PROVA?

    In effect the line inserted in each event change of related txtbox

    UpdatePROVA Cells(ScrollBar1.Value + 6, 19), TextBox18, TextBox34, TextBox35, TextBox38, TextBox39

    Call the procedure and update the record in the MDB


    http://www.gssitaly.com/l0785_def.zip
    http://www.gssitaly.com/prova.zip

    re-download the 2 files...

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MACRO + ACCESS (2000 sr 1)

    In the procedure UpdatePROVA, you open the TOTALE table in this line:

    ProvaRecordSet.Open "TOTALE", PROVADatabase, adOpenForwardOnly, adLockPessimistic, adCmdTable

    You could modify this to open a different table depending on the active worksheet:

    Dim strTable As String
    ' Take the name of the sheet after "L0785_"
    strTable = Mid(ActiveSheet.Name, 7)
    ProvaRecordSet.Open strTable, PROVADatabase, adOpenForwardOnly, adLockPessimistic, adCmdTable

    This will only work correctly for the TOTALE and CDI_50 tables, not for the PAGATI table since it doesn't have the extra fields such as NOTE_BOU.

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

    Re: MACRO + ACCESS (2000 sr 1)

    ok!
    But where i insert this line?

    ... and this is a good idea to update the record into MDB?

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MACRO + ACCESS (2000 sr 1)

    If you read my reply again carefully, you will see where the code should go.

    I don't think it is a good idea to update the database each time you modify Textbox38 on the Indirizzario form. It will cause considerable overhead to open, update and close the database for each modification.

    By the way, you should compact your database from time to time - updating the records will cause the database to grow in size. Select Tools | Database Utilities | Compact and Repair Database (Strumenti | Utilit

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

    Re: MACRO + ACCESS (2000 sr 1)

    SORRY FOR THIS, :-( I HAVE READ carefully)...
    If you read my reply again carefully, you will see where the code should go.
    ================================================== ==========
    But is possible to exclude for procedure, and not call that always when i click on the button in user form L0785_PAGATI , not is important for the update...?

    For compact... MDB you know a comnd to make that with a an Excel macro?

Page 1 of 2 12 LastLast

Posting Permissions

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