Can I Import Information from Excel into Linked Field in Access?

[Tim Mullin]

Hello All,

I have a table (Commissions) in Access that has a field (ClientName) that is linked to a "ClientName" field in a "Clients" table. The relationship was made via the LookUp Wizard.

I would like to be able to import spreadhseets of data into the "Commissions" table, however I am seeing an issue every time I attempt it. The spreadsheet in excel matches the field title names for the table exactly, however when I import the spreadhseet it deletes the data in the ClientName field. The ClientName in the spreadsheet matches exactly character for character what is listed in the Clients table, but it still deletes the data from that column.

I tried turning off the "Limit to List" for that specific field in the Commissions table, but I received an error message stating that "The first visible column, which is determined by the ComlumnWidths property, isn't equal to the bound column." Not too sure what they're referring to here.

Additionally I tried replacing the names in the ClientName field of the excel spreadhseet with the Clients.ClientName's ID. This seemed to work, but isn't very helpful to me as we would have to replace each client name with the ID before importing it.

Has anyone had this issue before or know of any possible solutions? Any help would be greatly appreciated!

PS. The primary key in the Clients table is ClientName, not the ID number. Again thank you for any help you can provide!!

[NeoPa]

This is very confusing. Your explanation contains references that clearly apply to a control of either ComboBox or ListBox type, yet nowhere is such a control introduced or explained.

I will add, at this time, that using a free-form string field as a PK is not a good policy. The idea of an ID for the [Clients] table is a very much better one.

[Tim Mullin]

My apologies for the shoddy explanation... I'll try again a different way.

Table 1: Clients
Field 1: ID [AutoNumber]
Field 2: ClientName (Primary Key)

Table 2: Commissions
Field 1: ID [AutoNumber](Primary Key)
Field 2: Client Name (in relationship with Clients.ClientName so that the field has a dropdown box with each ClientName available)

When I import the data from excel into the Commissions table, the data in the ClientName field gets deleted. Everything else imports correctly. Is it something to do with how the relationship is formatted? (enforce referential integrity and cascade update related fields are both enabled) The names in the excel spreadsheet match exactly what is listed on the Clients.ClientName field.

I think it has to do with the fact that the Commissions.ClientName field is formatted as a number due to the relationship the lookup wizard created, but cannot be sure.

[NeoPa]

Are you saying that the number stored in Commissions.ClientName is lost?

How can a number be said to be lost for a record that is just being imported (as in how can anything have existed anywhere before if it's just being created)?

I find your explanation very confusing. It seems even now, much of the relevant information is still missing. So much so that understanding what's going on is impossible (As none of the possible scenarios I can think of fit your explanation).

I suspect that your basic setup (which I commented on in my earlier post) is somehow related to the lack of sense involved. Having an AutoNumber field which isn't a PK makes no sense and using a text field for a PK is poor strategy. Having a number field called [ClientName] is just asking for trouble. It's no wonder an explanation of such a scenario is going to be hard to make sense of.