Excel link to Access

[ribena]

Does any one knows if after linking Excel to Access is there any way where I can allow access to add/ edit the data? For example let me use primary key to create a relationship. If possible pls help me as i really need help on this.

 

[RuralGuy]

This link may be useful: http://www.mrexcel.com/forum/microsoft-access/453613-using-access-visual-basic-applications-edit-excel-document.html

 

[ribena]

Hi, I think I didn't interpret correctly. What i meant was after access has link to excel from the external data is it possible for me to create primary keys from the table created when the files has linked up together.

Because the access keeps on preventing me from doing it. It will be a great help if u have any knowledge of this.

 

[GinaWhipp]

You cannot create a PK on a linked table you can only create it at the source and in your case, with the source being Excel, it can't be done. Excel does not recognize primary keys. The only way you could do that is to append the data from the Excel spreadsheet into an Access table and then create your primary key.

 

[ribena]

You cannot create a PK on a linked table you can only create it at the source and in your case, with the source being Excel, it can't be done. Excel does not recognize primary keys. The only way you could do that is to append the data from the Excel spreadsheet into an Access table and then create your primary key.

Hmmm ok. Let's say if I link the file is there a way that I can copy the data from the excel Table that is link, to a new table so i can create PK to create a relationship. It will also auto copy new data when the excel file that is linked is updated.

 

[GinaWhipp]

You can run an APPEND query sending it to a new table, there are no automatic import steps that do this. And sending it back out you will need to use something like TransferSpreadsheet as, again, no automatic routine to do that.

You could create a Form with a button or two and put these steps behind the button.