Do you have to "open" a table to programmatically add records to it?

jread

Registered User.
Local time
Today, 07:03
Joined
Dec 10, 2007
Messages
12
This is more of a general question (before I start trying to figure out how to do this in VBA) but do you have to actually "open" a table to add a new record or search the records it contains? For instance, if I have a form that is bound to one table and I want to create new records in *other* tables based on what the user enters into the form, do I have to actually open those tables (display them onscreen) or what? I'm working on an older Access application and I noticed that it does this: open table, add new record with appropriate data, close table. This seems silly to me.
 
jread,

No, you can always do something like:

Code:
DoCmd.RunSQL "Insert Into SomeOtherTable(FieldA, FieldB) " & _
             "Values(" & Me.FieldA & ", " & Me.FieldB & ");"

Wayne
 
Excellent! Thank you very much for the help.

So basically it is a programmatic update query?
 
jread,

Yes, it's a programmatic Action query; can be Delete/Insert/Update.

Wayne
 
One more question about this:

Will "DoCmd.RunSQL Insert Into" automatically create a new record in the table it is writing to? If so, how would I then write a value (incremented key field) back to the table the form is bound to?

This whole thing is complicated as hell. Basically, we have something like this (very simplified):

- Table1 has fields: ID (increments, no dups), ProjectNumber, UserName, IDTable2

- Form bound to Table1

- Table2 has fields: ID (increments, no dups), ProjectNumber, UserName, IDTable1

- User opens form bound to Table1 and enters ProjectNumber and UserName

- Values entered in form are also added to Table2: ProjectNumber, UserName and ID# from the new record in Table1 (IDTable1)

- Table1 also needs the ID number from new record just created in Table2, so it gets that number and puts it in (IDTable2)


How would something like this be done with DoCmd.RunSQL?
 
- Values entered in form are also added to Table2: ProjectNumber, UserName and ID# from the new record in Table1 (IDTable1)
Why?

You should not be storing data in two different tables. It breaks the rules of relational database design.

Perhaps you could explain why you think you need to do this so that we can provide a solution that follows the rules.
Chris
 
Why?

You should not be storing data in two different tables. It breaks the rules of relational database design.

Perhaps you could explain why you think you need to do this so that we can provide a solution that follows the rules.
Chris

Yes, I completely agree with you that this is not the right thing to do. The problem is that I am redesigning a legacy Access application that was horribly designed and I'm kind of limited as to what I can do without risking the loss of information. The original was built this way and its functionality relies on it, so I am trying to get it all to work. I guess that if there is a way to "fix" it, though, I am all ears :)
 

Users who are viewing this thread

Back
Top Bottom