Linked Excel worksheet losing data. Access table OK 
Author Message
 Linked Excel worksheet losing data. Access table OK

I am using DAO to return a recordset to Excel. I am using an Access
table to hold my data. I simply copied data from Excel and Paste=>
Appended it to an Access table. It works as exactly as planned.

Now, I would like to have the flexibility to update the data daily, so
I created a linked table in the database.

The problem I am running into is that some data seems to be getting
lost. E.g. my query is not picking up all of the records.
For example, I am using the LIKE operator to select a recordset.
It used to work perfectly with the Access table, but the SQL for the
linked Excel worksheet is losing data. I have tried saving the object
as both an Excel workbook and a dBase IV file, with identical results.

Am I missing something? Can't I use a linked object like I would any
Access table? Why would a query work for a regular Access table, but
not for a linked Excel worksheet?

Moreover, if I go directly into the linked table, and double-click it,
I can see the text I am looking for. So I create a query with the
criteria for the Name field => Like "*z*". NO values are returned.

But, if I run the same exact query in an Access table, the query
returns the correct number of records.

Data types for both tables are TEXT.

What is wrong with my linked table? What am I missing?

I appreciate any clues.
Thanks.
Tim

Access 97 SR2
Excel 97 SR2

Sent via Deja.com http://www.*-*-*.com/
Before you buy.



Fri, 09 May 2003 10:23:38 GMT  
 Linked Excel worksheet losing data. Access table OK
I took a walk, sat back down and played with it again and found out
that it is case sensitive!

Case seems to matter with a linked worksheet. Anybody know how to make
a DAO query from Excel case insensitive for a linked table? I put
Option Compare Text at the module level in Excel but that did not
affect it.



Quote:
> I am using DAO to return a recordset to Excel. I am using an Access
> table to hold my data. I simply copied data from Excel and Paste=>
> Appended it to an Access table. It works as exactly as planned.

> Now, I would like to have the flexibility to update the data daily, so
> I created a linked table in the database.

> The problem I am running into is that some data seems to be getting
> lost. E.g. my query is not picking up all of the records.
> For example, I am using the LIKE operator to select a recordset.
> It used to work perfectly with the Access table, but the SQL for the
> linked Excel worksheet is losing data. I have tried saving the object
> as both an Excel workbook and a dBase IV file, with identical results.

> Am I missing something? Can't I use a linked object like I would any
> Access table? Why would a query work for a regular Access table, but
> not for a linked Excel worksheet?

> Moreover, if I go directly into the linked table, and double-click it,
> I can see the text I am looking for. So I create a query with the
> criteria for the Name field => Like "*z*". NO values are returned.

> But, if I run the same exact query in an Access table, the query
> returns the correct number of records.

> Data types for both tables are TEXT.

> What is wrong with my linked table? What am I missing?

> I appreciate any clues.
> Thanks.
> Tim

> Access 97 SR2
> Excel 97 SR2

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.


Fri, 09 May 2003 13:02:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Export access table to excel worksheet

2. VB 4.0 Importing Data from Excel Worksheet into Access 2.0

3. Accessing data form Excel worksheet.

4. Using DAO to create new Excel worksheet - Type property lost

5. Excel Linking and Excel/Access Linking

6. AAAaarrrggh ! Linking Excel worksheets

7. changing source file of floating linked excel charts and worksheets

8. How to Link to Excel Worksheet?

9. OLE linking with Excel worksheet - HELP

10. VB3.0 DDE link w/ EXCEL: selecting worksheet?

11. linking an excel spreadsheet to an access table using vb

12. linking a excel spreadsheet to an access table through vb

 

 
Powered by phpBB® Forum Software