Transfer Spreadsheet acExport - Table with added column won't export

[Walkabout via AccessMonster.com]

I have developed an addition to a data report system that from which the user
may choose multiple reports that have been set up by the user, and as the
multi-selected reports choosen are run, all reports that have the same fields
are appended to a data table the user gets to name.

I added check boxes so the user could also run STD3 Deviation and Medians on
these reports (if they chose to save the results to a table/spreadsheet), on
each record added to the table, from the reports the user chose to run and
have been appended to the data table.

If the user chooses to do this, the needed fields are added to the table that
was created if the user checked the box to save the reports to a
table/spreadsheet. This is done by using the alter table command to add the
fields that are then caculated through functions and populated to each record
by its Report ID (and other fields, for each record):

If DoesFieldExist("RtnsSTD3") <> True Then
db.Execute _
"ALTER TABLE [" & NewTblName & "] ADD COLUMN RtnsSTD3 text(30)", _
dbFailOnError
End If

What goes wrong is the Transfer Spreadsheet command below does not work when
the table is altered and a field is added, but it does work if no fields have
been added.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, [OldQueryName],
[MyFile]

The Error message I receive is:

The Microsoft Jet Database Engine could not find the Object''. Make sure the
object exists and that you spell it's name and the path name. - But when I do
a watch on OldQueryName and MyFile the both have the correct values in them.
If I have not added the new columns, it copies the table to the Spreadsheet,
no problem. What gives here with the empty quotes?

Thanks in Advance,

Walkabout

 

[Ken Snell \(MVP\)]

Don't use square brackets in the TransferSpreadsheet action. Those arguments
are supposed to be text strings. (Perhaps those are field names for the
fields that contain text strings?)

What does "does not work" mean -- you get an error? you get no file? you get
a file with the wrong data in it?

Does the query that you're exporting include the new field in its field
list?

 

[Walkabout via AccessMonster.com]

Ken,

Thanks for your reply. I took your advice and added the brackets to the
variables that hold the table name and the export to filename. It is still
not exporting the table. Tha table has been created and populated with the
additional fields and calculations - and now I get his message:

Run time Error 3027: Cannot Update. Database Object is Read-Only.

Thanks Again for your help here Ken. Im looking into the error message 3027.
It looks as though I must find a way make the recordset updatable after
altering the table, before I export it.

Walkabout

 

[Ken Snell \(MVP\)]

This error usually means that there is a problem with the path or file name
for the EXCEL file -- path doesn't exist, path + filename string is too
long, file extension is invalid for EXCEL file type (needs to be .xls), you
don't have privileges to the folder where you want to put the file, etc.

Post the entire VBA code for what you're doing so that we can see the whole
picture.

 

[Walkabout via AccessMonster.com]

Ken,

As I replied the first time, when the user chooses to not add STD3 or Medians,
the export worked fine - until I added the brackets, that gives me the "Not
Updatable" message. Here is the sub for that (Without brackets or brackets
being added to strings):

sub MakeXLSMeasure
dim MyPath
Mypath = CurrentProject.path
dim QueryName as string
dim OldQueryName as string
dim MyFile as string

OldQueryname = QueryName

Queryname = Replace(QueryName, " ", "")
-- There are replacements for each character that is not acceptable in an
Excle name.

MyFile = Mypath & "\" & QueryName & "" & ".xls"
docmd.transferspreadsheet acexport, acspreadsheettypeexcel9, oldQueryName, My
File

End sub

I removed the brackets from QueryName and MyFile strings. Doing it this way,
it works when the the table to export has not been altered, but fails when
fields have been added and gives me the orginal message of The Microsoft
Database Engine could not find the object ''.....

Thanks Again Ken!

This error usually means that there is a problem with the path or file name
for the EXCEL file -- path doesn't exist, path + filename string is too
long, file extension is invalid for EXCEL file type (needs to be .xls), you
don't have privileges to the folder where you want to put the file, etc.

Post the entire VBA code for what you're doing so that we can see the whole
picture.

Ken,

[quoted text clipped - 13 lines]

Walkabout

 

[Ken Snell \(MVP\)]

I don't see the code where you're altering the table? Please, show us the
entire code so that we can follow all the steps.
--

Ken Snell
<MS ACCESS MVP>

Ken,

As I replied the first time, when the user chooses to not add STD3 or
Medians,
the export worked fine - until I added the brackets, that gives me the
"Not
Updatable" message. Here is the sub for that (Without brackets or
brackets
being added to strings):

sub MakeXLSMeasure
dim MyPath
Mypath = CurrentProject.path
dim QueryName as string
dim OldQueryName as string
dim MyFile as string

OldQueryname = QueryName

Queryname = Replace(QueryName, " ", "")
-- There are replacements for each character that is not acceptable in an
Excle name.

MyFile = Mypath & "\" & QueryName & "" & ".xls"
docmd.transferspreadsheet acexport, acspreadsheettypeexcel9, oldQueryName,
My
File

End sub

I removed the brackets from QueryName and MyFile strings. Doing it this
way,
it works when the the table to export has not been altered, but fails when
fields have been added and gives me the orginal message of The Microsoft
Database Engine could not find the object ''.....

Thanks Again Ken!

This error usually means that there is a problem with the path or file
name
for the EXCEL file -- path doesn't exist, path + filename string is too
long, file extension is invalid for EXCEL file type (needs to be .xls),
you
don't have privileges to the folder where you want to put the file, etc.

Post the entire VBA code for what you're doing so that we can see the
whole
picture.

Ken,

[quoted text clipped - 13 lines]

Walkabout

 

[Walkabout via AccessMonster.com]

