Linked Excel File

[Guest]

I noticed that during the link wizard, you don't have a chose of defining
field types for the excel columns. I have a Grade column that is mostly
numbers, but has a few text too. When linking the file, Access automatically
made that field number. So all of the records with text became #NUM!.

Can someone tell me how to make it so Access will know it's text? In the
original excel file, it's set to text.

I've tried to save the excel as htm, the grade column came over fine, but
now the phone number gets the error...because there are dashes and Access
assumes it's a number field.

 

[Granny Spitz via AccessMonster.com]

Can someone tell me how to make it so Access will know it's text? In the
original excel file, it's set to text.

Either convert the file to a CSV and link to that (you'll get to define the
column types) or make sure that at least 5 of the first 8 records in that
column contain text. Access examines the first 8 records and assigns the
majority data type to the Jet linked table. If they're mostly numbers in
those first 8 records, you get a number column even if all the other records
contain text in that column.

 

[Guest]

The wizard used when linking an Excel file utilizes the top 2 rows to
determine field names and values. If the first row is field headings it uses
the values in the second row to determine what the field type of each column.
So if the column has a value in the first data row, and then in record 2 it
has a text value, the column will be treated as numeric.

You could populate the first row of you Excel table with the correct value
type for each column, or you could be consistent in how you enter your values
in Excel.

Another workaround is to create a table in Access, link to your Excel file
and then use an append query to append the Excel data to your table.

 

[Guest]

I did partly the second poster's work around of importing the file first, set
the formatting how I want it. The user can open the excel file, do the copy
command on all the fields. Then the user goes back to the access table,
highlights all of the fields and does the paste command.

I need to override existing data so this workaround would do that.

As for your option on turning it into csv or even a delimited text file,
that works fine for me, except when I go to update my existing Access table
with data from the linked csv or txt file. I get an error saying that
updating isn't available with this ISAM.

When I read the help file, it talks about not being able to update linked
text file, but I'm not doing that so I don't know why I get the error.

 

[Granny Spitz via AccessMonster.com]

When I read the help file, it talks about not being able to update linked
text file, but I'm not doing that so I don't know why I get the error.

A linked CSV file *is* a linked text file, so you won't be able to edit the
existing records in that file. But if you have either Access 2002 or 2003
and the latest service packs, you couldn't edit those records in the XLS file
anyway. Does that mean you're using an old version of Access, or do you not
have the latest service pack installed on your computer?

I need to override existing data

It depends on *where* you're getting the data from, but you may be able to
keep the linked CSV table. If it's coming from data in another (linked?)
table, when you need to overwrite the existing data you can delete the CSV
file, then use a query to recreate the file. (Don't try to update/append the
data in the linked table itself, just the file.) The linked table will show
what's in the file.

The syntax for creating the CSV file is this:

SELECT FirstName, LastName, DOB
INTO [Text;HDR=YES;DATABASE=C:\IMDB\].Emps.CSV
FROM tblEmps;

If you need to append data to the existing CSV file:

INSERT INTO [Text;HDR=YES;DATABASE=C:\IMDB\].Emps.CSV
SELECT FirstName, LastName, DOB
FROM tblEmps;

 

[Guest]

I have Access XP with all the SPs installed. I'm not looking to edit the
xls/text file. I just want to copy the data from the link text file to my
existing access table.

UPDATE StudentTxt INNER JOIN tblStudents ON StudentTxt.Field1 =
tblStudents.DistrictID SET tblStudents.FirstName = [StudentTxt].[field2];

When I do this, I get this error:

Updating data in a linked table is not supported by this ISAM.

When I go look at the help file, it says:
The installable ISAM you are using does not allow updates to records in
external tables. You can only add new records.

But I'm not updating the external table. It's fine that it's read only. I
want to copy the data from that file to my access table.

A linked CSV file *is* a linked text file, so you won't be able to edit the
existing records in that file. But if you have either Access 2002 or 2003
and the latest service packs, you couldn't edit those records in the XLS file
anyway. Does that mean you're using an old version of Access, or do you not
have the latest service pack installed on your computer?

I need to override existing data

It depends on *where* you're getting the data from, but you may be able to
keep the linked CSV table. If it's coming from data in another (linked?)
table, when you need to overwrite the existing data you can delete the CSV
file, then use a query to recreate the file. (Don't try to update/append the
data in the linked table itself, just the file.) The linked table will show
what's in the file.

The syntax for creating the CSV file is this:

SELECT FirstName, LastName, DOB
INTO [Text;HDR=YES;DATABASE=C:\IMDB\].Emps.CSV
FROM tblEmps;

If you need to append data to the existing CSV file:

INSERT INTO [Text;HDR=YES;DATABASE=C:\IMDB\].Emps.CSV
SELECT FirstName, LastName, DOB
FROM tblEmps;

 

[Granny Spitz via AccessMonster.com]

UPDATE StudentTxt INNER JOIN tblStudents ON StudentTxt.Field1 =
tblStudents.DistrictID SET tblStudents.FirstName = [StudentTxt].[field2]; (snip)
But I'm not updating the external table. It's fine that it's read only. I
want to copy the data from that file to my access table.

You're trying to update from a join on an external file, which the ISAM
driver doesn't support. (I know you're not wanting to update the file, just
the table in the database, but the ISAM driver has its quirkiness.) If you
want to update from the data in the file, you need to import it into a table
first or paste the data into a table.

And I think I'd want to include last names in that inner join. Otherwise
*all* the first names in tblStudents are going to match the last record in
the StudentTxt table for each district.

 

[Guest]

There was more to the query that what I posted

Anyways, thanks for the info. I'll just have the user copy from the excel
file and paste into the access table.

UPDATE StudentTxt INNER JOIN tblStudents ON StudentTxt.Field1 =
tblStudents.DistrictID SET tblStudents.FirstName = [StudentTxt].[field2]; (snip)
But I'm not updating the external table. It's fine that it's read only. I
want to copy the data from that file to my access table.

You're trying to update from a join on an external file, which the ISAM
driver doesn't support. (I know you're not wanting to update the file, just
the table in the database, but the ISAM driver has its quirkiness.) If you
want to update from the data in the file, you need to import it into a table
first or paste the data into a table.

And I think I'd want to include last names in that inner join. Otherwise
*all* the first names in tblStudents are going to match the last record in
the StudentTxt table for each district.

 

[Granny Spitz via AccessMonster.com]

Anyways, thanks for the info. I'll just have the user copy from the excel
file and paste into the access table.

You're welcome, hon. Sorry I couldn't be of more help.