How to transfer only new records from old_table to new_table 2

[qb828]

Hi guy!

Can someone help me?

I have a database with 100 tables. I delivered this to the client.
Mean a while I am entering new records to these tables at my site.

Once a month I need to update my client's database at another site with only new records.

Can you give your thoughts or share your wits how you would go about doing this?

can you tell me if this is one of the ways to do it:
1)query only new records since last month into a table.
2)take it to the client site.
3)import those new records into the client's database.

Another question, I have about 100 tables.
That means I need to do 100 queries and insert them to 100 tables.
and my client needs to do 100 imports to their tables.

Is there a better way to do this or is this only way?

Thanks

QB

 

[Darrylles]

Hi,

I personally would have 2 forms: frmImport & frmExport.
Also a duplicated database (called Updates) of the 100 tables (without data).

I would prefer to have a boolean field in all tables in the original called 'updated', which once updated is set to true. This would simplify recognising records that required an update.

The '100 queries' would be a piece of VB code that built the query string for each table, which would use an additional table that held all table names to be updated.

I would create 2 forms:

frmExport would run the VB code that would loop through all tables copying each record to the Updates db (at the click of a button).

frmImport would do the opposite for your client.

I'd zip the Updates db, email it to the client, with instructions of where to copy it to - and their frmImport form would import the records at the click of a button.

Only an overview, but quite easy to do.

Regards,

Darrylle







"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[desertdirk]

I haven't used it myself, but you might want to check out "replication" in the help section of Access. This creates a master design of the database and then generates copies of the data. It syncronizes any new data between replicated copies.

Hope this helps
DD

 

[JeremyNYC]

Why do you have 100 tables? I've never seen a database with that many tables in eight years of working with Access.

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Professional Development for Clients Large and Small

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[qb828]

Hi Jeremy!

Actually I have 263 tables in a database.
The database was created by someone already and I am helping out with data entries right now.
I was asked to create a way to deliver all new data records from all the tables maybe once a month.

I don't have a lot of experieces with Access and so many tables.

I guess you don't create these many tables with access.

anyway, I have been trying to decide a good way to deliver the data set to the client.

When I heard about the replication, i thought that was so easy.
But I don't know too much about it to use it yet.

I also am thinking about doing importing to exporting to tables.

I thought that is one safe way to do it.

let me hear your comments.
Thanks a lot for your input.


QB

 

[JeremyNYC]

Describe the different tables you have. Clearly I don't want a list, but I'm just about positive you can make your life MUCH easier by redesigning your table structure.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Professional Development for Clients Large and Small

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Darrylles]

Hi Jeremy,

I don't totally agree, and even 263 doesn't surprise me (if correctly normalized).

I've seen db's with 1000's of tables, and seen relationship diagrams printed on A4 where all you see is a solid black rectangular box because of the number of relationship lines.

Not Access - true, but maybe this db has passed the transition point where it needs to port over to SQL Server?

However, in this case - I think I'll hedge my bets and stick with you. (Especially as I've seen you around somewhat in here before - lol).

Regards,

Darrylle




"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[desertdirk]

I was thinking you could just save the whole database (after compacting it of course)and send it to your client each month. But that wouldn't be just the new data, that would be the whole thing.
DD

 

[qb828]

Yes, Desertdirk.

I can't copy the whole thing each month.


Jeremy!
Thanks again for your input.
I can't touch the objects of database.
They were done by someone else.

I am still looking at the replication.
Need to know more about it.

Thanks millions.

QB

 

[JeremyNYC]

WHAT?!? You're telling me that you have a database with 263 tables and that it's not split front end and back end? That is simply insane. You are begging for disaster, and I highly recommend you get someone in there immediately to deal with this. I would _never_ support a database like that, because I would not want to be associated with the pain it was _sure_ to cause. Yipes. I feel for you, you're in a hard spot. But I would raise this as a "must act now" issue with your supervisor and get this database put into better shape.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Professional Development for Clients Large and Small

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.