Using SQL statement to append a recordset to a table

[Guest]

Using Access 2003 on Windows XP although most of the code was originally
Access 97

I have an Application.mdb front end that contects to a data.mdb. There are
many of these data.mdb's that are identical in structure but each is a
different scenario for a model. We have had Comparison form that allows us to
to select (not necessarily the one we are currently connected to) two
different data.mdb and ct which tables to compare and the create either a
summary or detailed report. One of the things we check for are records in db1
not in db2 and visa versa. For one of the data tables (120+ fields) I have
been asked to modify the reports so the "Indb1notdb2" and "Indb2notdb1"
records are copied to separate tables.
I have created two tables that are in the application.mdb.

Here are the relavent sections of code (which resides inside a function
called by the report buttons).

'-- here are the assignments near beginning of function
Set ws = CreateWorkspace("", "admin", "", dbUseJet)

Set db1 = ws.OpenDatabase(Me!cboMDB1, False)
Set db2 = ws.OpenDatabase(Me!cboMDB2, False)
Set td1 = db1.TableDefs(sTable)
'sTable is a string variable with current table name passed to function by
the form
Set td2 = db2.TableDefs(sTable)


' -- and later for recordsets
Set rs1 = td1.OpenRecordset(dbOpenTable)
Set rs2 = td2.OpenRecordset(dbOpenTable)


rs1.MoveFirst
rs2.MoveFirst

Do While Not rs1.EOF
' many lines of code for other functions of the code
If rs2.NoMatch Then
If sTable = "OPRRIG" Then
sSQL = "INSERT INTO tblOprrig_In1_Not_In2 SELECT FROM rs1.Value"
DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True

I've tried many variations for sSQL (like - rs1.Fields(*).value) none which
have come close to working. I know I have access to the rs1 as I've used it
for the report with code like

sTitle = rs1.Fields("Name").Value
sType = rs1.Fields("Type").Value

So any help would be appreciated

 

[Ken Snell [MVP]]

First, if the two tables are linked tables, you cannot use the dbOpenTable
argument for the OpenRecordset. Use dbOpenDynamic instead.

Second, I believe you need to change this line
sSQL = "INSERT INTO tblOprrig_In1_Not_In2 SELECT FROM rs1.Value"

to this line (assuming that rs1 has just a single field, and that the field
is numeric data type):
sSQL = "INSERT INTO tblOprrig_In1_Not_In2 SELECT " &
rs1.Fields(0).Value

 

[Guest]

Thanks Ken for your reply

However rs1 and rs2 recordsets are complete records not just a field in a
record.
The over all structure basically is (rs1 and rs2.Index = "PrimaryKey")

rs1.MoveFirst
rs2.MoveFirst
Do While Not rs1.EOF
"other code
If rs2.NoMatch Then 'is current record rs1 in rs2
' if true here is where I am trying to append record rs1 to a local table
"In1notIn2"
' more code for summary or detailed report
End If
rs1.MoveNext
Loop

In regards to dbOpenTable vs dbOpenDynamic - the following may make a
difference.
1) The current function is working fine (for 5-6 years and four versions of
Access).
2) All Data.mdb's are single user and have unique names. And only one
Data.mdb is linked at a time.
3) The comparison form is designed such that user can't go do anything else
so there is no other activity taking place with any of the linked tables.

Thanks again for your help and I hope this clarifies the problem.

First, if the two tables are linked tables, you cannot use the dbOpenTable
argument for the OpenRecordset. Use dbOpenDynamic instead.

Second, I believe you need to change this line
sSQL = "INSERT INTO tblOprrig_In1_Not_In2 SELECT FROM rs1.Value"

to this line (assuming that rs1 has just a single field, and that the field
is numeric data type):
sSQL = "INSERT INTO tblOprrig_In1_Not_In2 SELECT " &
rs1.Fields(0).Value

 

[Ken Snell [MVP]]

OK. Look at the SQL line that I provided:

sSQL = "INSERT INTO tblOprrig_In1_Not_In2 SELECT " &
rs1.Fields(0).Value


