Question Tips to start designing this DB (1 Viewer)

[jonathanchye]

Hi all!

I have been tasked to design something which I currently think is a challenge to me so hopefully someone could give some pointers

What I have now is an MS Access 2010 file which generates a report by querying data from an SQL server. I have a make table query in this db which creates a local temp table and generates a report based on this temp table. In other words this access file is just used to query and generate reports with a nice user friendly UI

This works fine as the report is run daily (on demand) and the temp table just gets overwritten each time its ran. Now the end user wants to utilise this temp table and convert this into a database instead. Basically they want an additional column which they will populate from the results of my DB.

My initial thoughts are to run daily update macros to this table (only update new data to avoid overwriting existing data) based on dates. However, this idea itself has a few challenges. How would I know which data to pick from source to update? What sort of query should I run? This is the first time I've been asked to do such as thing with Access so hopefully someone could offer some suggestions.

p/s: I have access to a virtual machine that runs 24/7 so I can leave access running on this machine to carry out timed operations to tables etc.

Thank you!

 

[gemma-the-husky]

it is a good idea - and avoids potentially updating the "live" data source.

as long as you have something that can uniquely idenitfy each row in the SQL table, you can do this with 2 processes

1. an unmatched query to find new records. use an append query to add these to your access table(s)

2. a matched query to find existing records. use update queries to reflect any changes in these

I think someone said you can combine these into a single process with an outer join?

 

[jonathanchye]

Great idea gemma! I was having some problems with the unmatched query but the handy Query Wizard in 2010 helped me through it

I think I've got it working after trying out with some test data. What I did was have 2 tables - tblMain will be the table users will update and tblMainTemp will be a dump from the SQL server.

I've a macro which runs a query to create tblMainTemp at preset times. I create a unique ID by concatenating 2 fields in both tables. I then run an "unmatched-append" query based on this unique ID to update tblMain. I basically modified the wizard's unmatch query to an append query.

Tried it and seems to work so far as it only updates new entries so it won't overwrite older ones which users will need

Now on to create a pretty UI and then off to present it to the end user

it is a good idea - and avoids potentially updating the "live" data source.

as long as you have something that can uniquely idenitfy each row in the SQL table, you can do this with 2 processes

1. an unmatched query to find new records. use an append query to add these to your access table(s)

2. a matched query to find existing records. use update queries to reflect any changes in these

I think someone said you can combine these into a single process with an outer join?