Ken,

That was in the first message I sent out. Here's the sub that does the
medians:

Private Sub ARMedians()
Dim SQlStr As String
Dim Rst As Recordset
Dim MedianTmp As Single
Dim StrMedainUpdate
Dim Recs As String
Dim NewTblName As String
Dim Tbl As Recordset
'Dim db As Database
Dim dbMedian As New TableDef
Dim fld As Field
Dim strRptFilter As String
Dim strBodFilter As String
Dim Code As String
Dim Subcode As String
Dim tmp1 As String
Dim db As Database
Dim StrFYDateChk As String
Dim TmpMedianField As String
Recs = AllRptOpts.strRptTitle
NewTblName = Me.SaveToSSFileName
Set db = CurrentDb()
If UCase(AllRptOpts.strRptCat) <> "STRATIFICATIONS" And UCase(AllRptOpts.
strRptCat) <> "LIST REPORTS" Then
If DetailTableExists() = True Then
If DoesFieldExist("Median") <> True Then
db.Execute _
"ALTER TABLE [" & NewTblName & "] ADD COLUMN Median single", _
dbFailOnError
End If
If DoesFieldExist("MedianField") <> True Then
db.Execute _
"ALTER TABLE [" & NewTblName & "] ADD COLUMN MedianField text(30)", _
dbFailOnError
End If
Set db = Nothing
Dim tmp2 As String
tmp2 = "SELECT " & "[" & NewTblName & "].*, SavedRptsTemp.* FROM [" &
NewTblName & "] INNER JOIN SavedRptsTemp ON [" & NewTblName & "].AllRptNum =
SavedRptsTemp.intrptnum;"
Set Rst = CurrentDb.OpenRecordset("SELECT [" & NewTblName & "].*,
SavedRptsTemp.* FROM [" & NewTblName & "] INNER JOIN SavedRptsTemp ON [" &
NewTblName & "].AllRptNum = SavedRptsTemp.intrptnum;")
Do Until Rst.EOF
With Rst
AllRptOpts.AllRptNum = !AllRptNum
VarSetUp
FYDateChk
glsys.strCurCycle = !Prod_Cycle
AllRptChgSQLDbs
SetRptFilter
SetMedianField (OptMedian)
If IsNull(!Code) = True Or IsEmpty(!Code) = True Or !Code = "" Then
AllRptOpts.strRptFilter = !strRptFilter
Else
If !Field <> "RtnCategory" Then
Code = "'" & !Code & "'"
Else
Code = !Code
End If
If IsNull(AllRptOpts.strRptFilter) Then
AllRptOpts.strRptFilter = !Field & " = " & Code
Else
If IsNull(Code) = True Then
AllRptOpts.strRptFilter = AllRptOpts.strRptFilter
Else
AllRptOpts.strRptFilter = AllRptOpts.strRptFilter & " and " &
!Field & " = " & Code
End If
End If
End If

If IsNull(!Subcode) = True Then
AllRptOpts.strRptFilter = AllRptOpts.strRptFilter
Else
If !Subfield <> "RtnCategory" Then
Subcode = "'" & !Subcode & "'"
Else
Subcode = !Subcode
End If
AllRptOpts.strRptFilter = AllRptOpts.strRptFilter & " and " & !
Subfield & " = " & Subcode
End If
glsys.strCurCycle = !strCurCycle

TmpMedianField = AllRptOpts.StrMedianField
MedianTmp = Median("ccdb", AllRptOpts.StrMedianField, 2)
.Edit
!Median = MedianTmp
.Update
End With
Rst.MoveNext
Loop
Rst.Close
Else
MsgBox "Standard Three Deviation Reports Can Only Be Run on Tables Generated
From Detail Reports."
Exit Sub
End If
tmp1 = "Update [" & NewTblName & "] set MedianField = '" & TmpMedianField &
"' Where MedianField is Null;"
DoCmd.RunSQL (tmp1)
Else
MsgBox "Median Reports For Each Record of a generated table, Can Only Be Run
on Tables Generated From Detail, NonTaxable and Claim Reports."

End If
End Sub
Fred

I don't see the code where you're altering the table? Please, show us the
entire code so that we can follow all the steps.

Ken,

[quoted text clipped - 50 lines]

 

[Ken Snell \(MVP\)]

Thanks ... though this code does not show the relationship for the
TransferSpreadsheet action.

Does the query that you're exporting include the new fields that you added
to the table? Are you using the same query name for when the table has the
extra fields and for when it doesn't?
--

Ken Snell
<MS ACCESS MVP>

Fred

I don't see the code where you're altering the table? Please, show us the
entire code so that we can follow all the steps.

Ken,

[quoted text clipped - 50 lines]

Walkabout

 

[Walkabout via AccessMonster.com]

Ken,

Thanks for your effort so far. Could you allow me to paste in my next
message? I attempted to send you a reply yesterday, but it would't go
through. I copied it out and pasted it in word, so if I could paste that in
a reply it would help immensely.

Thanks Again,

Fred

Thanks ... though this code does not show the relationship for the
TransferSpreadsheet action.

Does the query that you're exporting include the new fields that you added
to the table? Are you using the same query name for when the table has the
extra fields and for when it doesn't?

Fred

I don't see the code where you're altering the table? Please, show us the
entire code so that we can follow all the steps. [quoted text clipped - 3 lines]

Walkabout

 

[Ken Snell \(MVP\)]

I'm not understanding your question about "pasting" a reply? Are you wanting
to include some graphics or other special formatting in your reply? The
microsoft newsgroup supports plain text, but I don't know that it will
support HTML (which I believe you'd need in order to do what you're
asking?)?