Hi everybody,

I have created a new table and want to populate the fields with records from another table. Also I am new to VBA I cannot believe I am the first person who wants to do this.
My attempts along the lines of

rec("date") = SELECT "Date" FROM "Other table" WHERE "Call ID" = intCount

or

rec("observer") = SELECT "observer" FROM "other table" WHERE "Call ID" = intCount

Are answered only by the programmes request for an expression.

Any thoughts how to get one table's value into another table.


Cheers

Golo

Hi,

   What are you trying to do here? Why don't you use an update query to push data from one table to another?

   If you want to use VBA, please explain your variables etc.

   In any event, you can't update a table the way you are showing here. You are trying to update a recordset like:

rec("date") = SQL Statement

   You can't do this. You need to create two recordsets and then update one recordset from the other, if you want to use VBA.

   You might try:

Open 1st Connection
Open 1st Recordset
Open 2nd Connection if not same database
Open 2nd Recordset

Update first Recordset
   Loop through second recordset
   Update
Loop

You can do something like this as well, which it looks like you are trying to do:

Open 1st Connection
Open 1st Recordset

Update 1st Recordset
   Open 2nd Connection
   Open 2nd Recordset
   Update
Loop

   But when you use the SELECT statement here in your recordset update, your recordset will have multiple fields and multiple reocrds, and you can't push them all into one field value (like "date").

   For example, you want to update the date field. You make a SELECT statement to the second table, and it pulls one record, which has the fields "PK", "LName", "FName", "Date" and then try to push all that into the "Date" field as below here. It won't work.

   You need to call the recordset in a seperate procedure, capture the individual values you want from the recordset as variables, and then push them like this:

1stRec("Date") = dt2ndRecDate

   Also, don't use reserved names like "Date" for field names.

   Post more information and we can help you do this. It is easy.

HTH


mmcdonal

hi
actually you have to many steps before

- you have to create current connection object
- create two record set
- assign each table to record set
- read from the first record set
- write to the secound record set
-

That's what I said/. There are two ways of doing that depending on the location of the second table (assuming the first is local, which it doesn't have to be.)


mmcdonal

Here's the easy way to do it:

DoCmd.RunSQL "select * into FirstTable from SecondTable where ID = " & intCount

Have fun!

Using DoCmd is very bad news.

1. You give control to Access which can do funny things that you don't know about until later
2. You get messages boxes that the user doesn't understand nor want to know about
3. You may not know if the query processed any records at all
4. You may not know if some inserts failed if an index was violated
(Big problem, when you are looking for that record you thought should be there)

Best to use
qdf.Execute dbFailOnError + RecordLocking or use some ADO

Point is check for Errors afterwards.
dbFailOnError fails if even a single record fails due to index violation.



Database Agreements

Well, there's more than one way to skin a cat. We don't use DAO in my organization, so this is out.

You can turn off the user warnings when using DoCmd with "DoCmd.SetWarnings False" and turn them back on with "DoCmd.SetWarnings True"

Anywho, I never use DoCmd when I can get away with it. I use ADO connections and recordsets, etc.

If you create an Append query, for example, you can just call it from your code. Of course you use the DoCmd, but it is the same as running the query manually.

You can also do something like place a counter in your code, and every time a record is passed in a loop, add 1 to your counter, then have a message box pop up at the end of the run stating how many records were passed to the database. I do this on one of my solutions where we grab data from lines in a text file. etc.

There are many ways around this. I prefer ADO, so can't use the old methods.

mmcdonal

P.S. ADO recordset allow you to control record locking and cursor characteristics, so funny things are less likely.

mmcdonal

This is an interesting discussion about DAO/ADO.
I haven't worked in ADO much, but from most every book that I've looked at including Wrox, I've come to the conclusion that ADO doesn't give the ease of use that DAO does.
Now, I've worked quite a bit in DAO, and I find the Record Locking quite sophisticated in it.
There's an old white paper on the locking in Access.
Of course in Access 2000, the DAO recordlocking was improved to row-level.
Of the most challenging tasks in MS Access is Multi-User Row Level locks, and there's were I believe you need really good error handling that cascades down to a stack.

With ADO, the material I've read said that you have to open up a new connection to get another type of RecordLock. I admit my understanding of this is insufficient, and practice is always where you learn the "real" stuff.

In DAO, you can lock however many records you like without having to open up a connection. Lock the Table Even.
This type of thing is documented in the Access 2003 VBA Programmers Reference.
I'd say this is a discussion for a different forum, but I can resist putting some points down.

I believe that ADO is optimized for SQL Server, and often used in ASP.
I believe its also the way Microsoft wants people to go.
But most people including myself who have used DAO for quite a while won't bother giving it up. Its not being improved by Microsoft that for sure.

I also want to say that I haven't satisfied my curiosity to find out what is really happenning in Access 2003/Access 2002 with the Access 2002 file format.

One other thing is that DAO/JET are I believe well glued to the .mdb file. I think that this is why ADO will never be able to do what it wants as with DAO.

Also, since Access is not a real client/server model, my believe is that cursors, record locking in ADO are not really that useful compared to DAO.



Database Agreements

As I was reading your reply, I kept thinking that I would have to post that I interact a lot with SQL Server, so there you go.

I do find, however, that these days you do have to go to each desktop to make sure that each user has a reference to DAO, if you're going to use it, and that it is referenced in the proper order. That is a chore.

Also, my understanding is that the locking and cursor are set with the recordset with ADO (that's when I set it) and not with the connection. I frequently re-use connections - but then again I use a lot of similar lock/cursor settings. Hmmm.

Anyway, I am moving towards more ASP and ADO.NET, so I guess that is my bias.

As I said, there are many ways to skin a cat (of course, there's no way to do it where the cat is going to like it.)


mmcdonal