Writing records from one database to another database

[fred]

I need a method that would take the payment received data from the billing
database and transfer the customer account name, payment amount & date into
the general ledger database each time a new payment is received from a
customer. Currently, both databases are manually updated each time a payment
is received thus costing time and an opportunity to make a mistake by
forgetting to log the payment into the general ledger. Please let me know if
I should post this in the VBA forum or if a macro is an appropriate solution.
Thank you in advance.

 

[Steve Schapel]

Fred,

Really, this is a design flaw with your system. Ideally, the billing
database and the general ledger database should be using the same data
table(s) for the payments records, so the concept of duplicated entries
should not arise.

Anyway, now that you have what you have, I suggest linking to the applicable
general ledger table from your billing application, and then make an Append
Query to write the required data to the general ledger. You could use an
Op[enQuery action in your macro to run the append query. This would
probably be done on the After Update event of the form where you enter the
payment in the billing system.

 

[fred]

Steve,
Thanks for your suggestion. I used the Microsoft Access template databases
(business account ledger and time & billing) to quickly get my new business
up and running. I am only 2 months into the new hardware business. Would you
recommend merging the two databases together instead of linking? I manage 150
charge accounts with the time & billing database and of course the account
ledger database is used to maintain accurate financial records and print nice
reports for the bankers.
Thanks,
Fred.

Fred,

Really, this is a design flaw with your system. Ideally, the billing
database and the general ledger database should be using the same data
table(s) for the payments records, so the concept of duplicated entries
should not arise.

Anyway, now that you have what you have, I suggest linking to the applicable
general ledger table from your billing application, and then make an Append
Query to write the required data to the general ledger. You could use an
Op[enQuery action in your macro to run the append query. This would
probably be done on the After Update event of the form where you enter the
payment in the billing system.

--

Regards
Steve

I need a method that would take the payment received data from the billing
database and transfer the customer account name, payment amount & date
into
the general ledger database each time a new payment is received from a
customer. Currently, both databases are manually updated each time a
payment
is received thus costing time and an opportunity to make a mistake by
forgetting to log the payment into the general ledger. Please let me know
if
I should post this in the VBA forum or if a macro is an appropriate
solution.
Thank you in advance.

 

[Steve Schapel]

Fred,

Since you are obviously planning to use this database system to manage your
core business processes over a long period of time, I would certainly
recommend investing the time into getting it set up to a higher level of
sophistication than the templates provide. The templates can give a handy
start, but seldom sufficient.

Since there is data that is common to, or related to, both aspects of your
data management needs, it makes sense to integrate them. Ultimately you
want your actual data tables split into a separate backend database file
anyway... not sure whether that's how you have done it already. So I would
work out the data requirements for the whole system on that basis. Then, it
may make sense to still have separate front-end application files for the
billing and ledger processing - both of them would be linking to whichever
of the backend tables they need, and some of the backend tables would be
linked to from both. Whether it is a good idea to do it like that, or
whether it would be better to incorporate all funtions into a single
application, depends on your business processes, so I am not able to
recommend specifically.

Hope that at least points you in a useful direction.