Modify split database

[Guest]

I have a split database that acts as a call log. The back end consists of a
table with over 2,000 records and the front end contains a form that multiple
users fill out. My company has decided to change the requirements of the
call log table and has decided to modify the form's appearance. They have
provided the new table and form to me to use. I have tried to import my old
data into the new table without success. How can I marry the two and deploy
the new form to all users?
Thank you.

 

[Jeff Boyce]

From your description, it isn't clear what you've already tried in terms of
adding your existing data to the new table structure.

Are there "irreconcilable differences" between the two tables (one uses
text, the other numbers)?

Is it a mechanical problem (how to append/copy over the records)?

A typical approach would be to open the new back-end, link to the existing
back-end table, then write an append query to "load" the new table.

How you deploy the new form to existing users depends on how they now use
the existing form. Do they each have a copy of the front-end installed on
their PCs, or do they "share" a single front-end located on a network share
(NOTE: the former is preferable, the latter is "fraught with danger!")?

A common way to handle this is to modify a copy of the front-end to
include/use the new form, then replace the former front-end on each/every
desktop/PC.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Douglas J. Steele]

We'll need more details in order to be able to help you transfer the
existing data into the new table.

 

[Guest]

The new table and form was e mailed to me. I tried to link my old table to
my new table and it remains blank. I then tried to import my data and got an
error message that reads "You tried to assign a Null value to a variable that
is not a variant data type".

My users each have the front end form on their desktop that links to the
back end table.

 

[Guest]

I run the same type of db here. Question when you brought in the new table
was it populated with your data; if so were all the field names the same as
the old table and was the table named the same?

Save your old table by renaming it. Make sure your new table has the same
name as the old. Next, go into your form and reattache to the new table, even
if the Record Source looks like it is pointing to the right place. Next
checkout the query built behind ths record source, this is where you can take
out old field that many not apply and add the new ones from your new table.
Finally, once your new fields are added modify the form to incorporate the
changes, test the data enter something that you can go find in the table if
that works, reinstall on all the users desktop. (Make sure your form is not
set on ADD MODE) this will cause it to apear to be blank when you open it)
I hope this helps...

 

[Guest]

So I'm going to have to modify my existing form and table and cannot use the
ones that they e mailed to me? The new ones are drastically different, and
I'd like to be able to bring my old data to the new table.

 

[Jeff Boyce]

I'm not there. I can't see what you are seeing.

I don't understand what you are doing when you "tried to link my old table
to my new table". I know of no way in Access to link one table to another.
I know of ways to link a table to a database. I know of ways to append
records from one table to another.

How did you try "to import ... data" -- specifically, what steps did you
use? Did you use File | Get External... | Import ...?

When you say they have a "front-end form" on their desktop, do they have a
hyperlink to a form inside an Access .mdb file, or do they have an Access
..mdb file?

How does the "front end form link to the back end table"?

More information, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

I'll modify that statement somewhat ...

If I am working solely in the tables (a BAD idea!), I can use relationships
to show that one table is a "parent" and another is a "child", via
subdatasheets. This is NOT a recommended approach, and would only work if,
unlike what I believe you've described, the two tables are in a one-to-many
relationship.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Sorry, I wasn't clear. My old database has the back end (table) on the
server and the front end (form) is on the users' desktop. The user modifies
the form (front end) and this information populates and updates the form
(back end).
Management has e mailed a new table and form that we are to start using.
The new table and form has some of the same fields as my old form, as well as
some new fields.
I would like to have all of my old data populate the new form rather than
have to recreate the form on the back end.
I tried to import the data using File-Get External Data-Import and came up
with an error message of "You tried to assign a Null value to a variable that
is not a variant data type".

 

[Jeff Boyce]

Perhaps we're struggling with a difference in terminology...

In a traditional back-end, there are only tables (i.e., "buckets o' data").
So I'm not sure what you are referring to when you describe recreating "the
form on the back end".

You mention receiving an email with a new table and a new form. Does this
mean you received an Access .mdb file that contains these? All Access
objects (tables, forms, reports, etc.) live inside one single file (unless
you have split the database, and store tables in one and everything else in
the other).

Again, with terminology, you usually wouldn't allow a user to "modify the
form", as this would mean they were changing the fields and layout of the
form itself. I assume you mean that the users use the form to do data
entry. Since a form is just a "peek" at a set of underlying data, when your
users do data entry via your form, they are actually putting the data into
the table(s).

Data doesn't "populate" a form. Data lives in a table. If you have "old"
data, the trick would be to get that old data migrated over into a new
table. Have you opened the new table in the email you received? Have you
looked at it in design view? Are the fields defined comparably to the "old"
data fields?

You would NOT "recreate the form on the back end" ... the back end holds
tables only.

I'm still not clear whether you actually have a new form object and a new
table object inside a new .mdb file, or if you have hyperlinks attached to
your email. If the latter, you probably would not be able to import the
object, as it isn't really there.

Sorry, but I still need more info to understand what you have...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Then just revers the process, modify the old table names to corresspond to
the your new stuff. I still do not know if the new table came populated with
your data?

 

[Guest]

Another note, makes sure you backup all your old and new data before you
start playing with the configuration. This way if you screw it up you still
have your good copies....

 

[Albert D. Kallal]

So I'm going to have to modify my existing form and table and cannot use
the
ones that they e mailed to me? The new ones are drastically different,
and
I'd like to be able to bring my old data to the new table.

No, what you need to do is to import the old data from the old back end to
the new back end..

I assume while this is happening, they all stopped working and entering
data..right?

So, what you would do is open up the new back end, and then import the data
from the old back end.

since the tables don't match, then you would create a append query from that
old back end table you just
imported into the new table structures. An append query will allow you to
re-map, and send data from fields
with different names to the new table structure.

of cause, if the data structures been changed a lot, or the number of tables
don't match, then you might have write a bit
of code to re-direct, or modify the data. However, for the most part,
usually you can just use append queries to send
the data you imported from the old system into those new tables. Once you
satisfied that you salvaged as much data
as possible, then you can delete the old tables. All of this of course
occurs in your back end. It seems that the front end has already been
created
and modified for you to work with the new back end. so, it seems your
development task at hand is to import the data.

We often have to do this type of import, as often we are salvaging data from
a older application, and must import it into
a new application. So, import the old data into some tables (in the back
end...these tables will be deleted after you managed to import the data from
those tables to your new table structures). After your done..then those
older tables can be deleted.

So, the question you seem to be basking is how do you move data from one mdb
data base to another. and, the answer is that you simply import those older
tables, and then use some append queries to send the data from those older
tables to the new table structures. You need documentation, and
understanding of the field names to accomplish this goal.

So, for example, the old tables might has used
LastN
for the last name, and the new table might use:

LastName

So, when you build the append query, you can chosoe the from/to field nameas
to accomplsh this re-mapping task. It would save a good deal of work if
those poeple started with the odler data file..and modifed that data for
you. Regardless, the above is much the process you have to follow.

 

[Guest]

Thank you so much. I'll give it a try tomorrow and let you know of my
success or failue.

 

[dbahooker]

you guys are all a bunch of mofo idiots

you all need to learn Access Data Projects

MDB is for lamers and retards that can't spell SQL