Problems Exporting from Access

Problems Exporting from Access

Post by TUo » Thu, 01 May 2008 15:19:03


I am having problems trying to export table data. My first desire was to
export three data tables to an "archive" database. Perhaps someone out there
can help me with my situation.

As said above I wanted to export these tables completely for record
purposes. I attempted to use two methods: TransferDatabase and CopyObject.
Below are examples of the code:

1) DoCmd.TransferDatabase acExport, , _
"H:\Data\M (M Ctr)\ProdRep\OptEdit Error Reporting Project\" & _
"OptEditError Archive.mdb", acTable, _
"Table: OptEdit Errors, NEW", _
"Table: OptEdit Errors, NEW " & _
Format([Table LU: DateSat].[CW], "yyyy mm dd")

2) DoCmd.CopyObject "H:\Data\M (M Ctr)\ProdRep\OptEdit Error " & _
"Reporting Project\OptEditError Archive.mdb", _
"Table: OptEdit Errors, NEW " & _
Format([Table LU: DateSat].CW, "yyyy mm dd"), , _
"Table: OptEdit Errors, NEW"

In each case, I get the following error... "<Database name> can't find the
field '|' referred to in your expression." I may be missing something, but
no where in this code are there any '|'s. I have even tried simplfying the
names and still get the same error message box.

As a result of these continued errors, I altered my plans and decided to try
exporting to spreadsheets into an archive directory instead. My first try
was simply to export the table into xls, then I tried creating a simple
select query to export to xls using the following methods:

3) DoCmd.TransferSpreadsheet acExport, 8, _
"Table: OptEdit Errors, NEW", _
"Table: OptEdit Errors, NEW " & _
Format([Table LU: DateSat].CW, "yyyy mm dd") & ".xls", True, ""

4) DoCmd.TransferSpreadsheet acExport, 8, _
"Qry-Weekly Rpt: OptEdit Errors, NEW", _
"OptEdit Errors NEW " & _
Format([Table LU: DateSat].CW, "yyyy mm dd") & ".xls", True, ""

Unfortunately, I have gotten the same error results.

Hopefully one of you out there will be able to help me out of my predicament.

Thanks in advance for your help

--

MJ
 
 
 

Problems Exporting from Access

Post by Douglas J. » Fri, 02 May 2008 06:12:16

Table: OptEdit Errors, NEW isn't a valid table name.

--
Doug Steele, Microsoft Access MVP
http://www.yqcomputer.com/
(no private e-mails, please)

 
 
 

Problems Exporting from Access

Post by TUo » Sat, 03 May 2008 05:05:01

irst of all, the name chosen may create some issues having to do with
exporting, it is not the problem of this error issue, but thank you for your
help there. I was already looking at it.

After doing some further research and testing, it appears that the REAL
problem is in how I am passing a DATE variable and the "/" in the date is
most likely the culprit causing the "<Database name> can't find the field '|'
referred to in your expression."

Once I get past reconciling a year's worth of back data I can handle the
file naming convention using "Now()" to give me the date input. Until I get
to that point, I will picking up my date value from a lookup table where it
is stored as a SHORT DATE.
In my code I am using Format([Table LU DateSat].[CW], "yyyy mm dd") to
create a string to append to a name in exporting. Even after doing some
tweaking on the table name, I am still hitting this bump in the road.

Is there a better way to store a date value and use it in this manner for
naming export files?

Thanks again,
MJ
--

MJ


"Douglas J. Steele" wrote:

 
 
 

Problems Exporting from Access

Post by Douglas J. » Sat, 03 May 2008 06:25:20

ates aren't stored with any particular format: they're 8 byte floating
point numbers, where the integer portion represents the date as the number
of days relative to 30 Dec, 1899, and the decimal portion represents the
time as a fraction of a day. The format you're using in the call to the
Format function will ensure that no slash is present. If you're concerned,
assign the file name to a variable and check what's in the variable.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"MJ" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

Problems Exporting from Access

Post by quinter » Wed, 28 May 2008 04:34:35


"MJ" < XXXX@XXXXX.COM > escribiè´¸ en el mensaje de
noticias: XXXX@XXXXX.COM ...