Primary Key Issues

[ItsMe]

If you create a new Access file, create a new link to a table, double click the table, and then see duplicate records, your data has duplicate records. If it does not have duplicate records, you are golden. All you need to do now is create a query based on your newly linked table(s). Either your data or your query is goofy. You do not need Primary Keys defined in your tables to create a query.

What may be happening is your query has JOINS that are creating a Cartesian Join.

[wcobb]

The queries are not giving me issues. The tables are. Did you see the previous post with pictures showing one of the tables at issue? When linked the table is showing all of the same row, when imported the table is correct.

[orange]

What is/are the table designs in the operational database? What is/are the Primary Key(s)?
What exactly is the structure of the download you do/receive?

What exactly is the impact if you change the design to make the proper PK?

[wcobb]

What do you mean by table design?

The primary keys that I entered when importing are wrong.

What do you mean the structure of the download I receive?

Like I said, I believe it happened when I chose poorly on the unique identifier and its using those choices as primary keys when it shouldn't be.

[orange]

So, what if you change it to ItemID or whatever it should be?

If you really do not understand what table design means, then read this.

What do you mean the structure of the download I receive?


Is it a flat file? Is it a database dump(for unload/reload)?
Can you give us 10-20 records from the download file?

[ItsMe]

If you look at your raw data and it has duplicates, then your raw data has duplicates. You might be able to put a Band-Aid on it by employing a query that masks the underlying issue.

[wcobb]

If you look at your raw data and it has duplicates, then your raw data has duplicates. You might be able to put a Band-Aid on it by employing a query that masks the underlying issue.

The raw data doesn't have duplicates. The issue cannot be with the raw data as it only appears when I link to the table, not when I import the data. The data is correct when I import the table but wrong when I link to it.

So, what if you change it to ItemID or whatever it should be?

I can't, Access doesn't allow changing the primary key on linked tables.

If you really do not understand what table design means, then read this.

Even reading that there's no good answer I can give you, as there's no standard names so I'd need to either dump all the tables to show you or spend an hour telling you everything inside there.

Is it a flat file? Is it a database dump(for unload/reload)?

Like I said before it's a .db exported from sqlite somehow (I'm unsure past this)

Can you give us 10-20 records from the download file?

I took some screenshots of an affected table to show you how it messes up when it's linked but not when it's imported.

[ItsMe]

The issue cannot be with the raw data as it only appears when I link to the table, not when I import the data.

I have never experienced this with linked tables. Perhaps your idea of linked is different from mine. If you recall, I asked about your tables in your Access file. Answering this previous question may have shed some light on the subject. Linking a table and importing a table can both be accomplished via the External Data tab within the Ribbon. Importing a table is when you have an external table and create a copy of it locally. Linking a table is connecting to a remote table. Linking to a table will display the raw data, only. Linking to external data may or may not allow for editing of the remote data.

When you are working with a query, you do not create links. Perhaps you are confusing a JOIN with Linked.

[wcobb]

I have never experienced this with linked tables. Perhaps your idea of linked is different from mine. Linking a table and importing a table can both be accomplished via the External Data tab within the Ribbon.

Yes and that is how I am adding them. As I've said before when I link to an affected table it shows a wrong primary key and the entries are wrong. When I import that table there is no shown primary key and the entries are correct.

Importing a table is when you have an external table and create a copy of it locally. Linking a table is connecting to a remote table.

Yes, that is quite obvious and I use that to good effect as I want to link to the external table so it updates daily when I update the source db. I prefer not to import as I have to manually import every time I need it updated.

When you are working with a query, you do not create links. Perhaps you are confusing a JOIN with Linked.

I have zero issues with queries and have never brought them up except in response to people who think I have. Queries are not the issue. The issue is when some tables are linked they are linked with the wrong primary keys and give me a bad table. But when those same exact tables are imported they are correct.

If you recall, I asked about your tables in your Access file. Answering this previous question may have shed some light on the subject.

What haven't I answered about my tables?

[ItsMe]

What haven't I answered about my tables?

I was trying to discern what the Import process was vs. the Linked process and understand if you were using local tables JOINED with external data. Hoever, this question seems to be moot, at this point.

If your linked tables are not showing the correct data. I suggest you import the data to a Temp table within your Access file. This is what I, sometimes, do with raw data. A table within your Access file will always reside there. This table will be your temp table. The structure of the table does not change and the table is never deleted. What changes and what is temporary is the data within.

You can use the Wizard to create a table. Use the Wizard to Import data and create a new table. Then, delete the data from the table and launch the Wizard a second time. This second time around, at th eend of the Import process with the Wizard, save the process. Give it a good name and make a note of the name. Now you can repeat the process via the controls in the ribbon or automate via VBA.

[wcobb]

If your linked tables are not showing the correct data. I suggest you import the data to a Temp table within your Access file. This is what I, sometimes, do with raw data. A table within your Access file will always reside there. This table will be your temp table. The structure of the table does not change and the table is never deleted. What changes and what is temporary is the data within.

You can use the Wizard to create a table. Use the Wizard to Import data and create a new table. Then, delete the data from the table and launch the Wizard a second time. This second time around, at th eend of the Import process with the Wizard, save the process. Give it a good name and make a note of the name. Now you can repeat the process via the controls in the ribbon or automate via VBA.

Sounds like a possible workaround. Thanks!