We have 2 standalone Access databases with exactly the same table and
field structure, call them db1 and db2. We want to transfer (not
replicate) data from db1 to db2, primary keys need not be the same
between the two dbs. Specifically we want to transfer customer data,
which is stored in tblCustomer of db1 into tblCustomer of db2. For
simplicity, let's say we want to import into db2 the Name and Address
of the customer, exactly as stored in db1, but without having to
manually retype the data. The transfer needs to be handled
automatically without any manual intervention. It is acceptable to
have an intermediate file that is exported from the one db and
imported into the other db.

One way to do the transfer is with an intermediate text file, but this
runs into problems when we transfer non-English characters(for example
Greek). The specific case involves the dental software
(www.VisualDentist.com) which is designed to work with any language.
Occasionally one dentist wants to refer a patient to another dentist
and there is a need to transfer the personal data of the patient from
one dentist db to another.

Both db1 and db2 have a front end mde and a backend mdb. Table
tblCustomer is stored in the mdb. We could perhaps make a copy of the
backend mdb, delete all tables except tblCustomer and all records in
tblCustomer which are not needed. Then have db2 read this mdb. It
would be better if the mdb that would store the customer data was an
mde and that db2 would read this mde. I have not tried this solution,
which I must admit is not very smooth, but I feel that there will be
several problems like
1) How do I delete the tables in the copied mdb.
2) Can the copied mdb be compacted to something reasonable for
transfer purposes.
3) Can I create an mde based on the copied mdb
4) Can all the above be achieved with automatically with a click of a
button

Any ideas?
Thanks, John

Link your FE to both DBs. When a table from the 2nd DB with the same name is
linked, it is give a new name (in the FE not the BE); normally "tablename"1.
You can then use a normal append query to export the records.

"JohnP" <john (AT) kosmosbusiness (DOT) com> wrote

Ron, the data is on two different, independent PCs. The application is
installed on every PC. Each PC has a FE MDE and a BE MDB. The BE MDB
on each PC has a table called tblCustomer, where the dentist stores
the personal data of the patients (name, address, medical history
etc). When a dentist refers a patient to another dentist who uses the
same software (www.VisualDentist.com) I want to provide a mechanism
where the dentist to whom the patient is referred does not need to
manually enter the data.

I thought of using an intemediate text file. This works OK except in
cases of non-English characters. I then thought of using an MDB as an
intemediate file. I thought of copying the BE MDB and deleting all
tables except tblCustomer and all records in tblCustomer except the
one needed. The problem here is how do I delete the tables in this
newly created MDB. Don't I have to attach to the newly created MDB.

Thanks,John

You could try some scullduggery with ADO disconnected and saved
recordsets. It would be possible to dump the results of a query to
file as an ADO recordset, transfer them to the other pc, then process
that set of results into the local BE of the target pc. It would be a
bit of code writing to do it but nothing majorly complex, just a
little tedious.

To speed it up you might dump the incoming recordset to a temporary
table and then do a comparison / append via SQL directly in the BE
rather than in ADO - it might be faster. At least saving an ADO
recordset to file you will keep the data types and so on..... You
might also want to try XML as an option. I would tend to go with the
ADO on this as I am familiar with it and I dont believe it would be
difficult to work out the details for building a functional solution.

Cheers

The Frog

If the PC are not on the same network, the 2 BE can work. Create a MDB with
only the BE tables you need to transfer. Use a query to transfer the
information to the new MDB. Email it to your users. Have it copied to the
proper location, (code in the FE can handle this). Import the data. You
never need to convert it from Access.

"JohnP" <john (AT) kosmosbusiness (DOT) com> wrote

Ron, I would tend to agree that this may be the only fullproof method.
In the specific case, I would only need to transfer only 1 record from
1 table (the specific patient details from tblCustomer). I could
create a specific mdb (or even an mde) with only the 1 table and ask
the users to attach to this mdb file for exporting and then for
importing. To automate matters I could create this mdb file on the fly
from the BE MDB file ie copy the BE MDB file into the import/export
mdb file. The only problem here is how to I make sure that the import/
export mdb contains only the one table?
1) Is there a way to automatically copy into the import/export mdb
only the required table?
2) If not, is there an easy way to delete all tables except te one I
require?

