To link or to import, that is the question (second post)

[Guest]

Hi all,
I have an Excel spreadsheet that comes in via e-mail that is created from a
query at the other end (two different entities, no network connection). When
I download the file it goes into "My Documents" and overwrites the old file.
For convenience I have it linked into the database on my PC, but linking it
causes some of the date fields to show up as !NUM#. This is not happening in
all of the date fields, only two. When I look at the spreadsheet nothing
seems out of the ordinary. When I do an import of the file I don't have this
problem. Also, when I run and Append query I am getting a "Validation Rule"
error for all records, which shouldn't happen because the source table from
the satelite Db is a copy of the destination table.

Seems like this should be relatively straight forward, but it's not...
NickX

 

[Guest]

Whether to Link or Import is always a question. Sometimes one is better and
sometimes the other is. My preference is to link when possible. I usually
have the best luck if I convert the spreadsheet to a csv file, then define an
import specification, and link the spreadsheet. When you define an import
specification, you can define the fields' data types and change the field
names if necessary. You cannot do that with a spreadsheet.

 

[Guest]

Thank you for your response.

I need to be able to send this in an e-mail. This is what I have:

Dim yadda yadda As yadda

DoCmd.SendObject acSendQuery, ObjectName:=strDocName,
outputformat:=acFormatTXT, To:=strEmail, Bcc:=strEmailBcc,
Subject:=strMailSubject

Is there anyway to format the TXT file so that it is comma delimited instead
of fixed width?

 

[Guest]

Is there anyway to format the TXT file so that it is comma delimited instead

of fixed width?

Is there anyway to do this programatically?

 

[Guest]

I really don't know for sure, but you might try this. Save your spreadsheet
in csv format, the use
outputformat:=acFormatXLS

 

[hawa del]

Whether to Link or Import is always a question. Sometimes one is better and
sometimes the other is. My preference is to link when possible. I usually
have the best luck if I convert the spreadsheet to a csv file, then define an
import specification, and link the spreadsheet. When you define an import
specification, you can define the fields' data types and change the field
names if necessary. You cannot do that with a spreadsheet.