Access to Excel link, issue when db is open

I am trying to link a access DB to an excel sheet and everything works as expect it to, unless the DB is open.
my db is an encrypted accdb file and I have connected it via the "From Access" wizard within excel.
it is an encrypted file as I do not want the users to gain write-access to the tables that are in the database.

the error I get is:
"Do you wish to connect to 'db-location'?
if I select "Yes" I get the excel "Data Link Properties" connection window expecting me to confirm the data source and enter a login and password. regardless of what I enter, I am brought to the same page as if I click "No".

if I click "No", I get the "Microsoft Access Database Engine OLE DB Initialization Information" again asking me to verify the Data Source, Username, Password, Provider String and Open Mode.

Is there a way to get around this?
Possible thoughts:
Create a new database which has a link to the back-end as well but dont password protect it, then attempt to link from the newly created front end?

Thanks in advance for any help

Thanks for the quick response, I have a few questions...

As i want to extract into an already existing excel spreadsheet... I would assume that I am looking for the "Xlbiznes_extract_data" function? (to actually do the extracting)

with this function you have: ThisWorkbook.Worksheets("Xlbiznes").Range("A5").CopyFromRecordset rec

is it possible to enter data into a table range instead?

I have the following:

Please Login or Register  to view this content.

my code fails at ".Open" saying that the database or object is read-only.
I checked the file and its not in read-only so I can only assume that it is a network property that does give me sufficient rights?
but... if that is the case... how was I able to import the data before using the connection wizard?

additionally... all I want to do is read the information, I have no intention of actually importing