Update linked tables - Excel spreadsheet with/imported external....

[wlwoman]

Background:
I am implementing a new system that uses imported (linked) nformation, along with user-generated data, to track customer returns. The goal is to automate the process insofar as possible. Our company has a data system that's patched together using bits and pieces of various DB packages across several corporate locations. The AS/400 DB is located in another state and the only access to it requires login/pw; hence the macro to import the records.

Question:
I have an Excel spreadsheet that uses a recorded macro to connect to an intranet database (AS/400) and import all fields from a table. The Excel spreadsheet is linked to a table in my Access database. What I would like to do is have the Excel macro run every time I open up my database to make sure I have the most up-to-date records available. Is it possible to formulate a macro in Access that will call up and run the Excel macro when the database is opened?

 

[JunglesMcBeef]

G'day,

It is possible to do exactly what you want to do. All you have to do is write a function in Access that runs the Excel macro and create a RunCode macro in Access called AutoExec that runs that function.

 

[wlwoman]

I was able to link to the AS400 from Access, but now the AS400 asks me to log in 3 separate times - any ideas??

 

[JunglesMcBeef]

You need to set the criteria of any subsequent login prompts to the original login value you enter. If you can't find this value, set it to a variable so you know where it is and can use it.

 

[janetlyn]

wlwoman, since you marked this thread I was hoping you would see it swiftly and could help me please. You stated you are already sending Excel data to Access. How? I need to populate 30 Access Unbound fields with data from specific cells (never changing) in an Excel spreadsheet.

By Never Changing I mean the cell location will always be the same for each unbound field; not that the value will never change.

Any help you can give would be much appreciated. Thanks, JL

 

[wlwoman]

Janetlyn:

You have a few ways to do this but this is what I've done:

If you want a permanent link between the cells on your Excel spreadsheet and an Access table, from Access you can do a File, Get External Data, Link Tables, and change the file type option at the bottom of the Link window to .xls. Then browse to the Excel spreadsheet, and select the fields you wish to link to. You can then either write a macro to automatically update the linked cells when you open the database, or you can do a Tools, Database Utilities, Linked Table Manager type update when you open your database.

If you want a one-time import of the cells from Excel, then from Access toolbar, do a File, Get External Data, Import, and again select the spreadsheet and cells you wish to import (as above).

If you've already built your tables in Access, you may have to run a query on the imported or linked data to import from a new table (saved from the results of importing or linking above) into your existing table. You could also make the fields in your existing table Lookup fields and get the data from a new table.

I hope this helps! If not, please give me some more details on the the relationship between the two documents.

I admit to being new at this but have learned in the past two months just how much fun Access is to work with!
-SG