excel 2000 append field error
 

Hi again!

Apparently, I'm not allowed to Append a field to my recordset. I've tried
changing the Cursortype to everything but Forward only. And I receive:

error 3219
Operation not allowed in this context

on the Append line in the code below.

ADO is required as the source will change after debugging.

Any advice greatly appreciated.

cheers,
Matt.
-------------------
Dim connDB As New ADODB.Connection
Dim rsWeeklyScrap As New ADODB.Recordset

Dim strDataPath As String
Dim strConnection As String
Dim strSQL As String

Dim intCurRow As Integer
Dim intCurCol As Integer

strDataPath = "\\hserver01\hworking\pcntrl\FORECAST\xAmcan2003.m db"
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = " &
strDataPath
connDB.Open strConnection

strSQL = "SELECT ProDate, SUM(ScrapQuantity) AS ScrapQuantity " _
& "FROM qryWeeklyStartupScrap " _
& "WHERE [Machine #] <= 14 " _
& "GROUP BY ProDate"

rsWeeklyScrap.CursorType = adOpenStatic
rsWeeklyScrap.CursorLocation = adUseClient
rsWeeklyScrap.Source = strSQL
rsWeeklyScrap.ActiveConnection = connDB
rsWeeklyScrap.Open

rsWeeklyScrap.Fields.Append "WeekNum", adInteger

excel 2000 append field error
 

What will you be doing with this appended rs field? Will this work for you:

SELECT
ProDate,
SUM(ScrapQuantity) AS ScrapQuantity,
1 AS WeekNum
FROM ...

"Matt." wrote in message ...
Hi again!

Apparently, I'm not allowed to Append a field to my recordset. I've tried
changing the Cursortype to everything but Forward only. And I receive:

error 3219
Operation not allowed in this context

on the Append line in the code below.

ADO is required as the source will change after debugging.

Any advice greatly appreciated.

cheers,
Matt.
-------------------
Dim connDB As New ADODB.Connection
Dim rsWeeklyScrap As New ADODB.Recordset

Dim strDataPath As String
Dim strConnection As String
Dim strSQL As String

Dim intCurRow As Integer
Dim intCurCol As Integer

strDataPath = "\\hserver01\hworking\pcntrl\FORECAST\xAmcan2003.m db"
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = " &
strDataPath
connDB.Open strConnection

strSQL = "SELECT ProDate, SUM(ScrapQuantity) AS ScrapQuantity " _
& "FROM qryWeeklyStartupScrap " _
& "WHERE [Machine #] <= 14 " _
& "GROUP BY ProDate"

rsWeeklyScrap.CursorType = adOpenStatic
rsWeeklyScrap.CursorLocation = adUseClient
rsWeeklyScrap.Source = strSQL
rsWeeklyScrap.ActiveConnection = connDB
rsWeeklyScrap.Open

rsWeeklyScrap.Fields.Append "WeekNum", adInteger

excel 2000 append field error
 

