Linking Excel spreadsheet

[nikogeorgiev]

Dear all,



I've linked an Excel spreadsheet to my database. One of the fields in the spreadsheet contains more than 255 characters and the imported data is truncated to exactly 255 characters. I tried changing the properties in Access from Text to Memo without much success.

I would appreciate any comments. Many thanks

[JoeM]

See here: http://support.microsoft.com/kb/839785

[nikogeorgiev]

Thank you JoeM! Apparently there is a design problem. This is very unfortunate as I have a dynamic Excel spread sheet which I cannot just import every single day.

[JoeM]

What is the Access database used for?

You could use Macros to automatically re-import a fresh copy of the Excel file whenever the database is opened.

[nikogeorgiev]

it is for nomitoring of suppliers, exposures, financials, news etc

thank you

[JoeM]

But how do you use it? What do you actually do? Run reports?
How often do you need the Excel information refreshed?
Is it just one user who uses it?

[nikogeorgiev]

It will be used by procurment and the credit function once it's done to run reports. The Excel sheet is updated on an ad hoc basis

thank you

[JoeM]

If it is only used by one user at a time, I would recommend going with the suggestion I put in reply #4.
You can create a Macro using the TransferSpreadsheet Action to import the file.
If you name the Macro "AutoExec", it will run automatically upon the opening of the database.

[nikogeorgiev]

Many thanks for that!

[JoeM]

One final tip. If you need to clear out the old data before importing the new, add another Action in your Macro, specifically the RunSQL action.
Then in the SQL Statement argument, add in this code:

Delete [TableName].* From [TableName];

where TableName is the name of your table.