Coding to link to other mdb tables in form code

[Pete Straman Straman via AccessMonster.com]

First Issue:
I have coded a form in VBA that automatically creates tables and queries,
appends, and deletes records. The process must be performed on 200 or so
mdb databases.
I copy the form from one mdb to the next, view the code, use find and
replace to change table and mdb name. Then I close the form and run the sql
embedded in the form.
Is there a way to automate this process so that when I copy the form to
another mdb it reads the mdb and table name in the database to which the
form is copied?

Example:
1) Form is working in an mdb named "060012 Desciption" with a table named
"060012"
2) I copy the form to another mdb named "060013 Description" with a table
named "060013"
3) I view code use find/replace to change to "060013" and change mdb name
used to make queries to "060013 Description"

Second Issue:
I must link to several different tables in a different mdb to perform my
appending process. It is a point and click operation each time I copy the
form to another database. I would like to code this directly into my form
as well.

I would like the form to make the links, run through all the appending from
these mdbs (which I already have coded), and then eliminate the links to
these database/tables.

Resolving one or both would help save me time.

Thanks in advance to anyone who has anything to offer.

 

[Jeff Boyce]

Pete

You did say "... anything to offer" <g>...

Why? As in "why are you creating multiple forms, tables, queries, ... in
multiple databases? The example you gave (forms named "060012Description"
and "060013Description", and a table named "060013") imply that you've
embedded data in table (and form) names.

While this approach is useful and even necessary when using spreadsheets,
the only thing it accomplishes in a relational database is to cause both you
and Access severe headaches, from the "work arounds" you have to come up
with.

"... anything to offer" includes pointing out the possibility that your data
structure needs to be revisited for further normalization.

If you post a description of the data in 060012 and 060013, and explain what
you are attempting to accomplish by creating, appending deleting, the 'group
may be able to offer alternative approaches.

 

[Pete Straman Straman via AccessMonster.com]

Hi:

Anything is appreciated. I will try to give a better explanation.
I am stuck with the database design and format from the customer. The
"060012" represents a facility number. "060012 Description" represents the
facility number and name (IE-060012 Cleveland). I have old "060001 Desc,
060002 Desc, 060003 Desc, ...." mdb that I have to append with new data.
They are all in the a separate file folder at the customers request. I
could not put all the data in to one MS Access any way because some of the
databases are close to 2 GIG on there own and cause my machine to lock up
from time to when processing.

I am given another MS Acess "Append.mdb" database with six tables that
represent different systems used at all the facilities. The first thing I
have to do is change some of the data format from the six table database to
be compatable with databases I am appending (adding Q3/Q4 data).

The form I created for the "060001 Desc, 060002 Desc, ...." goes through
the Append.mdb, finds the facility ID (IE-060002) and appends it to the old
information that I have. The old information is not in the Append.mdb.

I copy the form I programmed from 060001 to 060002 and so on, use find
replace to change 060001 to 060002 and so on. There is one replacement
where I have to add the database name with the nujmber (Ie-060012
Cleveland.mdb). The individual database usese the facility number and name.
The tables within the database uses the facility number only.

Keep in mind I am not allowed to change design. I am only allowed to get it
done ASAP. The form I programmed contains many embedded SQl statements that
would take me an enormous amount of time if I had to run each one
individually.

I make external links to each facility data base to the Append.mdb database
to get the data.

I was hoping I could program the form to automatically change to the
correct facility number (table name) and name (database name) when I copy
it over to a new facility. Either when I hit the command button ot make
another module to go out and find what it needs in the database it has been
copied to. I was also hoping to be able to have the form create the links
to the Append.mdb tables automatically without me having to do it every
time I copy the form to another facility database.

I am not allowed to redesign any databases at this time. I am only allowed
to do monotonous, tedious, work as fast as I can. The form with the
embedded SQl really increased my productivity. However, I sometimes forget
to make the links or do not get the database name correct when I copy the
form template to another facility database. It is not a big deal to fix. I
am just trying to make it a little faster.

I do not know if I could understand any of this if it was sent to me by
some one else but I hope you can help. If not thanks for responding.

Pete S

 

[Jeff Boyce]

Pete

Thanks for the additional information. Given that you can only do what
you've been assigned to, and are not allowed to think or improve what you're
working with, I don't know a way to automate what it sounds like you're
doing.

Hopefully, one of the other 'group readers can offer assistance. I'd
suggest re-posting your question, perhaps with a bit more clarification, to
get the attention of 'group readers.

 

[John Nurick]

Pete,

I haven't followed this thread closely, but you can get the filespec of
the current database with
CurrentDB().Name
or
DBEngine(0).Databases(0).Name.

In recent versions you can also use
CurrentProject.Name
to return the filename.

So you could parse out the table name and write code to construct your
SQL statements accordingly.

Also, do you really need to copy the form into every database? Obviously
you do if the form has to become part of the database's user interface,
but if you just want to run code that assembles SQL statements to do
things to data, you should be able to do that in one mdb file, passing
it the filespec of the different mdbs you want it to operate on.