Well, my query should add a field to the recordset and populate it
with a value of 1 for all rows. You could then loop through the
recordset and change the value to whatever you want. If you can't call
an MS Access UDF from Excel (not even via a query? I'm surprised) then
I assume you'll have to recreate the function in Excel.

"Matt." wrote in message ...
The new field will be populated by a function called WeekNumber (from MS'
web site) calculating the Week number of date value in ProDate. MS
acknowledges the format "ww" contains a flaw and published the function as a
work around.

It is impossible to trigger an Access user defined function from within
Excel.

cheers,
Matt.

"onedaywhen" wrote in message
om...
What will you be doing with this appended rs field? Will this work for
you:

SELECT
ProDate,
SUM(ScrapQuantity) AS ScrapQuantity,
1 AS WeekNum
FROM ...

"Matt." wrote in message
...
Hi again!

Apparently, I'm not allowed to Append a field to my recordset. I've
tried
changing the Cursortype to everything but Forward only. And I receive:

error 3219
Operation not allowed in this context

on the Append line in the code below.

ADO is required as the source will change after debugging.

Any advice greatly appreciated.

cheers,
Matt.
-------------------
Dim connDB As New ADODB.Connection
Dim rsWeeklyScrap As New ADODB.Recordset

Dim strDataPath As String
Dim strConnection As String
Dim strSQL As String

Dim intCurRow As Integer
Dim intCurCol As Integer

strDataPath = "\\hserver01\hworking\pcntrl\FORECAST\xAmcan2003.m db"
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = " &
strDataPath
connDB.Open strConnection

strSQL = "SELECT ProDate, SUM(ScrapQuantity) AS ScrapQuantity " _
& "FROM qryWeeklyStartupScrap " _
& "WHERE [Machine #] <= 14 " _
& "GROUP BY ProDate"

rsWeeklyScrap.CursorType = adOpenStatic
rsWeeklyScrap.CursorLocation = adUseClient
rsWeeklyScrap.Source = strSQL
rsWeeklyScrap.ActiveConnection = connDB
rsWeeklyScrap.Open

rsWeeklyScrap.Fields.Append "WeekNum", adInteger

excel 2000 append field error
 

I have the function in Excel. I'm experiencing two difficulties. The first
is the Fields.Append function isn't working (I get error 3219). And I don't
know how to do is fire the query against the recordset. If you can help me,
please do so.

cheers,
Matt.

"onedaywhen" wrote in message
om...
Well, my query should add a field to the recordset and populate it
with a value of 1 for all rows. You could then loop through the
recordset and change the value to whatever you want. If you can't call
an MS Access UDF from Excel (not even via a query? I'm surprised) then
I assume you'll have to recreate the function in Excel.

"Matt." wrote in message
...
The new field will be populated by a function called WeekNumber (from
MS'
web site) calculating the Week number of date value in ProDate. MS
acknowledges the format "ww" contains a flaw and published the function
as a
work around.

It is impossible to trigger an Access user defined function from within
Excel.

cheers,
Matt.

"onedaywhen" wrote in message
om...
What will you be doing with this appended rs field? Will this work for
you:

SELECT
ProDate,
SUM(ScrapQuantity) AS ScrapQuantity,
1 AS WeekNum
FROM ...

"Matt." wrote in message
...
Hi again!

Apparently, I'm not allowed to Append a field to my recordset. I've
tried
changing the Cursortype to everything but Forward only. And I
receive:

error 3219
Operation not allowed in this context

on the Append line in the code below.

ADO is required as the source will change after debugging.

Any advice greatly appreciated.

cheers,
Matt.
-------------------
Dim connDB As New ADODB.Connection
Dim rsWeeklyScrap As New ADODB.Recordset

Dim strDataPath As String
Dim strConnection As String
Dim strSQL As String

Dim intCurRow As Integer
Dim intCurCol As Integer

strDataPath = "\\hserver01\hworking\pcntrl\FORECAST\xAmcan2003.m db"
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source =
" &
strDataPath
connDB.Open strConnection

strSQL = "SELECT ProDate, SUM(ScrapQuantity) AS ScrapQuantity " _
& "FROM qryWeeklyStartupScrap " _
& "WHERE [Machine #] <= 14 " _
& "GROUP BY ProDate"

rsWeeklyScrap.CursorType = adOpenStatic
rsWeeklyScrap.CursorLocation = adUseClient
rsWeeklyScrap.Source = strSQL
rsWeeklyScrap.ActiveConnection = connDB
rsWeeklyScrap.Open

rsWeeklyScrap.Fields.Append "WeekNum", adInteger

excel 2000 append field error
 

My apologies.

I would like to create totals of a few of the other fields in the recordset
by the WeekNumber (the WeekNumber is calculated using the UDF) using the SUM
and GROUP BY SQL functions.

An (aircode) example (after the Fields Append statement):
UPDATE rsWeeklyScrap
SET WeekNumber = WeekNumber(ProDate);

SET rsWeeklyScrap = SELECT WeekNumber, MIN(ProDate), MAX(ProDate),
SUM(ScrapQuantity) AS ScrapQuantity2
FROM rsWeeklyScrap
GROUP BY WeekNumber;

The Totals (ScrapQuantity2) will be written to relevant cells in the
worksheet.

My original intent was to do that with the SQL statement that creates the
recordset, but it is impossible to fire an Access UDF from outside Access.

If there is another approach, believe me, I'm all ears (well, eyes).
Thankfully, I'm juggling multipe projects (:-P), and I can work on others
while we're working this out.

Anxiously awaiting your reply,

Matt.


"onedaywhen" wrote in message
m...
Matt, I don't think adding a field to the recordset will solve your
problem (nor will mine, I fear!) But I don't know for sure because you
haven't said why you need it. Say you did successfully add a field to
the recordset, what next? You'd use your UDF to populate the new field
with values but what would you then do with the recordset? Believe me,
these questions are relevant.

"Matt." wrote in message
...
I have the function in Excel. I'm experiencing two difficulties. The
first
is the Fields.Append function isn't working (I get error 3219). And I
don't
know how to do is fire the query against the recordset. If you can help
me,
please do so.

cheers,
Matt.

"onedaywhen" wrote in message
om...
Well, my query should add a field to the recordset and populate it
with a value of 1 for all rows. You could then loop through the
recordset and change the value to whatever you want. If you can't call
an MS Access UDF from Excel (not even via a query? I'm surprised) then
I assume you'll have to recreate the function in Excel.

"Matt." wrote in message
...
The new field will be populated by a function called WeekNumber
(from
MS'
web site) calculating the Week number of date value in ProDate. MS
acknowledges the format "ww" contains a flaw and published the
function
as a
work around.

It is impossible to trigger an Access user defined function from
within
Excel.

cheers,
Matt.

"onedaywhen" wrote in message
om...
What will you be doing with this appended rs field? Will this work
for
you:

SELECT
ProDate,
SUM(ScrapQuantity) AS ScrapQuantity,
1 AS WeekNum
FROM ...

"Matt." wrote in message
...
Hi again!

Apparently, I'm not allowed to Append a field to my recordset.
I've
tried
changing the Cursortype to everything but Forward only. And I
receive:

error 3219
Operation not allowed in this context

on the Append line in the code below.

ADO is required as the source will change after debugging.

Any advice greatly appreciated.

cheers,
Matt.
-------------------
Dim connDB As New ADODB.Connection
Dim rsWeeklyScrap As New ADODB.Recordset

Dim strDataPath As String
Dim strConnection As String
Dim strSQL As String

Dim intCurRow As Integer
Dim intCurCol As Integer

strDataPath =
"\\hserver01\hworking\pcntrl\FORECAST\xAmcan2003.m db"
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source =
" &
strDataPath
connDB.Open strConnection

strSQL = "SELECT ProDate, SUM(ScrapQuantity) AS ScrapQuantity "
_
& "FROM qryWeeklyStartupScrap " _
& "WHERE [Machine #] <= 14 " _
& "GROUP BY ProDate"

rsWeeklyScrap.CursorType = adOpenStatic
rsWeeklyScrap.CursorLocation = adUseClient
rsWeeklyScrap.Source = strSQL
rsWeeklyScrap.ActiveConnection = connDB
rsWeeklyScrap.Open

rsWeeklyScrap.Fields.Append "WeekNum", adInteger

excel 2000 append field error
 

I don't think you can directly append a field to a recordset in this
kind of scenario. I have no real idea of the internal workings of a
recordset but I know it needs to maintain the 'schema' of the
datasource to be able to update it. Therefore, I'm not surprised you
can't mess with the schema by adding a field.

My example of adding a column in the query does create a field but it
is read only, again for reasons of schema integrity I assume.

So do you really need to append a field? You could instead use the UDF
to update your Excel worksheet, if that's the ultimate destination of
the data, rather than add it to the recordset first.

But if you really want the extra field, you could use data shaping.
Change your connection string to use the MSDataShape provider e.g.

strConnection = "Provider=MSDataShape;" & _
"Data Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = " & strDataPath"

Use the APPEND NEW syntax in your query e.g.

strSQL = "SHAPE {" & _
"SELECT ProDate, SUM(ScrapQuantity) AS ScrapQuantity " & _
"FROM qryWeeklyStartupScrap " & _
"WHERE [Machine #] <= 14 " & _
"GROUP BY ProDate" & _
"} APPEND NEW adDouble AS WeekNum"

The appended column should be read/write.

"Matt." wrote in message ...
My apologies.

I would like to create totals of a few of the other fields in the recordset
by the WeekNumber (the WeekNumber is calculated using the UDF) using the SUM
and GROUP BY SQL functions.

An (aircode) example (after the Fields Append statement):
UPDATE rsWeeklyScrap
SET WeekNumber = WeekNumber(ProDate);

SET rsWeeklyScrap = SELECT WeekNumber, MIN(ProDate), MAX(ProDate),
SUM(ScrapQuantity) AS ScrapQuantity2
FROM rsWeeklyScrap
GROUP BY WeekNumber;

The Totals (ScrapQuantity2) will be written to relevant cells in the
worksheet.

My original intent was to do that with the SQL statement that creates the
recordset, but it is impossible to fire an Access UDF from outside Access.

If there is another approach, believe me, I'm all ears (well, eyes).
Thankfully, I'm juggling multipe projects (:-P), and I can work on others
while we're working this out.

Anxiously awaiting your reply,

Matt.


"onedaywhen" wrote in message
m...
Matt, I don't think adding a field to the recordset will solve your
problem (nor will mine, I fear!) But I don't know for sure because you
haven't said why you need it. Say you did successfully add a field to
the recordset, what next? You'd use your UDF to populate the new field
with values but what would you then do with the recordset? Believe me,
these questions are relevant.

"Matt." wrote in message
...
I have the function in Excel. I'm experiencing two difficulties. The
first
is the Fields.Append function isn't working (I get error 3219). And I
don't
know how to do is fire the query against the recordset. If you can help
me,
please do so.

cheers,
Matt.

"onedaywhen" wrote in message
om...
Well, my query should add a field to the recordset and populate it
with a value of 1 for all rows. You could then loop through the
recordset and change the value to whatever you want. If you can't call
an MS Access UDF from Excel (not even via a query? I'm surprised) then
I assume you'll have to recreate the function in Excel.

"Matt." wrote in message
...
The new field will be populated by a function called WeekNumber
(from
MS'
web site) calculating the Week number of date value in ProDate. MS
acknowledges the format "ww" contains a flaw and published the
function
as a
work around.

It is impossible to trigger an Access user defined function from
within
Excel.

cheers,
Matt.

"onedaywhen" wrote in message
om...
What will you be doing with this appended rs field? Will this work
for
you:

SELECT
ProDate,
SUM(ScrapQuantity) AS ScrapQuantity,
1 AS WeekNum
FROM ...

"Matt." wrote in message
...
Hi again!

Apparently, I'm not allowed to Append a field to my recordset.
I've
tried
changing the Cursortype to everything but Forward only. And I
receive:

error 3219
Operation not allowed in this context

on the Append line in the code below.

ADO is required as the source will change after debugging.

Any advice greatly appreciated.

cheers,
Matt.
-------------------
Dim connDB As New ADODB.Connection
Dim rsWeeklyScrap As New ADODB.Recordset

Dim strDataPath As String
Dim strConnection As String
Dim strSQL As String

Dim intCurRow As Integer
Dim intCurCol As Integer

strDataPath =
"\\hserver01\hworking\pcntrl\FORECAST\xAmcan2003.m db"
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source =
" &
strDataPath
connDB.Open strConnection

strSQL = "SELECT ProDate, SUM(ScrapQuantity) AS ScrapQuantity "
_
& "FROM qryWeeklyStartupScrap " _
& "WHERE [Machine #] <= 14 " _
& "GROUP BY ProDate"

rsWeeklyScrap.CursorType = adOpenStatic
rsWeeklyScrap.CursorLocation = adUseClient
rsWeeklyScrap.Source = strSQL
rsWeeklyScrap.ActiveConnection = connDB
rsWeeklyScrap.Open

rsWeeklyScrap.Fields.Append "WeekNum", adInteger