Problems Exporting from Access

  • Thread starter Thread starter MJ
  • Start date Start date
M

MJ

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
 
First 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 said:
Table: OptEdit Errors, NEW isn't a valid table name.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


MJ said:
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
Click to expand...
Click to expand...
 
Dates 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

(no private e-mails, please)


MJ said:
First 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 said:
Table: OptEdit Errors, NEW isn't a valid table name.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


MJ said:
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
Click to expand...
Click to expand...
Click to expand...
 
MJ said:
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
Click to expand...
 
Back
Top