Problem with append query

[Robert Rupe]

I am having a problem appending some data from a table in a remote db
to a table in the local db.

My code is:

Private Sub pvtRefreshSubDepts()

Dim strQuery2 As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection

On Error GoTo pvtRefreshSubDepts_Err

Set rs = New ADODB.Recordset

Set cn = New ADODB.Connection

cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.ConnectionString =
"DataSource='\\usflo1gtsfp01\Ifs_flo1_grp\IFS Learning
Maps\Database\LMS_10_Base.mdb'"

cn.Open

strQuery2 = "SELECT * FROM tblSubDept;"

rs.Open strQuery2, cn, adOpenStatic, adLockReadOnly, adCmdText

strQuery = "INSERT INTO tblSubDept (SubDeptIdx, SubDeptName,
DeptIdx) " & _
"SELECT rs.SubDeptIdx, rs.SubDeptName, rs.DeptIdx " & _
"FROM rs;"

DoCmd.RunSQL strQuery


When I run the SQL I get an error stating the system can't find rs as
a query or table. Yet I can loop through the records and assign the
data just fine using the rs designation for the recordset. Can anyone
see what is wrong with this?

 

[Neil Sunderland]

I am having a problem appending some data from a table in a remote db
to a table in the local db.

My code is: [snipped]

When I run the SQL I get an error stating the system can't find rs as
a query or table. Yet I can loop through the records and assign the
data just fine using the rs designation for the recordset. Can anyone
see what is wrong with this?

Recordsets aren't tables or queries, and you can't get at them from
SQL; they exist so that you can loop through the records returned by a
query one record at a time.

This might work better...
----------------------------------------------------------------------
Private Sub pvtRefreshSubDepts()
Dim strQuery2 As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection

On Error GoTo pvtRefreshSubDepts_Err

Set rs = New ADODB.Recordset

Set cn = New ADODB.Connection

cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.ConnectionString =
"DataSource='\\usflo1gtsfp01\Ifs_flo1_grp\IFS Learning
Maps\Database\LMS_10_Base.mdb'"

cn.Open

strQuery2 = "SELECT * FROM tblSubDept;"

rs.Open strQuery2, cn, adOpenStatic, adLockReadOnly, adCmdText

Do Until rs.EOF
strQuery = _
"INSERT INTO tblSubDept (SubDeptIdx, SubDeptName, DeptIdx) " & _
" VALUES (" & rs!SubDeptIdx & ", '" & rs!SubDeptName & "', " & _
rs!DeptIdx & ")
DoCmd.RunSQL strQuery
rs.MoveNext
Loop
----------------------------------------------------------------------
....but since all you appear to be doing is coping the existing data
from tblSubDept into tblSubDebt, it would surely be quicker using this
query:

SELECT * FROM tblSubDebt
UNION ALL
SELECT * FROM tblSubDebt

 

[Robert Rupe]

Neil,
Thanks for responding. You are correct that all I am doing is
basically updating the table from the server after truncating the old
one.

Your suggestion of using this:

SELECT * FROM tblSubDebt
UNION ALL
SELECT * FROM tblSubDebt

I am not too familiar with Union queries but wouldn't there be some
confusion on which table is which. I should probably rename one of
them so to avoid this. And if I can't get at the data through SQL and
I see no properties of the connection object that reference a table
how is the reference to be implemented?

While looping through the dataset will work in this particular table
there is one table that has many more records (>100,000) and it would
take a prohibitively long time to get through. My thinking was an
append would make one call across the network while a loop makes a
call for each record.

I am having a problem appending some data from a table in a remote db
to a table in the local db.

My code is: [snipped]

When I run the SQL I get an error stating the system can't find rs as
a query or table. Yet I can loop through the records and assign the
data just fine using the rs designation for the recordset. Can anyone
see what is wrong with this?

Recordsets aren't tables or queries, and you can't get at them from
SQL; they exist so that you can loop through the records returned by a
query one record at a time.

