A2003 bringing in data from an Excel spreadsheet

[John MilburySteen]

Hi Access gurus,

Another application (not mine) produces an Excel spreadsheet. It writes the
spreadsheet to a certain address on my server. It can do so at any time,
even while users are logged on to my A2003 database. What is the best way
for the user to get data from this spreadsheet?

Does the user run a macro that imports it as a table?

Does the user link to it?

Does the user open it as an external file and read it?

Actually, I want the I/O to be invisible and fast, because I want the user's
updating of one field in my database to trigger the event of bringing in
data from the spreadsheet into other fields. Specifically, the user enters
a student ID number in my database, and a macro or function looks up that ID
number in the Excel spreadsheet and brings over the student's address,
marital status, gender, etc. from the spreadsheet.

 

[Piet Linden]

Hi Access gurus,

Another application (not mine) produces an Excel spreadsheet.  It writes the
spreadsheet to a certain address on my server.  It can do so at any time,
even while users are logged on to my A2003 database.  What is the best way
for the user to get data from this spreadsheet?

Does the user run a macro that imports it as a table?

Does the user link to it?

Does the user open it as an external file and read it?

Actually, I want the I/O to be invisible and fast, because I want the user's
updating of one field in my database to trigger the event of bringing in
data from the spreadsheet into other fields.  Specifically, the user enters
a student ID number in my database, and a macro or function looks up thatID
number in the Excel spreadsheet and brings over the student's address,
marital status, gender, etc. from the spreadsheet.

You should be able to link to the spreadsheet, so then whenever the
data in the spreadsheet is updated, you see the newest data in your
database. If you link to it, Access treats it like a local table.

I should probably get out ADH and cite chapter and verse, but I'm too
lazy...
Say you have the StudentID control set up as a combobox... then you
can have as many columns as you like and you can hide them all except
one by setting the column widths to zero. Then you can reference the
hidden columns in your "lookup" controls like this:

note that the columns collection is zero-based .Columns(0) is the
leftmost column...

lkpControl1.ControlSource = Me.cboStudentID.Columns(1)
lkpControl2.ControlSource = Me.cboStudentID.Columns(2)

etc. Then when you choose a value in the combobox, all the controls
that get their values from different columns of the dropdown will be
populated. Note, though, that they're read-only...