Exchange record info between two Access dbs

[JohnPapa]

With the knowledge obtained so far let me try to specify the problem. 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. 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.

One way to do the transfer is with an intermediate text file, but this ran into problems due to the fact that we have data stored in non-English (for example Greek). Actually the software (www.VisualDentist.com) is designed to work with any language.

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

Thanks, John

 

[DCrake]

I think the first thing you need to identify is Primary Keys. If in db1 a customer has a PK of 0010 in the tblCustomers table how are you going to transfer that data to db2 if a different customer has the same PK

db1
Tblcustomers
PK...............0010
Name...........Smith

db2
Tblcustomers
PK..............0010
Name..........Jones

 

[JohnPapa]

David,
There is no need to have the same primary key in the two dbs. The important thing is the customer name and telephone number and the medical history. As I mentioned, this is a problem of "transfer" of data, not of data "replication". The two dbs are independent of each other, even though they have exactly the same structure.

The transfer of data needs to happen automatically from db1 to db2. It is acceptable to click an Export button to create some sort of file and then click an Import button to read this file and add the new data. If this intermediate file is a text file, there may be problems with non-English characters, so I was thinking of maybe using an mdb file.

Hope this helps,
John

 

[DCrake]

I understand your problem, but what I am trying to get accross is say Miss Jones gets married or moves house? db1 records the name change and the address change. You then port this out to db2 how are you going to tell if Miss Jones, now Mrs Smith existed already in customers table? if you just append the details to the customers tabel you will have one record for Miss Jones and one for Mrs Smith - the same person. I you want to update the old Miss Jones's record in db2 what will you be using to check if she already exists in the cutomers table. This is where Primary Keys come into play.

 

[JohnPapa]

I appreciate you pointing out and I do understand that data integrity is one of the tenets of database design. In the specific case, it is not an issue, since the patient will be treated by two different dentists and it is the responsibility of the dentist to keep the info he wants and who he thinks is necessary. If a patient changes their address, then this may be an issue if the patient was treated, say at a dental clinic (where both dbs resided), and the clinic had to contact the patient. In my case we are talking about separate dental practices.
Hope this helps,

Any ideas on how to do the transfer using an mdb or mde?

John

 

[DCrake]

Create a blank mdb that will become the repository for the tblCustomers Table

Then from db1 create a SavedExport routine which exports the table to this new mdb.

Next create a form that will act as the trigger for exporting the table to the new mdb. A Simple form with an Option Group Import/Export and a button to fire off the command. Next create an SavedImport Routine in db2 that imports the new table.

Place this form in both db1 and db2. db1 exports db2 imports.

 

[JohnPapa]

David, many thanks for the reply. Do you know whether I can automatically create an mdb file from within say db1. In other words if I have db1, can I click a button and programmatically create the mdb and if yes can this mdb have the same security characteristics as my backend mdb (which contains tblCustomer) so that it can only be opened by authorized users such as db1 and db2? One thought would be to work on a copy of the backend mdb and delete all tables and records other than the one you want to transfer. The problem here would be that I would need to attach to every table for the deletion of the tables.
John

 

[DCrake]

What you really need is a db3 which acts as an admin db that is totally sperate from both db1 and db2. This will act as a gobetween.

Preperation to Export:
It will ask which mdb contains th data you want to Export
Once selected use FileCopy to make a new mdb
Delete unwanted tables from new mdb

Preperation to Import:
Where is the mdb to import into
Make backup of this mdb first
where is the mdb that contains the new data
import all none system tables in to db3
Link Tables between new data and import tables
Append data from old to new


Some steps and/or operations may be missing, this is just a conceptual approach to the methodology.

 

[gemma-the-husky]

Whoa!

I thought this was your own software

in post #11, however you mention a commercial software (visualdentist.com) So - does the software have an import/export mechanism

if not, how can you reliably write directly to the data files?

creating a patient may create other records, rather than just an entry in a patient table. eg - records in other tables, or a record in a log file. Just adding an entry to the patient table may create some unforeseen problems. Without knowing what else is created, I would avoid this. Approach the software suppliers for assistance.

 

[JohnPapa]

David, referring to your instructions
Preperation to Export:
It will ask which mdb contains th data you want to Export
Once selected use FileCopy to make a new mdb
Delete unwanted tables from new mdb

