importing spreadsheet to a database that's not the CurrentDB

[Howard]

I am trying to use DoCmd.TranferSpreadsheet to import a spreadsheet
into an Access table that's not the CurrentDB. I have the database
open, but I don't see how to tell the TransferSpreadsheet command that
the table is not in the CurrentDB. Is there a way to specify a
filename and table within the table string of the TransferSpreadsheet
command? Is there a way of temporarily changing the CurrentDB to be
the other database so the TransferSpreadsheet command will do what I
want? Any other ideas? Thanks.

[Steve Jorgensen]

On 7 Jan 2004 14:29:05 -0800, hh*****@harris.com (Howard) wrote:

I am trying to use DoCmd.TranferSpreadsheet to import a spreadsheet
into an Access table that's not the CurrentDB. I have the database
open, but I don't see how to tell the TransferSpreadsheet command that
the table is not in the CurrentDB. Is there a way to specify a
filename and table within the table string of the TransferSpreadsheet
command? Is there a way of temporarily changing the CurrentDB to be
the other database so the TransferSpreadsheet command will do what I
want? Any other ideas? Thanks.

Either create a link to the table in the other database, and import into that
(if importing into an existing table), or use Automation, to open another
instance of Access, and do the import using <application
instance>.DoCmd.TransferSpreadsheet.

[Rich P]

Just suggesting one more alternative: you could link a table from the
external mdb to your current mdb and use TransferSpreadsheet to the
linked table. And something even a little more reliable than that would
be to Transferspreadsheet to a local table in the current mdb and use
Insert Into to fill the linked table.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Howard]

Thanks for the help. I got it working now, but of course, now I have
another question...

I have a column in the spreadsheet that has mostly numbers but has a
couple of text entries at the end. The Access Table that I'm
importing this data into is defined to have all text fields. When I
do the import function (TransferSpreadsheet), I get an error (Numeric
Field Overflow). Now, if I change the text entries to numbers (I
don't mean the formating - I mean the actual values), then it works
fine.

It seems like the import function assumes that the spreadsheet field
is a number field (maybe because that's what was in all the previous
rows) and when it gets to the text data, it messes up. Shouldn't it
use the table definition from Access to determine how to import the
data from the spreadsheet? Changing the format of the spreadsheet to
all text fields had no affect.

Any ideas as to why this is happening? Any suggestions as to how to
fix it?

Rich P <rp*****@aol.com> wrote in message news:<3f***********************@news.frii.net>...

Just suggesting one more alternative: you could link a table from the
external mdb to your current mdb and use TransferSpreadsheet to the
linked table. And something even a little more reliable than that would
be to Transferspreadsheet to a local table in the current mdb and use
Insert Into to fill the linked table.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Pieter Linden]

Create an import specification, and set the field types there. Then
pass the specification name as an argument in the TransferSpreadsheet
command. Then it should use the spec and know that you intended that
field to be text.