Create Database and Import Tables through VBA Code

[B Karthick]

Dear Experts and MVPs,

1) I want to create new backend database (for every year)
2) I have a backend boiler plate database "2005-2006.mdb"
3) I have a frontend database "ForwaderPro.mdb"
4) I have a data selection form "frmSelectYear" in the front end
5) I have a command button "cmdCreate" in the data selection form
Given below is its code ....

dim wrk as workspace
dim dbs as database
dim str as strdbsName

' some code here
Set dbs = wrk.CreateDatabase(strdbsName, dbLangGeneral)
wrk.OpenDatabase (strdbsName)

DoCmd.TransferDatabase acImport, "Microsoft Access", _
"2005-2006.mdb", acTable, "Billing", "Billing", structureonly:=True

' some code here

I have successfully compiled the above code and can create the backend
database through mouse click of "cmdCreate". When I import through
DoCmd.TransferDatabase, I intend to import the table from the boiler
plate database into the "Newly Created Database" and not in the
currentdb from which I am running the VBA code.

Can you guide me on how this can be done? Thanks for your usual
support.

Best Regards
B Karthick
Access Addict

 

[Jeff Boyce]

First, why are you creating new backends for each year? While I'm sure you
have a carefully thought out reason, you may not need to do this at all.

 

[B Karthick]

Dear Jeff,

First and foremost, I thank you for your lightning reply.

The package is a kinda Billing software. Creating seperate back-ends for
every financial year will help the customer for the two practical reasons.

1) Table Billing has a Autonumber Field which needs to start again from 1
every financial Year.
<Importing as structure only will make the Autonumber start at 1>

2) Customer want to close their Account every Financial Year end and arrive
at their
consolidated outstanding, profit, credits, debits, etc
<A Transaction Table can be used to feed the Opening Balance at the
Start of the Year to bridge the gap between the previous and current year
transaction>

This software is running in my customer place for past 2 years and they are
now upset that they need my intervention on the start of every financial
year.

Do you suggest an alternative for this?

Thanks once again.

Best Regards
B Karthick
Access Addict

 

[Rick Brandt]

Dear Jeff,

First and foremost, I thank you for your lightning reply.

The package is a kinda Billing software. Creating seperate back-ends
for every financial year will help the customer for the two practical
reasons.
1) Table Billing has a Autonumber Field which needs to start again
from 1 every financial Year.
<Importing as structure only will make the Autonumber start at 1>

2) Customer want to close their Account every Financial Year end and
arrive at their
consolidated outstanding, profit, credits, debits, etc
<A Transaction Table can be used to feed the Opening Balance at the
Start of the Year to bridge the gap between the previous and current
year transaction>

This software is running in my customer place for past 2 years and
they are now upset that they need my intervention on the start of
every financial year.

Do you suggest an alternative for this?

Don't use AutoNumber. It's a bad fit for a number of reasons. Once you replace
AutoNumber with your own numbering scheme then starting over the number each
year does not require a new table or database.

Won't this user ever want to look at data spanning years? This is just a really
bad idea.

 

[Douglas J. Steele]

To followup on Jeff's questions, why do you feel it's necessary to have the
Autonumber start at 1 each year? Autonumbers exists for one purpose only: to
provide a (practically guaranteed) unique value that can be used as a
primary key. No meaning or significance should be attached to the value of
an Autonumber field: in fact, it's unusual to even display the value of an
Autonumber field to the user. The fact that Autonumber fields are not
guaranteed not to have gaps makes their use quite suspect in a
financial-related application in my opinion. How to do you explain to the
auditors why there's no record 2546?

There's no reason that I can see why you need to do have a new copy in order
to close the accounts. All you should need is to have a flag in the record
to indicate that the record's been accounted for.

Unless the size of the database is getting dangerously close to the limit, I
would think that keeping the history available for query purposes would be
worthwhile.

 

[B Karthick]

Dear Jeff + Rick + Douglas,

My reply >>> UPPER case

To followup on Jeff's questions, why do you feel it's necessary to have
the Autonumber start at 1 each year?UNIQUE SERIAL NOS AND USER NEED NOT INPUT IT AND BE
BOTHERED ABOUT ITS UNIQUENESS.

Autonumbers exists for one purpose
only: to provide a (practically guaranteed) unique value that can be used
as a primary key. No meaning or significance should be attached to the
value of an Autonumber field: in fact, it's unusual to even display the
value of an Autonumber field to the user.FORSEE THE DANGERS OF AUTONUMBER FIELD.

The fact that Autonumber fields
are not guaranteed not to have gaps makes their use quite suspect in a
financial-related application in my opinion. How to do you explain to the
auditors why there's no record 2546?I HAVE ADDED SOME CODE TO THE FORM WITH RECORSET
CLONE TO AVOID THE 'GAPS'.

There's no reason that I can see why you need to do have a new copy in
order to close the accounts. All you should need is to have a flag in the
record to indicate that the record's been accounted for.BILLING PERIOD.

Unless the size of the database is getting dangerously close to the limit,
I would think that keeping the history available for query purposes would
be worthwhile.ONLY ABOUT 200 RECORDS PER YEAR.

 

[Douglas J Steele]

Dear Jeff + Rick + Douglas,

My reply >>> UPPER case

The fact that Autonumber fields
are not guaranteed not to have gaps makes their use quite suspect in a
financial-related application in my opinion. How to do you explain to the
auditors why there's no record 2546?
I HAVE ADDED SOME CODE TO THE FORM WITH RECORSET
CLONE TO AVOID THE 'GAPS'.

You may be missing my point. Whenever you start to insert a record, and
change your mind, the number that would have been used is lost. That means
that you'll have gaps in the numbering. You'll have Bill Numbers 2544, 2545,
no 2546, 2547, 2548. In my experience, Auditors tend to get upset about
things like that.

You cannot add code to avoid this.

There's no reason that I can see why you need to do have a new copy in
order to close the accounts. All you should need is to have a flag in the
record to indicate that the record's been accounted for.
BILLING PERIOD.

Easily. In fact, if the billing period is based on the current date, you can
change the underlying query to automatically filter for this, with no code
required.


Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[B Karthick]

Dear Douglas,

Thanks for your suggestion.

I have to ensure that there are no duplicates in the Bill No in a given
financial year but
the Bill nos will be duplicated in the next financial year. I guess, I have
to create a new
field and allow duplicates.

Do you have any suggestion to create a field (control) similar to Autonumber
without
having the user enter it. Another condition as mentioned earlier is the no
should start
counting from 1 from the next financial year.

I am thinking of a global module or form module and SQL source Combo for
this
purpose. Which one would be the best (or simpilar) choice.

Best Regards
B Karthick
Access Addict

 

[Douglas J Steele]

You can put logic in the BeforeInsert event that determines the highest
number used so far (DLookup, or opening a recordset) and adds one to it.
Another approach, useful in multi-user environments, is to maintain a
separate table that contains the highest number used so far. Wrap a
transaction around the code that reads the table and does the update to
ensure that no other user tries to grab the value before the first user has
completed.

 

[B Karthick]

Dear Doug,

I got the idea! I will try to implement it and come back to the news if I
got a problem.

Thanks

Best Regards
B Karthick
Access Addict