Append Filtered records to table

[Guest]

I would like to append filtered records to a table. I have researched a
couple options and am still unclear as to how to accomplish either. However,
I have researched the recordset property and transferdatabase. I have the
filtered records in a subform. Help appreciated,

ryan

 

[Arvin Meyer [MVP]]

You can write a piece of code which builds a recordset, and then walks
through the recordset adding each record to the table. Or you can do it
quite a bit easier by building a query that matches the filtered records in
the subform and appends them to the table.

If you already have the records though, why do you need to append them to
another table?

 

[Guest]

Well I am building a data dictionary in access based on the fields contained
in our Oracle databases. So I am creating a form that compares the Oracle
vs. Access tables and having a button to import if they don't exist in the
datadictionary yet. So, three questions:

1. Do you suggest having an query query built using the queries tab on the
DB or build the query in the VB code?

2. I can access my subform sheets .Recordset property, is that not full
already with the filtered results or do I have to build it somehow? If needs
bult, pointing me to a reference would be appreciated.

Also, i'm not sure if you are familiar with access/oracle but I was trying
to have a query that used the ODBC connection to pull a query from my oracle
so that its always up to date. I can get it to work but when incorporated
into the subform it prompts to select for the DNS connection profile about 4
times before the form displays and sometimes it will prompt in the middle of
viewing the form and connection fails.
3. I guess my question for this is if you can tie a DNS profile to a query
so that it won't prompt the user and make the connections behind the scene
persay?

Thankyou,

 

[Arvin Meyer [MVP]]

Well I am building a data dictionary in access based on the fields
contained
in our Oracle databases. So I am creating a form that compares the Oracle
vs. Access tables and having a button to import if they don't exist in the
datadictionary yet. So, three questions:

1. Do you suggest having an query query built using the queries tab on
the
DB or build the query in the VB code?

If I'm updating tables once, I usually use queries. If I'm building
something for users to use on a continuous basis, I use VBA code with an SQL
statement (instead of walking recordsets, which is much slower)

2. I can access my subform sheets .Recordset property, is that not full
already with the filtered results or do I have to build it somehow? If
needs
bult, pointing me to a reference would be appreciated.

It exists, but it is subclassed, so you need to change how you refer to it
depending upon where you are. Form inside the subform, use the same syntax
as you would use with a form, but from the main form, or from another form
use:

[Forms]![MainFormName]![SubformControlName].Form.PropertyName

[Forms]![MainFormName]![SubformControlName].Form![NameOfControlOnSubform]

Also, i'm not sure if you are familiar with access/oracle but I was trying
to have a query that used the ODBC connection to pull a query from my
oracle
so that its always up to date. I can get it to work but when incorporated
into the subform it prompts to select for the DNS connection profile about
4
times before the form displays and sometimes it will prompt in the middle
of
viewing the form and connection fails.
3. I guess my question for this is if you can tie a DNS profile to a
query
so that it won't prompt the user and make the connections behind the scene
persay?

I much prefer SQL-Server. In fact I hate working with Oracle so much so that
I've refused contracts since 1998. That said, ODBC connections with either
should be similar. With SQL-Server, I use integrated (with Windows)
security, but you can also use separate ODBC connections to link to all the
tables that you plan to use. Once doing that (at startup) you should not be
reprompted for your credentials.