Excel Link Question

[syd053]

Hi All,

I created a database system initially with every table an internal standard access table. However one of the tables must come from the main system of the company which is a UNIX based system. ODBC does connect to this main system but performs too slow to consider. I know that I could change the ODBC refresh rates to a longer time period but as the system will be multi-user each time a new user opens the database the ODBC will try to obtain the data. This slows both the access database and the main system considerably so cannot be regarded as a solution.

What seems feasible to me is to link an excel table in which itself links to the main system. This can then periodically can be refreshed to the main system and saved solving the problem of slowing down the system as this can then be performed over night. I have already tried to carry this out but linking the excel table seems to work differently than an internal access table. On both the invoice and stock system the access program does not allow me to edit or add new products but does allow me to search for them.

Does anyone know what the problem may be and if there is anyway you can correct it. I have read that it is possible to carry out any operations on a excel table that you can on a standard internal table although it may require a little more work. If anyone can help me it would be much appreciated,

Thanks,

Martin

 

[The_Doc_Man]

Conceptually, there is a problem hiding in the bushes.

Excel won't update on a machine if it ain't running. So saying that the Excel table links via ODBC and you link via Excel doesn't really buy you that much.

I deal with Excel input tables now and then, but Excel as a read-write data source has not worked well with me. I tend to use the Excel to make internal copies of what I want and work with that. If an update is required, I try to do one of two things:

1. Break the Excel link, export the new table to a new workbook, delete the old workbook, and regenerate the Excel link

2. Open Excel as an automation object and just directly update what I need to update.

I am coming to appreciate #2 as far superior for most things, though there WAS a case when #1 was preferred. I can't even remember at the moment WHY #1 was preferred, but it was.

 

[Pat Hartman]

Having a link to an ODBC data source does not cause data to be sent when a database is opened. I have built several applications that required the importing of external data. To do this, I create a separate db with only the link to the ODBC table. A make-table query is used to transfer the ODBC data to an Access table. The process can be automated by creating an autoexec macro to run your make-table query and then using the Windows scheduler to automate your db at some time over night. The db that the users use during the day contains a link to the other Access db where the imported data resides.

Joining to a non-database linked table will make a query non-updateable as you have discovered. Linking to an Access table instead will fix the problem.