Creating table using data from linked ODBC connection

[tcarp]

I am relatively new to Access and have been struggling with this for a while. I can use any help I can get. Thanks in advance!



I am creating a database to track patient case loads for individual providers. Some patients are seen by multiple providers. The goal is to have the patient in the database only once, but have the option to list multiple providers within the record.

I have created a Select Query that links to an ODBC table to pull patient demographic information from the electronic medical record system. The query pulls all of the data that I need correctly. I have created a Form titled "Home". The "Home" Form has buttons that run different Macros. I want to be able to click one button on the "Home" Form to run the demographic query and search for a specific patient ID. I was thinking that it would be helpful if I could set it up so that, on click, I could enter the patient ID and it would search the linked ODBC connection for that patient's demographics and then automatically add that patient to the patient list table, along with all of their demographic information that I need.

I hope this makes sense to someone. Like I said, I've been beating my head against the wall trying to figure this out and I just can't do it on my own. Thank you!!!

[June7]

You want to duplicate the data from the ODBC linked table into a local Access table? Why duplicate?

This would probably involve an sql INSERT (APPEND query) action.

[tcarp]

The ODBC is password protected. The providers will need access to the patient data, but they do no have access to the ODBC tables. Special privileges would need to be granted and there are too many providers (and too many hoops to jump through) for that to happen. So, the information needs to be pulled from ODBC and placed into a table so that a password is not needed to view the patient information.

I had thought about creating a master table with all patients and then using an Append query to add patients weekly or so, but wasn't sure if that was the best way to go...?

[tcarp]

Also, the ODBC linked table contains hundreds of thousands of records. I ran a query to create a table (Make Table) because I only need about 1500 of those records (specific patients).

[June7]

Okay, valid reason.

Still probably will involve running sql INSERT action (maybe even INSERT SELECT) to append new records. Run the sql manually with an Access query object or automate with code (macro or VBA). I only use VBA.

Then there is the issue of updating patient records for changes as in new phone number or address, if you are pulling that info.

Are there child tables in your db related to the patient info table?

[tcarp]

Yes, the patient demographics can change, so I will need to do something about that as well. I hadn't gotten that far yet, relating to the child table question.

[June7]

Okay, then explore the sql actions, develop code if desired, when you have specific issue, post question and code for analysis.

[jopap]

Have your form dynamically modify a pass-thru query

[tcarp]

Thanks for your help!