List of db objects date/time of last update

[RenaG]

Hi Bob,

At this point, I hope I am not going to have to add any fields to existing tables. We have worked hard to try to think of all the information we need. What I am adding is data in the tables. These are look-up tables. I am having to continue developing this as we add new programs. The programs we already have in place need this DB now so it has to go out as is. But as I add new programs, then some of the look-up tables will have data added. Plus new tables added. I hope that makes sense.

When you said to turn off the AutoCorrect option, I assume you mean all the options under that category: Track name..., Perform name..., Log name....

TTFN,
~RLG

 

[boblarson]

To add data in the tables, just link to the table that you have in the development version (it will add a 1 to the name) and then use an append query to append the new data between the two. Use an Update query if you need to update existing records. Then just delete the linked table when you are done. And I'm talking about linking backend to backend.

 

[RenaG]

Hi Bob,

I really like your suggestion to create an append query. I have tried this a couple different ways and can't get it to work. I have tried creating an append query in the production BE after copying the table from the development BE. I then tried creating an append query in the development BE that pointed to the table in the production BE. Both attempts yielded the same error when I tried to run the append query:

The INSERT INTO statement contains the following unknown field name: '[TypeName]'. Make sure you have typed the name correctly, and try the operation again.

I know it is typed correctly (especially as many times as I have tried it). Any thoughts on what I am doing wrong here?

~RLG

 

[boblarson]

Can't you just use the QBE grid to create the append query? No need to go writing it manually. It should work just fine. Also, why did you copy the table from the development backend. Just link to it and do the query and then unlink.

 

[RenaG]

HI Bob,

I am really confused. I am new to Access and not understanding your suggestions. How do you "use the QBE grid"? When ever I use the Linked Table Manager to link a table it just changes where the current table points to; not add another occurance. Sorry I am totally lost.

~RLG

 

[boblarson]

Sorry, I guess I may have omitted some information by assuming that you would have already done it.

When you create a new table you would need to, in the frontend, use the ImportLink of the table just like linking any table that you would if want to link to it. You would of course have already done this in the frontend because you had it ready to move to production. At least that is what I assumed. So I'm a bit confused as to what you have and what you have not done. So, from the beginning:

1. I find that I need to create some new tables in the backend and add some fields to others.
2. I create my tables and add fields to my tables in the development backend.
3. I import my new tables (by linking) into the development frontend and the new fields in the other tables should automatically be there in the linked tables which had existed previously. But if you are using any SQL Statements in queries or forms or reports which do not use the Select * FROM Table Name, then you would need to go modify those in the frontend for the new stuff to show up.
4. So once all of the development stuff is complete, I need to move my changes to production. I will use the method we talked about in the further steps.
5. I go to my production backend and then I add the fields manually to the tables which needed the new fields.
6. I then, while I am in my production database, import the new tables that didn't exist before (not link) from the development backend.
7. Next, I need to add the data to the new fields that were added to the existing tables, so I am going to need to use an UPDATE query instead of an Append query. Because the New tables didn't exist before they can just be imported to the backend from the development backend.
8. I will link the tables that have new fields but existing data in other fields by being in the production backend and then doing the link to those tables in the production database.
9. I then create the update by going to the Query QBE grid. You get there by clicking CREATE > QUERY DESIGN. I Add the tables - the one which is from the production db and the one from the development db.
10. Join the two tables using the PK field.
11. I select the fields from the production version table, which need to be updated, into the grid.
12. Then I change the query type to UPDATE (from going to the View menu or tab).
13. Next I will put the table name and field name from the linked development backend in the UPDATE spot in the grid like this: [TableName].[FieldName]
(changing to the actual table name and field. For example if I had linked my table named tblLookups from the dev backend it would be likely named tblLookups1 and let's say my field name is Preference, which I need to update. So I would put in the UPDATE spot in the QBE grid, it would look like:

Once I have done that for all of the fields I need to update, I will then unlink my dev backend from prod backend and then take my frontend and relink to the prod backend and THEN the linked table manager should work when you link to it from the frontend.

Just a reminder - the Linked Table Manager is ONLY for RELINKING tables that have ALREADY been linked.

 

[RenaG]

Wow, thank you for the detailed description! I have done a copy / paste into a Word document to keep this around. Unfortunately, I still have some confustion (please don't roll your eyes like that ).

First, I guess I haven't explained myself well enough. I am not adding fields to existing tables (at least not at this time). I am adding data only to existing fields in existing tables; as well as adding some new tables. I think I can use these steps; just tweak them a little to do an Append instead of an Update. But I get stuck on this step:

8. I will link the tables that have new fields but existing data in other fields by being in the production backend and then doing the link to those tables in the production (I think you meant development) database.

To "link to" only changes where the BE is pointing to. This is where I get confused. Do you mean import the table from the development BE into the production BE? Then set up the query. This is what I tried to do yesterday and got the error message I posted earlier. I noticed that your Linked Table Manager appears on your External Data tab in the Import & Link group. Mine appears on the Database Tools tab in the Database Tools group. They do the same thing, right?

~RLG

 

[boblarson]

First, I guess I haven't explained myself well enough. I am not adding fields to existing tables (at least not at this time). I am adding data only to existing fields in existing tables; as well as adding some new tables. I think I can use these steps; just tweak them a little to do an Append instead of an Update.

Okay, I see.

To "link to" only changes where the BE is pointing to.

You aren't understanding me. The LINK has nothing to do with the frontend at this point. I am telling you to do something which you would normally not do, but you would be going in to the production backend and then going through the process to link the same exact table (to do the append, or update if you were doing updates which you are not) that is in the DEVELOPMENT backend. You go to External Data tab (unless you are on 2003 or earlier and then it is to FILE > GET EXTERNAL DATA > LINK TABLES) and choose ACCESS from the IMPORT/LINK Tab. You then choose the datbase file (in this case it is the development backend because you are doing this from the production backend remember) and you choose LINK - not import, but LINK and then you go through and link the tables that you will need to do appends from. The new tables you would import, not link.

So once you have a linked table showing up in the production backend like this:

You see the arrow is my linked table from my development backend and the one with the same name (tblLookups) is the table that is inside my production backend. Right now we are dealing with backends only; no frontends.

Does that make sense?

And when you go to set up the query, are you using the wizard or are you going to design (the top option when you select NEW QUERY, if you are on Access 2003 or earlier)?

 

[RenaG]

Goooooood Morning!

I guess I needed the break of the weekend to finally see what you are talking about. Sorry I have been so dense; but the denseness is cracking!

What I hadn't noticed until this morning is that when I click on the External Data tab | Import group | Access option and it opens the Get External Data window that there is an Import option and a Link option! Imagine that - LOL!

So I think I got it now. The goal for today is to get this puppy pushed out to production!

Thanks for all your help! (that seems inadequate but it's all I got )

Cheers!
~RLG