This might work better...
----------------------------------------------------------------------
Private Sub pvtRefreshSubDepts()
Dim strQuery2 As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection

On Error GoTo pvtRefreshSubDepts_Err

Set rs = New ADODB.Recordset

Set cn = New ADODB.Connection

cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.ConnectionString =
"DataSource='\\usflo1gtsfp01\Ifs_flo1_grp\IFS Learning
Maps\Database\LMS_10_Base.mdb'"

cn.Open

strQuery2 = "SELECT * FROM tblSubDept;"

rs.Open strQuery2, cn, adOpenStatic, adLockReadOnly, adCmdText

Do Until rs.EOF
strQuery = _
"INSERT INTO tblSubDept (SubDeptIdx, SubDeptName, DeptIdx) " & _
" VALUES (" & rs!SubDeptIdx & ", '" & rs!SubDeptName & "', " & _
rs!DeptIdx & ")
DoCmd.RunSQL strQuery
rs.MoveNext
Loop
----------------------------------------------------------------------
...but since all you appear to be doing is coping the existing data
from tblSubDept into tblSubDebt, it would surely be quicker using this
query:

SELECT * FROM tblSubDebt
UNION ALL
SELECT * FROM tblSubDebt

 

[Neil Sunderland]

I am not too familiar with Union queries but wouldn't there be some
confusion on which table is which. I should probably rename one of
them so to avoid this. And if I can't get at the data through SQL and
I see no properties of the connection object that reference a table
how is the reference to be implemented?

See Jamie's reply. Unlike me, he actually read the whole of your
original post!

 

[Robert Rupe]

Jamie,
Thanks for responding. On trying the your code I got an error message
of "Could not find installable ISAM" Any hints?

since all you appear to be doing is coping the existing data
from tblSubDept into tblSubDebt, it would surely be quicker using this
query:

SELECT * FROM tblSubDebt
UNION ALL
SELECT * FROM tblSubDebt

The OP said they were trying to add data from a remote db. Therefore,
the required query would be more like:

INSERT INTO tblSubDept (SubDeptIdx, SubDeptName, DeptIdx)
SELECT SubDeptIdx, SubDeptName, DeptIdx
FROM [MS Access;DATABASE= \\usflo1gtsfp01\Ifs_flo1_grp\IFS Learning
Maps\Database\LMS_10_Base.mdb;].tblSubDept;

Jamie.
(Also in Devon)

 

[Robert Rupe]

I left out the space in my code. I also tried putting the path in
single quotes in case there was a problem with the spaces in the path.
No joy, same error.

the required query would be more like:

INSERT INTO tblSubDept (SubDeptIdx, SubDeptName, DeptIdx)
SELECT SubDeptIdx, SubDeptName, DeptIdx
FROM [MS Access;DATABASE= \\usflo1gtsfp01\Ifs_flo1_grp\IFS Learning
Maps\Database\LMS_10_Base.mdb;].tblSubDept;

On trying the your code I got an error message
of "Could not find installable ISAM" Any hints?

That space before the file path is a typo.

Jamie.

 

[Robert Rupe]

Jamie,

I got it. I had also left out the space between MS and Access.
Wrong!

On a related matter, originally I tried to create a link from the
local DB to the remote. After a table was linked, no matter which
table I opened, the DB was contacting the remote for some reason and
it really slowed things down. Why would my local DB be linking up to
the remote when the table I wanted to open was strictly local?

I appreciate the help. Thanks.

Bob

I left out the space in my code. I also tried putting the path in
single quotes in case there was a problem with the spaces in the path.
No joy, same error.

the required query would be more like:

INSERT INTO tblSubDept (SubDeptIdx, SubDeptName, DeptIdx)
SELECT SubDeptIdx, SubDeptName, DeptIdx
FROM [MS Access;DATABASE= \\usflo1gtsfp01\Ifs_flo1_grp\IFS Learning
Maps\Database\LMS_10_Base.mdb;].tblSubDept;

On trying the your code I got an error message
of "Could not find installable ISAM" Any hints?

That space before the file path is a typo.

Jamie.