Linked tables

[Trigger99]

Be gentle...I'ma newbie.

I am linking an Excel spreadsheet to my db and I want to cut down the time it takes to run queries. I thought that Make Table Query would do that and on the first time it works but then when I re run it, the query tells me it is going to delete the made Table(which is fine but) then it gives me a Numeric Field Overflow error. I have formatted the spreadsheet to Text for the first ten columns. Really what I am trying to do is make a hardcoded table from the linked table...or refresh the table. Please help.

 

[neileg]

Always messy using Excel to hold data. Despite what you think, you can't define the datatype in Excel. Excel will allow any old rubbish wherever you put it.

There's no need to keep running a make table, though. Set up the table once, then when you want to refresh the data, delete the data (not the table) and append the data from the spreadsheet to the now empty table.

 

[Trigger99]

thanks I will give that a try

 

[Trigger99]

That worked...I'm sure I will be back

 

[Trigger99]

OK it still isn't working. After I delete and append and save I still get the Numeric error. It is almost like it affects the original spreadsheet. I have to go back to the original spreadsheet adn then reformat the first 8 fields to text and then it works. anymore suggestions??

 

[neileg]

OK it still isn't working. After I delete and append and save I still get the Numeric error. It is almost like it affects the original spreadsheet. I have to go back to the original spreadsheet adn then reformat the first 8 fields to text and then it works. anymore suggestions??

You can't format the cells in Excel, only the data. So if you add new data this won't be treated as text unless it is obviously text, until you re-apply the formatting.

If there is no alternative to using Excel, you could use a second worksheet and reference your first sheet with a formula such as =""&Sheet1!A1
This will force the data to be text no matter what is entered. So let your users see the first sheet but link or import the second sheet to Access.

Still messy, though.