You'll note that I am using the value of the first field in the rs1
recordset as the value to put in the SQL statement.

Second, I see that I neglected to note that you need to specify the field
name for the tblOprrig_In1_not_In2 table that is receiving a value. The
general syntax for an append query is

INSERT INTO TableName
VALUES ( Field1, Field2, Field3 )
SELECT FieldOne, Field2, Field3
FROM AnotherTable;

or, when you have just a single record to insert and you have "constant"
values
for that record (such as you appear to be doing), you don't need
AnotherTable:

INSERT INTO TableName
VALUES ( Field1, Field2, Field3 )
SELECT 1, 2, 3;


Thus, the SQL step would be something like this:
sSQL = "INSERT INTO tblOprrig_In1_Not_In2 (FieldName) SELECT " &
rs1.Fields(0).Value

If you are writing more than one field into the record, then you'll need to
expand the SQL statement accordingly.
--

Ken Snell
<MS ACCESS MVP>

Thanks Ken for your reply

However rs1 and rs2 recordsets are complete records not just a field in a
record.
The over all structure basically is (rs1 and rs2.Index = "PrimaryKey")

rs1.MoveFirst
rs2.MoveFirst
Do While Not rs1.EOF
"other code
If rs2.NoMatch Then 'is current record rs1 in rs2
' if true here is where I am trying to append record rs1 to a local
table
"In1notIn2"
' more code for summary or detailed report
End If
rs1.MoveNext
Loop

In regards to dbOpenTable vs dbOpenDynamic - the following may make a
difference.
1) The current function is working fine (for 5-6 years and four versions
of
Access).
2) All Data.mdb's are single user and have unique names. And only one
Data.mdb is linked at a time.
3) The comparison form is designed such that user can't go do anything
else
so there is no other activity taking place with any of the linked tables.

Thanks again for your help and I hope this clarifies the problem.

 

[Guest]

Argh! - oh well I was hoping for some SQL "Wildcard" notation that would let
me insert into all the fields in tblOprrig_In2_Not_In2 all the values of the
current record of rs1.

So as a intermediate step I though if I could get one or two fields
populated in tblOprrig_In2_Not_In2 with the appropiate values from rs1 then I
could easily (with a lot of tying and proofreading) expand it to all the
fields/values. I first tried the Field "Priority" as it is the primary key.
I then spent the afternoon trying many variaitions, such as

sSQL = "INSERT INTO tblOprrig_In1_Not_In2 (Priority) SELECT " &
rs1.Fields(0).Value

-and the same all in quotes - and

sSQL = "INSERT INTO tblOprrig_In1_Not_In2 (Priority) SELECT
rs1.Fields("Priority").Value" <--- this one realy squawked took quote in ("P
as end of statement

Then tried the Field "Name" as I had already successfully read it into the
variable sTitle

sSQL = "INSERT INTO tblOprrig_In1_Not_In2 (Name) SELECT sTitle"
-and-
sSQL = "INSERT INTO tblOprrig_In1_Not_In2 (Name) SELECT Values(sTitle)"

all failed mean while each time my report was working fine (the record for
the table used in the report is created just a few lines down in the same
loop).

I also tried tablename[fieldname] notation and the tablename.fieldname
notation

Then I manually imported the OPRRIG table from the two Data.mdb's I had been
using in my testing as OPRRIG_Baseline and OPRRIG_Base265. Used the query
wizard "Find Unmatched Query Wizard" to create a query between these two
tables, changed to an append query, appending to the tblOprrig_In1_Not_In2
table (looking at the sql view along the way). It works!

Maybe I should be looking this route. Modifying the SQL replacing
OPRRIG_Baseline and OPRRIG_Base265 with the appropriate syntax for rs1 and
rs2 then use DoCMD to run the query (I know I would have to place it outside
the current loop to work - maybe set a flag on the first unmatched record
occurence inside the loop to test against when leaving).
Any thoughts on this

Thanks again - I've got a long weekend - will be back
Tuesday

 

[Ken Snell [MVP]]