The Frog, can you please explain a bit further how you would do it?

Thanks,
John

Hi John,

In short I would start by writing a query in design view / SQL that
retrieves the records I want - be it a single record or a whole table.
I would then use this query as the basis of building an ADO recordset.
I would then save the recordset to file (ADO natively supports this).
Export part is now done.

To import I would open the saved recordset file on the target system.
There are two ways to go here, either dump the recordset to a temp
table, or pump directly into the target table. If you are familiar
with ADO this isnt hard, just a little tedious.

Another thought occurred to me that might be helpful and extremely
quick also. You can write your query as before, but instead of using a
recordset you could write the results directly to a new MDB as part of
the SQL syntax - extremely fast and easy to debug. Its just SQL!

On the target machine you could also have an 'import' query that takes
a filename as a parameter, and then uses the 'export mdb' as its
source. Doing an upsert type approach can again be done entirely in
SQL - again extremely fast and easy to debug.

Here is an example of dumping data to an external MDB:
INSERT INTO [Database=C:\PathToDb\YourDbName.mdb].[YourTableName] (
[FIELD1, FIELD2, etc...] )
Values( 'FIELD1_Value1', 'FIELD2_Value2', etc...)

or

INSERT INTO [Database=C:\PathToDb\YourDbName.mdb].[YourTableName] (
SELECT * FROM [SourceTableName])

It really depends on how you want to approach it and how many records
you are needing to shovel across. The pure SQL way should be the
fastest and safest I think. I use ADO because I often move data from
different database systems to and from Access so it may not actually
be the best approach here. If there is a way to identify the
individual records you want to export we can make this extremely fast.

Importing can be done just as easily. For example:
SELECT *
FROM [TableName] IN C:\PathToDb\YourDbName.mdb;

Neat, clean and quick. All that needs to be done here really is to
figure out what you want to move from A to B and then build and
test :-)

Cheers

The Frog

Hi John,

Here is how I would do the export given what you have said:

1) Create a 'target' database (mdb) with the filename as the exported
customer name. The following sub will create a target mdb for you:

Const dbVersion10 = 1
Const dbVersion11 = 8
Const dbVersion20 = 16
Const dbVersion30 = 32
Const dbVersion40 = 64

Sub CreateMDB(FileName, Format)
DBEngine.CreateDatabase FileName, ";LANGID=0x0409;CP=1252;COUNTRY=0",
Format
End Sub

****Be careful of the wordwrap here.
I am sorry I dont have the enumerations for all the values here, but
you should make sure that the langid, cp and country settings match
your own (its the DAO.DBEngine object model being used here, so you
can check your own settings to be safe)

Now that we have a target DB with a name like
ClientNumFirstNameLastName.mdb we can move to step 2....
2) Do an append query using the select into syntax like this:
INSERT INTO [Database= Filename].[Customer]
SELECT *
FROM TableName
WHERE CustomerID = SomeControlOnYourForm.Value

You have now dumped the customers data into a table called Customer in
the target mdb. I would do this individually for each export that is
required. You will end up with an mdb file for each exported customer.
You probably want to do a file check to see if the file exists before
creating it (DIR function can be used here), and if it exists then
kill the 'old' file, make your new one (using code similar to the
above), then run your append query (either an actual append query or
docmd or a macro - whatever works best for fitting in with your
existing methods and design).

I will come back in my next post for the import....got a meeting to go
to.......

The Frog

OK, back again :-)

The import part can be a little trickier depending on the logic that
is required. For example what happens if the customer has already been
referred and is in the third party dentists database? Do we overwrite
the record? Do we make a new one? Do we do some sort of version
control?

Reading the customers record from the small mdb that is generated by
the original dentist isnt the hard part here, its what needs to be
done with it to make the data import 'data safe' so to speak.

In order to figure out a suitable import methodology I would need to
understand the structure of the customer / client table and
dependancies it may have on other tables. Are you able to provide a
description of the table and its fields, etc...?

Cheers

The Frog

The Frog,

I am trying it now and will come back. Importing should not be a
problem.

Regards,
John