Linking Excel and Access

  • Thread starter Thread starter Jamie
  • Start date Start date
J

Jamie

Hi -

I have an Excel spreadsheet that is linked to an Access database. The
spreadsheet is on a server and is set to be accessed (and changed) by
multiple users, simultaneously. (No problem there, because 2 people are
typically working on it at a time but with different rows in the
spreadsheet.)

Access database has back end on server with front end on each client
machine. In a client form, for each record there are three fields that link
to the Excel spreadsheet. These fields cannot be changed on the Access form.

When anyone is viewing the data through Access, the person working on in
Excel cannot close and save the spreadsheet until everyone has closed their
Access form.

Is this a problem we have to deal with, or am I missing a setting somewhere?
Would greatly appreciate any direction.

Thanks - Jamie Fugate
 
hi,
this a problem you may have to deal with. it is called
record lock. usually it poses no real problem but it can
be if a user decides to run the report and leave it up
while they go to lunch and you are in recieving trying to
find out why a "hot" po receipt wont go.
and worst.. HR wont let you kill 'em. something
about "bad company/employee relations".
i would suggest that you experiment with the
forms/queries/reports properties about "no Locks" but
with linked excel tables like you described, i may be on
foggy ground. you can try.
no easy fix for this. i have been dealing with it for 6+
yrs. when dealing with input, queries, reports ect, you
have to consider the "chain reaction" and access has some
built in stuff to deal with it.
good luck
frustrated Frank
 
Frank -

Thanks for the thoughts - I can relate to the frustrated part..
I have turned off record locks in the database as it is a shared database on
a network. This is my first play with linking Access and Excel. Hoping I'm
missing something.

Thanks - Jamie
 
Back
Top