Update Macro (Access XP)

Topic

New Reply

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.

Reply | Quote

Replies

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.).

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Do you really need the ID column?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Hans,
I tried the “true” switch but since my csv file does not have headings, the “true” switch converts the first line of data into the field headings. I do have another question, sorry. When I go back through the import text wizard, there is a page that recommends that a primary key be created for the new table. The default radio button is “Let Access add primary key”. Access then adds the ID field to the table with a seed value of 1, incrementing by 1 through the last record in the table.

For the record, the table I am trying to input has 7 columns (i.e., client number, client name, address line # 1, address line # 2, City, State and zip code. When I input via Dave’s code, Field1 is assigned to client number, Field2 is assigned client name, etc. Again, the only difference between a table input via the wizard and the macro is that the wizard adds an column to the table (i.e., a new first column called id) all the Field1 through Field7 columns match identical between the two input methods.

So I went back to Crystal reports and inserted a new first column, sequentially numbered from 1, incrementing by 1, to the last record in the table. When I input this revised table via Dave’s code, the incremental column becomes Field1 and all the other Field assignments are off by one compared to the wizard import. Access does not recognize it as an “ID” column. The queries still do not work.

So my question is: How does Access assign a “primary key” via the code import? Or is no primary key assigned via the code import? Is there any way I can tell what the primary key is on a table imported via the code? THANKS.

Reply | Quote

Docmd.TransferText doesn’t have an option to add an ID field. Dave has posted additional code in the meantime; it may have to be tweaked a bit, but it may well do what you need.

Reply | Quote

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.

Reply | Quote

Jim,

This should do what you need…

At the top of the code module, underneath “Option Compare Database”, type: “Public i As Integer” (without the quotes). Then copy and paste the following…

Function AddID(tbl As String)
Dim t As TableDef, f As DAO.Field
Set Db = CurrentDb
Set t = Db.TableDefs(tbl)
Set f = t.CreateField(“ID”, adInteger)
t.Fields.Append f
i = 0
DoCmd.RunSQL “UPDATE [” & tbl & “] SET [” & tbl & “].ID = NextNo([Field1]);”
End Function

Function NextNo(r As Variant) As Integer
i = i + 1
NextNo = i
End Function

Sub AddIndexes()
AddID “Table1”
AddID “Table2”
AddID “Table3”
End Sub

Now change “Table1” etc. to your real table names, and run AddIndexes.

This will:

• add an “ID” field to each of the three tables
• populate the “ID” field with a unique integer for each record
  [/list]Note that a number is not automatically generated for new records using this method.

  Hope this helps,

Reply | Quote

Dave,
Here is the code I have entered:

Option Compare Database
Public i As Integer
Function AddID(tbl As String)
Dim t As TableDef, f As DAO.Field
Set Db = CurrentDb
Set t = Db.TableDefs(tbl)
Set f = t.CreateField(“ID”, adInteger)
t.Fields.Append f
i = 0
DoCmd.RunSQL “UPDATE [” & tbl & “] SET [” & tbl & “].ID = NextNo([Field1]);”
End Function

Function NextNo(r As Variant) As Integer
i = i + 1
NextNo = i
End Function

Sub AddIndexes()
AddID “Access-address list”
AddID “Access-address-pi,estate,trust”
AddID “Access-clientlist3”
End Sub

I am getting a “run time error 3421, data type conversion error when I run AddIndexes. When I hit “debug” it highlights the line Set f= t.CreateField(“ID”,adInteger). Any suggestions? THANKS.

Sub UpdateTables()
DoCmd.DeleteObject acTable, “Access-address list”
DoCmd.TransferText acImportDelim, , “Access-address list”, “c:test_iqReName_access_filesAccess-address list.csv”
DoCmd.DeleteObject acTable, “Access-address-pi,estate,trust”
DoCmd.TransferText acImportDelim, , “Access-address-pi,estate,trust”, “c:test_iqReName_access_filesAccess-address-pi,estate,trust.csv”
DoCmd.DeleteObject acTable, “Access-clientlist”
DoCmd.TransferText acImportDelim, , “Access-clientlist”, “c:test_iqReName_access_filesAccess-clientlist.csv”
End Sub

Reply | Quote

Jim,

Yep, sorry, that’s my fault. From the VBA window, you need to select “References” from the “Tools” menu. Scroll down through the available references until you come to “Microsoft DAO 3.6 Object Library” and check the box next to it, then press OK. The code should then run properly.

Reply | Quote

Strictly speaking, you should use dbInteger instead of adInteger. The former is a DAO constant, the latter is an ADO constant. They both have value 3, so in practice it won’t make a difference, but it’s better to use DAO constants in DAO code.

Reply | Quote

Thanks, Hans. Database types (DAO/ADO/Jet etc.) are all still a bit alien to me!

Reply | Quote

Dave,
The reference to the “Microsoft DAO 3.6 Object Library” was already checked on my system. I unchecked it, closed, then opened references again and selected the library. Same result, same error message referencing the same line of code. Any other ideas? THANKS.

Reply | Quote

See my remark to Dave – try replacing adInteger with dbInteger.

Reply | Quote

Hans,
Using your suggestion gets rid of the previous error. Now I get a run time error 3191, “cannot define a field more than once”. When I hit debug, it highlights the line: t.Fields.Append f.

Reply | Quote

That means that the table already contains a field named ID. You can check this by opening the table in design view. If you want to continue testing, delete the ID field, then run the code again.

Reply | Quote

Hans,
I went back and ran the update tables macro again to re-establish the imported tables without an ID field. Now when I run the AddIndexs macro, I am prompted with a dialog box “enter Parameter Value, Field1, with a choice of ok or cancel. Seems any value I enter is ignored. When I click ok, I am then prompted twice more (i.e., once for each of the remaining two tables) with the same dialog box.

When I review the tables after clicking ok to the 3 dialog boxes, there is now an ID field, its located to the right of Field7 (i.e., when the import wizard does this process, the ID field is to the left of Field1), do you know if the location of the ID field in a table matters? Also, the ID field is filled with the number 1, it does not increment by 1, (2,3,4,5,6,etc.). The queries still don’t work, but I need to have the correct values in the ID field to discern if the location of the ID field in the table will be a show stopper.
I hope I have stated this information properly as I am truly beyond my bounds now…..any ideas? THANKS.

Reply | Quote

I don’t think I can provide useful help without seeing what you’re working with. Could you attach zip file with a stripped down copy of the database and of at least one of the files you’re importing? See post 401925 for instructions on how to remove unneeded items.

Reply | Quote

Hans,
Stripped down database and csv file. The only switchboard query that will work is related to the Address-pi,estate,trust as this is the only stripped down table I left in the database. THANKS.

Reply | Quote

I don’t see any place where you would actually need the ID field. Did you remove too much?

There could be a problem with the field names, however. Try the following:
– Import one of the .csv files manually, for example Access-address-pi,estate,trust.csv.
– Set everything the way you want.
– Instead of clicking Finish at the end, click Advanced. You’ll see all the settings.
– Click Save As…
– Specify a name, e.g. MySpec1 or whatever you like.
– Click OK twice.
– You don’t need to finish the import.
– Use the specification name as second argument to the DoCmd.TransferText instruction:

DoCmd.TransferText acImportDelim, “MySpec1”, “Access-address-pi,estate,trust”, “…Access-address-pi,estate,trust.csv”

– Repeat for the other files.

Reply | Quote

Hans,
You are indeed a magician!! It works! For those following this post, the final sub routine is:

Sub UpdateTables()
DoCmd.DeleteObject acTable, “Access-address list”
DoCmd.TransferText acImportDelim, “MySpec1”, “Access-address list”, “c:test_iqReName_access_filesAccess-address list.csv”
DoCmd.DeleteObject acTable, “Access-address-pi,estate,trust”
DoCmd.TransferText acImportDelim, “MySpec2”, “Access-address-pi,estate,trust”, “c:test_iqReName_access_filesAccess-address-pi,estate,trust.csv”
DoCmd.DeleteObject acTable, “Access-clientlist”
DoCmd.TransferText acImportDelim, “MySpec3”, “Access-clientlist”, “c:test_iqReName_access_filesAccess-clientlist.csv”
End Sub

I am grateful for your willingness to share your knowledge and, as importantly, your patience with those like me who are willing to stretch our skills and try things will beyond our current skill set—-as the experts, like you, can help us [me] when there is no hope to find a solution.

As I stated earlier, I am only a casual end-user of Access. I have never done any work in this application—for the brief exposure I have its been with basically menu driven Access applications in which someone else has done the heavy lifting. But when I inherited the updating of this database, I knew that there was a way to mechanize it. I now have this whole process running on a scheduler at 2AM every business morning….saving me 10-15 minutes a day….Doesn’t seem like much time, but I have found that by pursuing time savings, it does eventually pay bigger dividends. I also learned a significant amount of valuable information about Access/vba along the way! Again, thank you for willingness to share your TALENT! Hans, take a bow. Jim

Reply | Quote