By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,732 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

Moving data from one table to another one in same dbase

P: 14
I am running an access 2003 dbase that has been slowly developed for use on a network. (I know, should be sql, but our IT dept won't allow that to happen, so we are stuck)

Anyway, because of the extreme turtle's pace of inputting data online, and some other issues concerning liability issues, we have two versions of the same database. The one that is online, and then a version that can be used in the field. Like I said, these two versions are 98% identical with the exception of a couple of additional fields in the field version.

So here is the problem. The worker will do his/her data collection in the field, then heads back to the office. We need a way to send the data, after it is scrubbed by a query to the online database version. I thought for sure we could do that using Excel as a means of transferring, but there is a problem caused by the Key field. That field of course could duplicate a record already in the online database, thus you get the big error message and have to stop transferring the data.

Ok, so I thought, I'll build a table in the online version to accept the field version data, minus the key field, and then use Excel to make the transfer, and cut and paste the data into the proper table......Nope, got the same error message.

There has to be a way, that I can transfer the field data, from the table I built for just that to the table where it needs to reside, and as each individual record is transferred in, have the table generate a proper key field number for that record. I am using the automatic number to generate the key field.

Any ideas on how this novice can make this work?

Thanks!

mtgrizzly52
May 8 '07 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
I am running an access 2003 dbase that has been slowly developed for use on a network. (I know, should be sql, but our IT dept won't allow that to happen, so we are stuck)

Anyway, because of the extreme turtle's pace of inputting data online, and some other issues concerning liability issues, we have two versions of the same database. The one that is online, and then a version that can be used in the field. Like I said, these two versions are 98% identical with the exception of a couple of additional fields in the field version.

So here is the problem. The worker will do his/her data collection in the field, then heads back to the office. We need a way to send the data, after it is scrubbed by a query to the online database version. I thought for sure we could do that using Excel as a means of transferring, but there is a problem caused by the Key field. That field of course could duplicate a record already in the online database, thus you get the big error message and have to stop transferring the data.

Ok, so I thought, I'll build a table in the online version to accept the field version data, minus the key field, and then use Excel to make the transfer, and cut and paste the data into the proper table......Nope, got the same error message.

There has to be a way, that I can transfer the field data, from the table I built for just that to the table where it needs to reside, and as each individual record is transferred in, have the table generate a proper key field number for that record. I am using the automatic number to generate the key field.

Any ideas on how this novice can make this work?

Thanks!

mtgrizzly52
Link the table from the field version to the online version and use an append query to append the data to the table.
May 8 '07 #2

P: 14
Ok, back to the books.....I've never linked from one database to another which sounds intimidating enough, let alone how to do it with an online version, but it definitely makes good sense to do it this way.

I have done a couple of append queries so that is not a problem. How it works in this linking process is a mystery, but I'll give it a good shot, and watch what happens.

Thanks for the info! Gave me good food for thought. You all are the bestest!

mtgrizzly52
May 9 '07 #3

NeoPa
Expert Mod 15k+
P: 31,342
Let us know how you get on Grizzly :)
May 15 '07 #4

ADezii
Expert 5K+
P: 8,619
I am running an access 2003 dbase that has been slowly developed for use on a network. (I know, should be sql, but our IT dept won't allow that to happen, so we are stuck)

Anyway, because of the extreme turtle's pace of inputting data online, and some other issues concerning liability issues, we have two versions of the same database. The one that is online, and then a version that can be used in the field. Like I said, these two versions are 98% identical with the exception of a couple of additional fields in the field version.

So here is the problem. The worker will do his/her data collection in the field, then heads back to the office. We need a way to send the data, after it is scrubbed by a query to the online database version. I thought for sure we could do that using Excel as a means of transferring, but there is a problem caused by the Key field. That field of course could duplicate a record already in the online database, thus you get the big error message and have to stop transferring the data.

Ok, so I thought, I'll build a table in the online version to accept the field version data, minus the key field, and then use Excel to make the transfer, and cut and paste the data into the proper table......Nope, got the same error message.

There has to be a way, that I can transfer the field data, from the table I built for just that to the table where it needs to reside, and as each individual record is transferred in, have the table generate a proper key field number for that record. I am using the automatic number to generate the key field.

Any ideas on how this novice can make this work?

Thanks!

mtgrizzly52
To insure data consistency between the Field and On-Line Versions of your Database, you may want to look into Replication.
May 16 '07 #5

P: 14
Good morning,

Sorry for the delay on responding. I did indeed figure out how to do the append process, and it works fantastic. Now if little bugs would quit popping up in other areas I would say this dbase is finished, but I'm currently trying to figure out another "challenge." If I don't get it soon, I'll be back (that's a warning by the way! *LOL*)

Thank you all so much for your expert assistance and advice!

mtgrizzly52
May 18 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.