Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Update Macro (Access XP)

    I have inherited the process of updating an access database. This is done by deleting the three tables that make up the database and re-importing the tables into Access. The database usually starts with a switchboard, but I have to by-pass this with the window / hide menu selections.

    I then use the menu / unhide selection to expose the tables. I delete the three data tables and then import three new tables (csv files--created by a report writer extracting data from our time and billing system). I then click "new" and import a table. Once I identify the csv file to import (c:test_iqReName_access-filesaccess-address list.csv), I then click finish as the csv file is designed for a clean import into Access and I don't need to provide Access additional import information. I then repeat this process to import the two additional tables. This update routine to the Access database is done every business day.

    I would like to write a macro that will mechanize this "update process" but still leave the database with the start-up switchboard for the end users. It doesn't seem that Access has a macro recorder like EXCEL and I am struggling on even how to start this process. It just seems that this should be easy to mechanize as I follow the same steps day-after-day to update--delete the same three tables and import 3 new tables with the identical name. Once I can automate the access update with VBA code, I could assign this to the task scheduler and have it run every business day automatically. I am STILL VERY new to Access so your patience is appreciated. THANKS.

  2. #2
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Macro (Access XP)

    Hi Jim,

    Something like this should work:

    Sub UpdateTables()
    DoCmd.DeleteObject acTable, "Table1"
    DoCmd.TransferText acImportDelim, , "Table1", "c:test_iqReName_access-filesaccess-address list.csv"
    DoCmd.DeleteObject acTable, "Table2"
    DoCmd.TransferText acImportDelim, , "Table2", "c:test_iqReName_access-filesaccess-address list.csv"
    DoCmd.DeleteObject acTable, "Table3"
    DoCmd.TransferText acImportDelim, , "Table3", "c:test_iqReName_access-filesaccess-address list.csv"
    End Sub

    You'll need to change "Table1", "Table2" and "Table3" to the correct table names, and obviously change the file name for the other two tables.

    Note that if the csv files contain table headings, you should add ", True" (not including the quotes) after the file names. Otherwise, Access will assign its own headings (F1, F2, F3 etc.).
    Waggers
    If at first you do succeed, you've probably missed something.

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Update Macro (Access XP)

    Dave,
    Thanks...I have the macro working. I have another question: How can I assign short-cut key assignment to this macro---similar to EXCEL? I placed the modified code in Module2 as Module1 was already used. When I exit the VBA module and return to Access, click Tools, Macro, Run Macros---while 7 are listed there---none are labeled UpdateTables--the subroutine I just created in Module2. I can't seem to find away to assign a name or key combination to the newly created Module2. Access VBA seems so different from EXCEL...not too user friendly for a lost NEWBIE. I do not want to assign the macro to the Access menu or create a tool button so that the end users don't accidently click on the menu or tool bar button. THANKS AGAIN.

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

    Re: Update Macro (Access XP)

    Macros have a specific meaning in Access, different from other Office applications. Macros are created in the eponymous section of the database window, and they don't involve Visual Basic.

    You DO need a macro (in the Access sense) to assign keyboard shortcuts. Create a macro named AutoKeys (this name is obligatory). See <post#=511,293>post 511,293</post: >.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Update Macro (Access XP)

    Dave,
    I spoke too soon about everything working okay. After I run the code in Module2 (i.e., no error messages, etc) and then go back to Access to use the switchboard functions (i.e., the options on the switchboard look like pre-defined reports here), when I click on a report to run I get an error message. After looking closely at the data tables pre and post Module 2 execution, when I update the tables manually, Access adds an "ID" column to the table--when the macro code does the update there is no "ID" column in the tables. All the other fields (Field1, Field2, Field3, etc.) are identical between the manual versus the code update process. So my hunch is that the switchboard reports don't work since the code generated table update does not produce an "ID" field in the tables. As there any way to get the code to create the "ID" column via the code update process? THANKS.

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

    Re: Update Macro (Access XP)

    Do you really need the ID column?

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Update Macro (Access XP)

    I am not sure how to update the switchboard options (these look like pre-defined queries) to accomodate the VBA updated tables without the "ID" field in the tables. That is why I asked if the "ID" field could be created via the code update process since I haven't a clue about the switchboard update. Sorry, but I am so new at Access, NEWBIE is probably even a stretch for me. THANKS.

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

    Re: Update Macro (Access XP)

    You will have to tell us in detail what the tables are for, what their structure is, how they are related and what the role of the ID field in all this is.

  9. #9
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Update Macro (Access XP)

    Hans,
    Thanks but I am so new at Access...I really can't answer these questions. As I said in the opening post, I inherited the updating of the Access tables from a departed co-worker. Given the day-after-day grind of deleting three tables and re-importing them, there seemed to me that there had to be an easier way. I take it from your reply, that when you update via the code, it won't permit the creation of an "ID" field which is somehow created when I manually update the tables. As I said in the opening post, I merely open the .csv file to import and click finish as my co-worker perfected the csv file extract process to not have to answer any of the additional import questions. THANKS AGAIN.

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

    Re: Update Macro (Access XP)

    The option to add an ID field is only available when you import interactively.

    We can help in many situations, but we are not magicians who can provide solutions without having the relevant information.

  11. #11
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Update Macro (Access XP)

    Hans,
    Thanks. The limitation is on my end--I am just too new to answer your questions. I am sorry to take up lounge resources for a dead-end resulting from my lack of knowledge. Again sorry. In reviewing the pages of notes left behind by the departed co-worker, it appears that she imported the tables and then built the queries for use via the switchboard. None of the queries appear to me to use the ID field (the queries seem to use either Field1, Field 2 or Field3 only)....but I still don't know enough to answer your questions..the database is 15mg with client information so I really can't share it. The only thing I am "sure" of is that her queries work with the ID field and don't work when I update the tables via the code. Again, sorry to waste your time as you have been so gracious to help me previously in the EXCEL lounge. Take care.

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

    Re: Update Macro (Access XP)

    Just as an experiment, you could try the following:
    - Create a copy of the database, and use that.
    - Open each of the queries in design view, and delete the ID field if it is present in the query grid.
    - See if the queries still work correctly. If not, you're out of luck, otherwise continue.
    - Also open each of the forms and reports, and delete controls bound to the ID field, if present.
    - See if they still work correctly.

  13. #13
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Update Macro (Access XP)

    Hans,
    Thanks...I followed your suggestions and am "out of luck". The ID field is NOT present in the query grid for any of the queries. I also checked forms and reports--ID field is NOT present on the grid....When I try to run the queries after the code updates the table, a dialog box opens, the box is entitled "enter Parameter Value:" and then it has the table name, a period and then Field1. It seems whether I hit OK with a value or no value entered the dialog box re-appears with the table name, a period and the Field2, and this keeps repeating until I finally hit cancel on the dialog box. Thanks again for your patience. Looks like I will need to learn how to re-create these queries if I am to use the code to update the tables. Again thanks for your patience.

  14. #14
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Update Macro (Access XP)

    Hans,
    Reading further into the notes left by the former employee...none of the tables are linked. In fact, she tried to share this information via EXCEL that contained 3 worksheets populated with the table data, but found that EXCEL would "hang" after 4-5 users had opened the EXCEL file .--so she switched it to Access to permit multiple users to view the data. This is not an interactive database---merely used to look-up information which is why she created the switchboard with the queries already created. Again, thanks for your patience....I'll stick to manually updating the tables until I can figure out how to re-create the queries. Somehow Access uses the ID field behind the scenes. THANKS again! Take care.

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

    Re: Update Macro (Access XP)

    Have you tried Dave's suggestion at the end of his first reply? That is, change

    DoCmd.TransferText acImportDelim, , "Table1", "c:test_iqReName_access-filesaccess-address list.csv"

    to

    DoCmd.TransferText acImportDelim, , "Table1", "c:test_iqReName_access-filesaccess-address list.csv", True

    (with the appropriate table name substituted), and similar for the other two import instructions.

Page 1 of 3 123 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
  •