parameterized append query ...

[pietlinden]

I work at a company that analyzes healthcare records. Most of what
goes on here is beyond my control - why they don't use SQL Server for
everything etc... Here's the situation:

- the company stores "Patient", "Protocol" and "Protocol Roster"
records in a SQL Server database. The structure is like this:

Patient---(1,M)---Protocol Roster----(M,1)------Protocol

To collect and analyze the actual study information, they use Access
2003. I already wrote the query that gets the new patients on the
protocol roster and it appends just fine. What I was wondering was


I was planning on doing something like this in the startup of the
application:

1. show splash screen (like the one on Allen Browne's website).
2. grab information from various tables, maybe run Tony's AutoFE
Updater.
3. While the splash screen is being shown, grab all the new Patients
on the protocol and dump them into the Patient table in Access. (I
tried linking to the SQL Server table/view and performance was
appalling.) And I don't have any control over the SQL Server
database, so changing it is pretty much out of the question...
4. update the splash screen while the data is being imported.

Should I use the existing linked SQL Server tables (I can import them
from another Access DB) or would it be better to use DSN-less
connections so the users can't mess with them? They're currently just
hidden and there's no security on the front end databases - I want the
users to be able to create/edit their own queries, but not be able to
change form and table structures.

What would be the best way to deal with the grabbing of data and the
DSN/DSN-less issues?

Thanks! (Sorry for the wandering post... if you need clarification,
just ask!)

Pieter

 

[pietlinden]

I work at a company that analyzes healthcare records.  Most of what
goes on here is beyond my control - why they don't use SQL Server for
everything etc...  Here's the situation:

- the company stores "Patient", "Protocol" and "Protocol Roster"
records in a SQL Server database.  The structure is like this:

Patient---(1,M)---Protocol Roster----(M,1)------Protocol

To collect and analyze the actual study information, they use Access
2003.  I already wrote the query that gets the new patients on the
protocol roster and it appends just fine.  What I was wondering was

I was planning on doing something like this in the startup of the
application:

1. show splash screen (like the one on Allen Browne's website).
2. grab information from various tables, maybe run Tony's AutoFE
Updater.
3. While the splash screen is being shown, grab all the new Patients
on the protocol and dump them into the Patient table in Access.  (I
tried linking to the SQL Server table/view and performance was
appalling.)  And I don't have any control over the SQL Server
database, so changing it is pretty much out of the question...
4. update the splash screen while the data is being imported.

Should I use the existing linked SQL Server tables (I can import them
from another Access DB) or would it be better to use DSN-less
connections so the users can't mess with them?  They're currently just
hidden and there's no security on the front end databases - I want the
users to be able to create/edit their own queries, but not be able to
change form and table structures.

What would be the best way to deal with the grabbing of data and the
DSN/DSN-less issues?

Thanks! (Sorry for the wandering post... if you need clarification,
just ask!)

Pieter

Scott Barker had 2 functions in his book that used DAO Custom
Properties... I could create a custom property that stored the study
name. then I could use that to populate the tables. Is there a
better way? (that's from Access 2000 Power Programming)...

thanks!
Pieter