excel 2000 append field error

[Matt.]

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.mdb"
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

 

[onedaywhen]

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 ...

 

[onedaywhen]

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.

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.

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." <[email protected]> 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.mdb"
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

 

[Matt.]

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.

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." <[email protected]> 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.

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." <[email protected]> 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.mdb"
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

 

[Matt.]

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.

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." <[email protected]> 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.

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." <[email protected]> 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.

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." <[email protected]> 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.mdb"
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

 

[onedaywhen]

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.

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.

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." <[email protected]> wrote in message (from
MS'

function
as a

for
you: I've
tried

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