Primary Key Issues

[wcobb]

I have an issue related to primary keys and it would be great if I could get another set of eyes on the problem.

Currently we have a web based order taking/inventory system. Every night they export a .db to an FTP server and every morning I download it (I automated this part, it replaces the older file). I link to this database (using SQLlite3 to ODBC driver) from an Access database for specifically this purpose.

When I originally created it I had to go through every table and create a unique identifier. Unfortunately I chose poorly in some cases. I can't figure out how to get those primary keys changed to the correct ones. I've tried recreating the ODBC connection, tried a new database and new ODBC connection, tried killing the connection waiting a day (for the new .db to download) and recreating the connection and trying it in a new database.

I know access doesn't allow you the change the primary key in a linked table (which I currently find very frustrating).

Any thoughts? Thanks!

[ItsMe]

So you have raw data that you need to append to an Access database? If you have lots of data in your Access database, I am not sure there is an easy solution. One approach might be to use the existing Key within the raw data as a Business Key and the Autonumber in the Access DB would be a surrogate key.

Also, you can write data to an Autonumber field via an Append process.

[wcobb]

Well the Access Database is only the linked tables (and some imported tables from the .db download). So I don't need to append, I just need the linked tables to work correctly. Currently a number of them (with wrongly set primary keys) don't work when they're linked, they only work correctly when they're imported.

What do you mean by autonumber?

[ItsMe]

Autonumber is a data type in Access. It is commonly used for Primary Key columns.

I just need the linked tables to work correctly.

If they are not working correctly then you are not appending your records correctly. So, you need to figure out where you have control and where you do not. That is why I mentioned raw data. If someone is handing you data and saying, "Here you go.", I would consider that raw data.

[NTC]

there can be other blocks to a change beside the relation link; it should be an indexed field not allowing duplicates thus any attempt to manually enter a duplicate value would be blocked.

if it is really being blocked a relationship join then delete the relation join line in database tools tab - if we are discussing tables within Access (I'm not sure we are as the post is a little confusing to me)...

[wcobb]

Then that is what I'm getting. From my understanding it's a copy of our section of the sqlite database.

I don't know how to use the primary keys in the raw data, if they're there I don't know how to see what they are. Obviously I'd love to use them as they're going to be the most correct.

Also I trust you and believe you that my records aren't getting appended correctly. But I don't understand where I'm appending data. The source database I'm linking from get's overwritten every night. It's not an incremental update or anything like that.

[orange]

I'm with NTC -- post is a little confusing. Can you repeat the description and give use some sample data for ech of the pieces?

Currently we have a web based order taking/inventory system.

What dbms?

Why exactly do you download the database? Where does your database fit in the big picture?

What is the purpose of your database? How did your database get to where it is, given the poor chice of primary keys? Did anyone test this system?

[ItsMe]

I am confused and it seems others are too.

• In your Access database are there tables other than the linked SQLite tables?
• Post #1 describes unique identifiers in multiple tables. Are you using these unique identifiers in your Access file?

[wcobb]

The database is the data export from our 3rd party order taking/inventory system. We don't control it or run it.

We download it every day because they update it every night and we download it to get the most current data we can.

It fits in in the fact that it gives us all of our sales, inventory, and customer information and allows us to run reports off of it.

The purpose of the database is to give us access to this data so we can run reports off of it.

It got to where it was because when I originally set it up (as I described in the OP) I buggered up and chose poor primary keys because I wasn't familiar with the table structure (I had never opened it before) nor with primary keys (I have little background in DB)

Yes when I set it up we tested it and it's worked fine. It also works fine when I import the tables. Some of the tables do not like to be linked and instead show up with multiple rows of the same entry. So some tables I have to import manually to update whereas most of the tables work with the link.

[wcobb]

Also I apologize for being confusing, like I said in that previous post I have little DB background, and little access background.

Basically what I'm using Access for is a query generating software. We link to all the tables in the source file and then run queries off of those. When I originally set up the linked tables I was prompted to set unique identifiers for each table. I did that poorly so there are some tables that don't show up correctly when they're linked.

It's not the relations between tables. It's the primary key in each table that's the issue. I set the relations to each table when I run queries.

[wcobb]

This is the broken table when it is linked. As you notice all the entries are the same (it goes off the screen, but you get the idea)



This is the same table when it is imported and not linked



This is the design view of the linked table. You can notice the primary key is on CompanyID and it should be on ItemID (because it's an Items table).

[ItsMe]

If all of the tables are truly linked tables, there is not a need to define a Primary Key or a Key or, really, do anything. Just link the tables and create your queries. You might (if it is not a huge job) try starting with a new Access file and create new linked tables. Then you can copy or drag your favorite queries into your new Access file. You can also import Reports. Just be sure to make copies before starting anything ....

[wcobb]

I already did all that but it doesn't resolve the problem.

And I did have to define a primary key. Or at least a unique identifier. Look at my last post to see what's happenining

[ItsMe]

To be honest, I am not sure why having a Key defined or not would create an issue. So, I still do not know what the "problem" is. Perhaps your queries are goofy or perhaps the raw data has issues.

If you care to try linking tables and not defining a Key, just click 'No' when prompted to define one. Not sure if you should be prompted for a Key when linking though. So not sure where/why you are creating this Key.

.

[wcobb]

It doesn't prompt me to define a key anymore. It only did it on the first time. And like I've said I've tried relinking the tables multiple times and that doesn't fix the issue.

I have a wrong key defined when means when Access goes and looks for records it's matching everything wrong. That's where I've determined the issue to be which is why I asked about it.