Import individual records from external database

[jimh]

I'm new creating databases, so please bear with me. I'm sure this is probably relatively simple, but I've not been able to find similar example to learn from.



We have a master read-only database that contains the names of 3,000+ members. I want to create a second database for tracking issues related to those members. As an issue arises, I want to import the member's data from the master database to the issue database and then fill in additional data pertaining to the issue.

I envision doing this by opening a form (in the issue database) and start by entering the member's ID number, click a button that then refers to the master database to populate the form. From there I'd plug in the issue data and save everything.

What sort of code do I need to use to do this? Is it an append query or a SELECT/INSERT statement? How do I set it up so that when I click the button the input from the field is used as the search parameter for the query? Am I going to do this just using a query or will it be done using VBA?

If someone could point me to a resource that demonstrates something similar, I'd greatly appreciate it.

Thank you.

Jim

[June7]

If you are able to read the master data for import then you should be able to just establish a table link. Then would not need to import data. Just enter issue data along with the member ID into the Issues table.

Copying the data will require VBA code.

[jimh]

I considered that, but the master database is somewhat fluid. My concern is, what happens if we create an issue record for a member and then the next week the member is no longer in the master database? Would we then have an orphaned issue? Also, how would we know what previous member the issue was related to?

[June7]

What! Deleting records! Shame!

You would know what previous member the issue goes with if every member is assigned a unique ID in the original data source. You would save that ID in the issue record. As long as that member record remained in the external source data, could always retrieve details.

However, if deleting records is allowed or the external db could disappear someday, then you have justification for importing the data. This could get a little complicated. You have decisions to make.

First, should you record all the member info with every record in the Issues table (like a spreadsheet) or maintain a normalized structure? The latter requires a table for issues and a local table for members.

Second, should you update the local members table with new members everytime the database is opened or only add a member when an issue is entered. Probably the latter works best for you.

I assume you can link to the external source so that the existing members at any time are available to you? One way to set this up is to create a form that has a record source that joins the issues table with the linked member table (jointype 'Show all records from issues...'). Have a combobox that selects the member for that issue record and is bound to the Issues member ID field. Have textboxes bound to fields of the link table. Set them as Locked and TabStop No.

Now in some event that commits the issue record, use VBA code to save values of member info.

If you save the member info to issues table simply set fields equal to the textboxes, like:
Me!memberName = Me.tbxMemberName

If you save the member info to a members table need to first check if that member ID is in table and then either skip or update data. If not in table then insert new record. One way to check if member exists is with a DLookup function. Then update/insert will require VBA coded SQL action statements.

If it is possible that member is removed from source database before you enter the issue, that is another complication that could be dealt with.