To insert a value into the field Priority:

sSQL = "INSERT INTO tblOprrig_In1_Not_In2 (Priority) SELECT " & _
rs1.Fields("Priority").Value

You can use short version for inserting all fields from one table/query into
another, but not from the recordset itself. You'd need to store the
recordset into a table, and then run your append query using that table....
completely unnecessary overhead for what you're doing.

If you want to insert into Priority and Name fields:

sSQL = "INSERT INTO tblOprrig_In1_Not_In2 (Priority, [Name]) SELECT " & _
rs1.Fields("Priority").Value & ", " & rs1.Fields("Name").Value


Also, do not use Name as the name of a field. It and many other words are
reserved words in ACCESS, and can create serious confusion for ACCESS and
Jet. See these Knowledge Base articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


--

Ken Snell
<MS ACCESS MVP>

Argh! - oh well I was hoping for some SQL "Wildcard" notation that would
let
me insert into all the fields in tblOprrig_In2_Not_In2 all the values of
the
current record of rs1.

So as a intermediate step I though if I could get one or two fields
populated in tblOprrig_In2_Not_In2 with the appropiate values from rs1
then I
could easily (with a lot of tying and proofreading) expand it to all the
fields/values. I first tried the Field "Priority" as it is the primary
key.
I then spent the afternoon trying many variaitions, such as

sSQL = "INSERT INTO tblOprrig_In1_Not_In2 (Priority) SELECT " &
rs1.Fields(0).Value

-and the same all in quotes - and

sSQL = "INSERT INTO tblOprrig_In1_Not_In2 (Priority) SELECT
rs1.Fields("Priority").Value" <--- this one realy squawked took quote in
("P
as end of statement

Then tried the Field "Name" as I had already successfully read it into the
variable sTitle

sSQL = "INSERT INTO tblOprrig_In1_Not_In2 (Name) SELECT sTitle"
-and-
sSQL = "INSERT INTO tblOprrig_In1_Not_In2 (Name) SELECT Values(sTitle)"

all failed mean while each time my report was working fine (the record for
the table used in the report is created just a few lines down in the same
loop).

I also tried tablename[fieldname] notation and the tablename.fieldname
notation

Then I manually imported the OPRRIG table from the two Data.mdb's I had
been
using in my testing as OPRRIG_Baseline and OPRRIG_Base265. Used the query
wizard "Find Unmatched Query Wizard" to create a query between these two
tables, changed to an append query, appending to the
tblOprrig_In1_Not_In2
table (looking at the sql view along the way). It works!

Maybe I should be looking this route. Modifying the SQL replacing
OPRRIG_Baseline and OPRRIG_Base265 with the appropriate syntax for rs1 and
rs2 then use DoCMD to run the query (I know I would have to place it
outside
the current loop to work - maybe set a flag on the first unmatched record
occurence inside the loop to test against when leaving).
Any thoughts on this

Thanks again - I've got a long weekend - will be back
Tuesday

OK. Look at the SQL line that I provided:

sSQL = "INSERT INTO tblOprrig_In1_Not_In2 SELECT " &
rs1.Fields(0).Value


You'll note that I am using the value of the first field in the rs1
recordset as the value to put in the SQL statement.

Second, I see that I neglected to note that you need to specify the field
name for the tblOprrig_In1_not_In2 table that is receiving a value. The
general syntax for an append query is

INSERT INTO TableName
VALUES ( Field1, Field2, Field3 )
SELECT FieldOne, Field2, Field3
FROM AnotherTable;

or, when you have just a single record to insert and you have "constant"
values
for that record (such as you appear to be doing), you don't need
AnotherTable:

INSERT INTO TableName
VALUES ( Field1, Field2, Field3 )
SELECT 1, 2, 3;


Thus, the SQL step would be something like this:
sSQL = "INSERT INTO tblOprrig_In1_Not_In2 (FieldName) SELECT "
&
rs1.Fields(0).Value

If you are writing more than one field into the record, then you'll need
to
expand the SQL statement accordingly.