Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old October 2nd, 2005, 01:08 AM
Registered User
 
Join Date: Oct 2005
Location: Canberra, ACT, Australia.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Populate a table with records from another set

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


Reply With Quote
  #2 (permalink)  
Old October 3rd, 2005, 08:48 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old October 3rd, 2005, 09:11 AM
Registered User
 
Join Date: Oct 2005
Location: amman, , Jordan.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
-

Reply With Quote
  #4 (permalink)  
Old October 3rd, 2005, 11:19 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old November 9th, 2005, 04:30 PM
Registered User
 
Join Date: Nov 2005
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Here's the easy way to do it:

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

Have fun!

Reply With Quote
  #6 (permalink)  
Old November 9th, 2005, 05:55 PM
Authorized User
 
Join Date: Sep 2004
Location: Nanaimo, BC, Canada.
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #7 (permalink)  
Old November 10th, 2005, 08:43 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #8 (permalink)  
Old November 10th, 2005, 08:44 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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

mmcdonal
Reply With Quote
  #9 (permalink)  
Old November 10th, 2005, 01:59 PM
Authorized User
 
Join Date: Sep 2004
Location: Nanaimo, BC, Canada.
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #10 (permalink)  
Old November 10th, 2005, 02:42 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Populate a List Box with Table Names & Table date hewstone999 Access VBA 1 February 27th, 2008 10:10 AM
Auto-populate table data into form jeff394 Access VBA 2 April 19th, 2006 03:55 PM
Populate a Variable From Access Table sirmilt Access 5 March 21st, 2006 01:34 AM
populate data from Access Table into datagrid bisigreat VB How-To 6 August 17th, 2004 07:58 AM
Listbox doesn't populate all the records edcaru Access 1 April 25th, 2004 11:32 AM



All times are GMT -4. The time now is 05:50 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.