Exchange record info between two Access dbs

[JohnPapa]

I have two databases with the same structure. Both have a customer table with several fields in it. I want to be able to send the record information for a specific customer from one db to the other. Is there another way than outputing a text file by one db and reading this text file by the other db? Can I output another kind of file apart from a text file?
Thanks,
John

 

[boblarson]

So, the big question - why have two databases?

Second question - Can't you just have the one customer table and link to it from one or the other database?

 

[JohnPapa]

Bob, the specific problem has to do with dentists exchanging patient info when they refer patients to each other. Each dentist has their own db (same tool, www.VisualDentist.com) and I am looking for a simple way to import specific patient details (like name, telephone numbers, medical history etc all in one table). The referring dentist could output a file which he/she can email to the other dentist. Hope this hepls,
John

 

[boblarson]

Okay, yes that information is important to know.

So you could export to a text file for example and then use an IMPORT built in to import the file to a transition table. Then you can use a query or set of queries to append that information - or to update existing information if the person already exists in the incoming database (you should account for that possibility even though it might not be a frequent occurance).

You could export to another access database (for example an updater which you might write) but a text file is probably going to be the best.

 

[JohnPapa]

I thought there was another file format, native to Access that the exchange could take place through. Anyway, many thanks.

 

[Dairy Farmer]

I use a program that exports/imports data from one dataset to another (not access). What it seems to do is create a number of BE files in a folder. The folder is then sent to another user for importing. I guess it uses something akin to a Make Table Query. Then the second dataset looks in that folder and used an Append Table Query to add that info to the respective tables.

 

[JohnPapa]

I thought about using an mdb file instead of a text file, but there are issues of attaching to this db and security of the data structure. Maybe a text file is the simplest way to proceed. I could swear I came across some kind of format into which you could export a recordset, which would be readable at the other end.

 

[gemma-the-husky]

the underlying problem with this, is the same problem with any replication.

if the data is changed in both copies of the database, how do you merge the data into one consolidated whole without duplication/loss of data.

this can only work, surely, if one dentist is considered the "owner" of the data - and is the only one able to change the record.

you also need a unique identifier to identify the patient. NHS number may be OK- but name alone is unlikely to be sufficient. Name and DOB may be OK. But again, if these are incorrectly entered in one of the databases, then you will get duplicated records.

Automation is great - as long as you can avoid human errors.

 

[gemma-the-husky]

I thought there was another file format, native to Access that the exchange could take place through. Anyway, many thanks.

John

it's not about the file format - its about the content.

you can just create a query, and save the contents to a csv, or anything. and then import the csv back into the other dbs. The problem is deciding how to do this while maintaining the integrity of both sets of data.

 

[Dairy Farmer]

There is xml. Exporting / Importing XML

Or SharePoint. Having some/all of the data stored on (or should that be in) SharePoint, the different practices always have access to the records. So there would be no need to export/import. Obviously there are things to consider, i.e. do you want the info available across the practices without specifically sending it. You would have to spend time analysing access logs to see who is doing what.

I have a similar project in mind for when our cows are moved from one farm to the next. The assosiated records from a number of tables need to move with the animal (i.e Medical treatments, AI, Lactations....). Unfortunatly we have very poor internet access so it would have to be a "sneakerware" solution.

 

[JohnPapa]

Everything you mention about data integrity is fine, but not an issue in the specific case. Each dentist is responsible for keeping up to date with his patient. For example, assume that a general dentist refers his patient to an implant specialist. The patient data that needs to be forwarded from the general dentist to the implant specialist are the name, address, medical history (stored in memo field), date of birth etc. I have used code like

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile(TheFileName, True)
Set a = fs.GetFile(TheFileName)
Set f = a.OpenAsTextStream(ForWriting, TristateUseDefault)
f.write TheData

to create an output text file, but I seem to have a problem during f.write with non-English characters (specifically Greek). How can I write non-English characters in a text file and then read them into the second db?

 

[DCrake]

So are you saying that it is going from English to Greek or Greek to English or Greek to Greek. If it is the later then what you need is a transaltion table to convert Greek to English Then export to text file. Import English then translate English to Greek.

 

[JohnPapa]

There are entries in Greek, which need to be delivered in Greek. Is there a way to avoid the Greek -> English -> Export to text -> English -> Greek?

 

[DCrake]

Never been down that route before so to be honest I do not have a valid answer for you. sorry.

 

[JohnPapa]

Another way that may solve the problem is to output an mdb file with one table, containing the one record. Then the second database can attach to this file and read the saved record. In this case there should not be any language issues. If there is a way to tranfer an mde instead of an mdb it would be better.

 

[boblarson]

An mde can have tables imported/exported just fine. You just can't modify forms, code, macros, or reports in an mde file.

 

[JohnPapa]

Thanks Bob,
Do you know whether I can create an mde with one table in it? I could use a copy of the existing mde (about 42Mb) and try to delete all content except the one table with the one record which I need but this may not be possible programmatically. Any ideas how to attempt to transfer the required record with an mde?
Thanks,John

 

[boblarson]

Create an mdb file first with a single table by importing your current table but select the option STRUCTURE ONLY from the OPTIONS button on the import.

Then you can export the record using SQL:

Dim strSQL As String
 
strSQL = INSERT INTO [I]YourTableInExternalDatabaseNameHere[/I] ([I]field1[/I], [I]field2[/I], etc.) IN [I]'C:\Temp\YourblankDatabaseFileNameHere.mde'[/I] SELECT [I]field1[/I], [I]field2[/I],etc. FROM [I]YourTablenameInCurrentDatabaseHere[/I]
 
[I]CurrentDb.Execute strSQL[/I]
[I]

[/I]

Remember you'll have to substitute the actual names of things in the code above.

 

[JohnPapa]

Thanks Bob, I will try it. I need the creation of the mdb and mde to take place automatically and the resulting mde to have the same security characteristics. Do you think that I can automatically create an mde?

I could use a copy of the existing backend mdb and delete all tables except the one I want, for this table I would delete the contents and then import the record and have the second database read this mdb. I do not know whether there will be an issue of size. I will try it.

John

 

[Dairy Farmer]

the underlying problem with this, is the same problem with any replication.

if the data is changed in both copies of the database, how do you merge the data into one consolidated whole without duplication/loss of data.

It shouldn't be replication, it should be transfer of data. Therefore no duplicates.