All is clear except the use of FileCopy to make new mdb. The tblCustomer table is in the BE MDB file. I can copy this using FileCopy into a new MDB but how do I programmatically delete the unwanted tables? Don't I need to attach to the tables in the new MDB?

Regrding Dave's (Gemma-the-husky) comment, the software is my creation and it is a commercially available product.

 

[DCrake]

In theory you should not be deleting tables anyway. You should be deleting the conents of the tables and appending new data to the tables. If the said tables are not needed then what harm are they doing just leaving them there. You wizard will only be programmed to work with nominated tables. However if you only want to have the specified tables in the database then why not split your backend into 2 halves, 1 with the common tables and one with the export/import tables. Both backends can be linked to your front end.

 

[JohnPapa]

There are two problems with not deleting the tables:
1) The BE MDB can be big (40Mb) for dentists who use the software for say the past 10 years and it would not be practical to send such a big file.
2) The BE MDB contains the patient data of the dentist and this can get dangerous.

The software is used at present by about 200 dentists and they all have a FE MDE and a BE MDB. The inclusion of the transfer feature is probably not worth the spliting of the db.

John

 

[gemma-the-husky]

David, referring to your instructions
Preperation to Export:
It will ask which mdb contains th data you want to Export
Once selected use FileCopy to make a new mdb
Delete unwanted tables from new mdb

All is clear except the use of FileCopy to make new mdb. The tblCustomer table is in the BE MDB file. I can copy this using FileCopy into a new MDB but how do I programmatically delete the unwanted tables? Don't I need to attach to the tables in the new MDB?

Regrding Dave's (Gemma-the-husky) comment, the software is my creation and it is a commercially available product.

Ah I see, - You are consulting with the designer then!

The simplest way would be to modify your code.

Provide an export facility to write any details for a transferring patient into a csv.

provide a matching import facility to bring it in to the new database.

 

[Dairy Farmer]

New be with the same structure as the working be's.
Link the table in new be to the fe, but give it a different name i.e. Import/Export

Create a form that lists the customer names and has a check box.
Write a query empties the import/export table.
Write a query that appends the all ticked records to import/export table.
Write a query that deletes all ticked records from the origional table.

Send new be to second site.

Second site has same setup with import/export table linked to new be.
Write query than appends original table with data from import/export.
Have a fail safe that checks for duplicates.
Write query that deletes all records in import/export table.

Have a backup system that backs up bes before import/export (see my post here on how to backup and restore)

Now the record is transfered, in Access format, from one site to another. It is not a problem if the details change as there is only one holder of the record at any one time.

 

[JohnPapa]

Dave (Gemma-the-husky),

I am the designer, programmer, owner of the package.

If you look at the previous messages, I mentioned that an intermediate text file would work except in cases of non-English characters. If we can overcome this problem then I will use a text file.

Regards,
John

 

[DCrake]

Can you post a sample set of records with greek / special characters to play with when exporting to text format files.

 

[gemma-the-husky]

Dave (Gemma-the-husky),

I am the designer, programmer, owner of the package.

If you look at the previous messages, I mentioned that an intermediate text file would work except in cases of non-English characters. If we can overcome this problem then I will use a text file.

Regards,
John

Sorry, I skimped on the whole thread.

can you not "try" a text file. If it has foreign/illegible characters, then the user would have to enter the name/correct errors manually, to correct the transfer. I suppose it depends how often you need to do this. Is it just names and addresses, or are there lots of patient notes as well?

Unless you have comprehensive translation facilities, I just don't see how you could do it, over a language barrier.

 

[JohnPapa]

David (DDrake),

Just tried it. It works OK with English. It chokes on "f.Write TheData" (see code below) when the content is Greek. It says
"Run-time error '5':"
"Invalid procedure call or argument"

The code follows:
If Not IsNull(Me!memMedicalHistory) Then
TheData = CStr(Me!memMedicalHistory) & vbCrLf
f.Write TheData
End If

 

[DCrake]

Have you tried a different method of send the data to a text file?

Open file for output as #1


Print #1, your data


Close #1

It may be the transaltor that is the route of the problem.

 

[JohnPapa]

DCrake,

I tried
txtFileNameAddress = "C:\a1\e2.txt"
Open txtFileNameAddress For Output As #1
Print #1, Me!memMedicalHistory
Close #1


and received the following when reading and outputing Greek.

??a ?a?? p????aµµa